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

I understood that EF does not directly support a filetable. Now You ask me to see the migration documentation. I'm really confused.

Your code snippet has an class named FileTable. The FileTable class is not an SQL file table. At least the schema does not match an actual SQL file table. It looks like you made arbitrary name changes to code that was functioning properly.

If I read between the lines, I've to do the migration of the simple table and filetable (exlude of migration) and after that I 've to add FileTableRoot column to FileDescription. Do I have it right?

No. In my example FileTableRoot and FileDescription are two difference entities with distinctly different purposes. FileTableRoot.Name holds the SQL file table steam directory. FileDescription is a separate entity that models the FileDescription table. This whole mess started with a tutorial where you copied the code without understanding how the code worked. Anyway, that's where the FileDescription originated.

If I come back to GetCustomer method var customerData = (from tempcustomer in context.FileDescriptions select tempcustomer); will be my datasource. Do I have it right?

Does your DbContext have a FileDescriptions defined or did you change the name to FileTable?

Answer 2

You are right has started with this code. But right now I've changed the approach that we have made together. The FileDescription name seems to me to correspond to the task.

Does your DbContext have a FileDescriptions defined or did you change the name to FileTable?

DbContext is defined with FileTableRoot and the FileDescription.

 public class FileContext : DbContext
    {
        public FileContext(DbContextOptions<FileContext> options) : base(options)
        { }

        public DbSet<FileTableRoot> FileTableRoot { get; set; }
        public DbSet<FileDescription> FileDescriptions { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<FileTableRoot>().HasNoKey().ToTable("FileTableRoot", t => t.ExcludeFromMigrations());
            modelBuilder.Entity<FileDescription>().HasKey(m => m.Id);
            base.OnModelCreating(modelBuilder);
         }

Now if I keep the same code that works how to add the datasourve in GetCustomer method

Answer 3

Now if I keep the same code that works how to add the datasourve in GetCustomer method

Oh my, now I see the problem.

Here' your are querying an entity.

var customerData = (from tempcustomer in context.FileDescriptions select tempcustomer);  

Then you switch to a .NET framework DataTable query to order the columns.

customerData = customerData.OrderBy(sortColumn + " " + sortColumnDirection);  

I think bhanujkn is right, it looks like you copied the bulk of the code from https://qawithexperts.com/article/asp-net/jquery-datatable-server-side-processing-in-aspnet-core/417. Then for some unknown reason you changed the OrderBy logic to what looks like a .NET Framework DataTable query. I'm guessing you did not understand how to adjust the code to fit the FileDescription columns. So you found some other code snippet on the Internet, pasted it, and hoped for the best.

                if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))  
                {  
                    //get sorting column  
                    Func<Person, string> orderingFunction = (c => sortColumn == "First Name" ? c.FirstName : sortColumn == "Last Name" ? c.LastName : c.FirstName);  
  
                    //check sort order   
                    if(sortColumnDirection == "desc")  
                    {  
                        personData = personData.OrderByDescending(orderingFunction).AsQueryable();  
                    }  
                    else  
                    {  
                        personData = personData.OrderBy(orderingFunction).AsQueryable();  
                    }  
  
                }  

Let the community know when you copy someone else's code and provide the link. Also, if you ran the code through the debugger you would see this line of code does not work as expected.

customerData = customerData.OrderBy(sortColumn + " " + sortColumnDirection);  

You have to understand that the community cannot read your mind. Clarify what you are trying to do, explain the expected results, and the actual results. Stop adding code that has nothing to do with the problem.

Lastly, the tutorial(s) I suggested illustrates how to sort, filter, and page which might be easier to understand.

https://learn.microsoft.com/en-us/aspnet/core/data/ef-mvc/sort-filter-page?view=aspnetcore-6.0

Answer 4

I understand you don't want give me a simple answer.

The code is completely functional when I used the simple table and I understood the logic.

So my need is how introduce the filetable as datasource in getcustomer?

The definition of datasource

var customerData = (from tempcustomer in context.FileDescriptions select tempcustomer);

Apparently is not easy!

The link of the code is : jquery-datatable-in-aspnet-core it's very good tutorial

Answer 5

Let's keep the same language
I will use FileTableRoot to define the class.

The data located in the FileTable with the access path : \servername\ParatageData\wSerie\wSerieDir.
wSerieDir is the directory where all the files are stored.

Are you asking how to execute a query in an actual file table

In the GetCustomer() method I want to define the datasource.
In the datasource I need to have access to the data of the FileTable (with all the files).
I thought I could use the raw requests directly in the GetCustomers method to get direct access to the FileTable (the files).

the datasource is defined in the line code by

var customerData = (from tempcustomer in context.???.select tempcustomer);

I added the ??? this is where I need to put my access to the FileTable data.

and this is where I need you to define ???

I hope that it is clearer