ASP.NET CORE server side processing to acces to the files stored in FILE TABLE ?

Question

Hi,
first I want to create an ASP.NET core application linked to the FILE TABLE to open the files directly in my interface
After several attempts I was able to implement in my interface the links of my files to be able to consult them.
In the FILE TABLE I have 60000 files so the access to the file and directory of the FILE TABLE was not relevant.

I took the option to work server side processing to improve the performance.

I've created an class FileTable and the controller FileController

Questions :
_ Can I do a first migration to create a FILE TABLE?
_ If no, Should I create a class with the [NoMapped] attribute?
_ Can I use jquery Datatable with File Table?
_ How to use a controller to put the path of the files from the controller I put below?

I know these are not expert questions but I really need help on this.
thank you in advance

You will find below the class that I will need.

public class FileTable
     {
         public int Id { get; set; }
         public string Name { get; set; }
         public string Description { get; set; }
         public DateTime CreatedTimestamp { get; set; }
         public DateTime UpdatedTimestamp { get; set; }
         public string ContentType { get; set; }
     }


[Route("api/[controller]")]
     [ApiController]
     public class FileController : Controller
     {
         private readonly ApplicationDbContext context;
         public FileController(ApplicationDbContext context)
         {
             this.context = context;
         }

         [HttpPost]
         public IActionResult GetCustomers()
         {
             try
             {
                 var draw = Request.Form["draw"].FirstOrDefault();
                 var start = Request.Form["start"].FirstOrDefault();
                 var length = Request.Form["length"].FirstOrDefault();
                 var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
                 var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
                 var searchValue = Request.Form["search[value]"].FirstOrDefault();
                 int pageSize = length != null ? Convert.ToInt32(length) : 0;
                 int skip = start != null ? Convert.ToInt32(start) : 0;
                 int recordsTotal = 0;
                 var customerData = (from tempcustomer in context.FileDescriptions select tempcustomer);
                 if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))
                 {
                     customerData = customerData.OrderBy(sortColumn + " " + sortColumnDirection);
                 }
                 if (!string.IsNullOrEmpty(searchValue))
                 {
                     customerData = customerData.Where(m => m.Name.Contains(searchValue)
                                                 || m.Description.Contains(searchValue)
                                                 || m.ContentType.Contains(searchValue));

                 }
                 recordsTotal = customerData.Count();
                 var data = customerData.Skip(skip).Take(pageSize).ToList();
                 var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data };
                 return Ok(jsonData);
             }
             catch (Exception ex)
             {
                 throw;
             }
         }

Answer 1

a select from the filetable should be just as fast a regular table. maybe you included the file contents in the filetable query, this is much slower if the files are large. I'd just create a view of the columns I wanted, and scaffold the view.

Answer 2

Thanks a lot

Answer 3

you question is not clear.

if you mean a sqlserver filetable, then your schema does not match a file table. also you do not reference it in code.

EF does not directly support a filetable. you would need to create the table outside EF, but could query it. I don't believe EF has file stream support, you may need to access the file data outside of EF. Typically the unc path is used via the file system to access.

a jquery datatables makes an Ajax call, you can support anything you want..

Answer 4

Filetable schema is in the docs

https://learn.microsoft.com/en-us/sql/relational-databases/blob/filetable-schema?view=sql-server-ver16

Sample create table

CREATE TABLE DocumentStore AS FileTable;    
GO  
  

Answer 5

also you do not reference it in code.

What I don't understand is how to reference the filetable in the controller to work directly on the server side?