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

sblb 1,166

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;
             }
         }

24 answers

sblb 1,166 Reputation points

2022-06-01T16:34:20.473+00:00

I confirm you I have a code that works quite well but as you know the display performance is not good because I work client side. ( Thanks for your help)
I guess you understood I want to work server side.

If you want to query the actual file table then use entity framework's raw query option

I should be able to put in GetCustomer method var FileName= context.FileDescription.FromSqlRaw("EXECUTE dbo.wSerie" .ToList(); but is not good way!
Please sign in to rate this answer.
AgaveJoe 26,136 Reputation points

2022-06-01T17:51:54.117+00:00

I guess you understood I want to work server side.

You are already working on the server side! The problem is you are return 600,000 records from the server server side. It takes a long time for the jQuery DataTable to parse such a large results set. Rather than returning 600,000 records, return 10, 20, 50, 100. The pagination, sorting, and filtering is a something you need to figure out. The community has no idea how you intend the application to work.

It might be easier to drop the DataTable and write your own pagination, sorting, and filtering. I suggested going through the getting tutorials on this site many many times which actually covers this subject. Unfortunately, reviewing your GetCustomer action confirms you have not go through the tutorials because you are not taking advantage of model binding.

sblb 1,166 Reputation points

2022-06-01T18:41:31.157+00:00

It is good to write with certainty and but I assure you I understood you are a specialist I am not I just seek to understand and help because all codes I do are in the personal scope

It takes a long time for the jQuery DataTable to parse such a large results set.

I don't agree with you I tried to use server side processing with a simple table that contains 60k. So there is a significant difference between server side processing and server client.

The pagination, sorting, and filtering is a something you need to figure out.

I took the datatable option because I admit that filtering, pagination...is already integrated. I would like to finalize this option and then make my own filter code...
Now I need only one element.

In picture below I INSERT the all element from File Table to Simple Table and really there is a huge difference in the access to my application

To improve all this I will need your help to work on server side.

I should be able to put in GetCustomer method var FileName= context.FileDescription.FromSqlRaw("SELECT dbo.wSerie" .ToList(); but is not good way!
It's here

Just one question, do you understand what I am asking?
If my last one is not clear I can clarify.
Sign in to comment
Vikas Lalwani 6 Reputation points

2022-06-02T07:40:21.5+00:00

I suppose you have referenced this article,

https://qawithexperts.com/article/asp-net/jquery-datatable-server-side-processing-in-aspnet-core/417

as code looks similar to what you have written in controller.

I will suggest to you first connect your database by using Microsoft.EntityFrameworkCore.SqlServer Nuget package and also install Microsoft.AspNetCore.Mvc.NewtonsoftJson.

Also configure DbContext properly, you don't need to run raw queries, better handle everything using Linq or lambda.

performance is dependent on this part of code

var data = customerData.Skip(skip).Take(pageSize).ToList();

As we will only fetch required rows (like 10 rows only from database), based on currently viewed page.
Please sign in to rate this answer.
sblb 1,166 Reputation points

2022-06-02T09:03:09.69+00:00

@Vikas Lalwani , thanks to your message. I have already done what you say with a simple table and EF migration. It works perfectly!
According to the previous message from Bruce-SqlWork EF does not directly support a filetable because I work with a FileTable hence the raw SQL query
Sign in to comment
Armin Duraković 1 Reputation point

2022-06-02T09:05:24.827+00:00

Chech did you you included the file contents in the filetable query.

Best regards
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

sblb 1,166

@ AgaveJoe

The first intention that I have is to put some elements from the file table (Name, Type, Date...) in FileDescription class to render it and have acces to the file.
I don't use the procedure.
This elements must put in GetCustomers method.
1 - I need to recover the access path, I think to do that

     FileDescription results = _context.Set<FileDescription>().FromSqlRaw("SELECT FileTableRootPath() as Name").FirstOrDefaultAsync();

2 - Put the element of file table in class FileDescription

string[] FileName = context.FileDescriptions.FromSqlRaw("SELECT*FROM dbo.wSerie").ToList();

The FileDescription has defined as below:

  public class FileDescription
    {
        [NotMapped]
         public string Name { get; set; } = string.Empty;
         public string Description { get; set; }
         public DateTime CreatedTimestamp { get; set; }
         public DateTime UpdatedTimestamp { get; set; }
         public string ContentType { get; set; }
    }

I defined the ApplicationDbContext

 public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {
        }
        public DbSet<FileDescription> FileDescriptions { get; set; }

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

          }
    }

Connextion
"ConnectionStrings": {
"DefaultConnection": "Data Source=servername;Initial Catalog=wSERIE;Trusted_Connection=True"
}

FileController

 [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;
            }
        }
    }

The customerDatatable

$(document).ready(function () {
    $('#customerDatatable').dataTable({

        "processing": true,
        "serverSide": true,
        "filter": true,
        "ajax": {
            "url": "/api/file",
            "type": "POST",
            "datatype": "json"
        },
        "columnDefs": [{
            "targets": [0],
            "visible": false,
            "searchable": false
        }],
        "columns": [
          //  { "data": "id", "name": "Id", "autoWidth": true },
            { "data": "name", "name": "Name", "autoWidth": true },
            { "data": "description", "name": "Description", "autoWidth": true },
            { "data": "createdTimestamp", "name": "CreatedTimestamp", "autoWidth": true },
            { "data": "updatedTimestamp", "name": "UpdatedTimestamp", "autoWidth": true },
           { "data": "contentType", "name": "ContentType", "autoWidth": true },

        ]

    });
});

AgaveJoe 26,136 Reputation points

2022-06-02T11:47:19.313+00:00
1 The original code to get the path functioned perfectly and had the following pattern.

IFileTableService

public async Task<string> GetFileTableDirectory(string fileStreamDirectory = "Archive") { FileTableRoot? results = await _context.Set<FileTableRoot>() .FromSqlRaw("SELECT FileTableRootPath() as Name").FirstOrDefaultAsync(); return $"{results?.Name}\\{fileStreamDirectory}"; }

FileTableRoot class

[NotMapped] public class FileTableRoot { public string Name { get; set; } = string.Empty; }

For reasons only known to you, you changed the DbContext and service code essentially breaking the logic. Why???

2 The code has compiler errors. FileDescriptions is a type not a string.

Like your other posts, it seems like you are aimlessly coping and pasting code without understanding what the code does. You must have noticed that the code stopped working after making these changes. Can you go back to the working code?
sblb 1,166 Reputation points

2022-06-02T12:33:01.837+00:00
1 The original code to get the path functioned perfectly and had the following pattern.

I agree with you!

Let's move on.

Ok for IFileTableService

In your FileTableRoot class, why do you put only one element? In my side I want to have several informations eg date, content type...
If I understood well, the FileTableRoot is a "Temp" class which allows to recover the path with the name of the file. Did I understand correctly?

After that I want to use GetCustomer method this means that I only call the datasource as follow :

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

The small remark : you always have the little phrase that makes you happy ;-)

AgaveJoe 26,136 Reputation points

2022-06-02T13:30:58.907+00:00
In your FileTableRoot class, why do you put only one element?

In the original design, the FileTableRoot class holds the file table root directory which a string. It's not clear why you changed the code.

In my side I want to have several informations eg date, content type..

You want to return the FileDescription, right? It seems like you changed this line...

modelBuilder.Entity<FileTableRoot>().HasNoKey().ToTable("FileTableRoot", t => t.ExcludeFromMigrations());

to this line.

modelBuilder.Entity<FileDescription>().HasNoKey().ToTable("FileDescription", t => t.ExcludeFromMigrations());

Rather than changing FileTableRoot to FileDescription in the DbContext, just add the FileDescription to the context.

The DbContext should have the following pattern.

public partial class ArchiveDbContext : DbContext { public ArchiveDbContext(DbContextOptions<ArchiveDbContext> options) : base(options) { } 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); } }

Next, add a migration and update the database. For example...

add-migration NameOfTheMigration update-database

Of course, this assumes you've added the required NuGet packages. See the official docs which covers code first migrations.

Migrations Overview
Please sign in to rate this answer.
sblb 1,166 Reputation points

2022-06-02T14:36:40.717+00:00

I have no problem with the migration
I really don't understand anything anymore.
I understood that EF does not directly support a filetable. Now You ask me to see the migration documentation. I'm really confused.

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?

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?
Sign in to comment