question

sblb-6958 avatar image
0 Votes"
sblb-6958 asked AgaveJoe commented

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

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;
              }
          }
dotnet-csharpdotnet-aspnet-core-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

bet365samcom avatar image
0 Votes"
bet365samcom answered sblb-6958 commented

Thanks a lot

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered sblb-6958 commented

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..


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What's schéma should I take for the filetable (from SQL Server)
Have you an example or Microsoft's tutorial?

0 Votes 0 ·
Bruce-SqlWork avatar image
0 Votes"
Bruce-SqlWork answered sblb-6958 commented

Filetable schema is in the docs

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

Sample create table

 CREATE TABLE DocumentStore AS FileTable;  
 GO


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

FYI the filetable table is already created in SQL it's OK.
as you know my concern is how I can return in my interface the link of the file and the content type and have the possibility to open it via server side processing?

Has you hnow the link to the full tutorial to do that?

I thought make the class not mapped with this two field and do an action to get the path of file.

0 Votes 0 ·
sblb-6958 avatar image
0 Votes"
sblb-6958 answered AgaveJoe edited
 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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

This subject was covered extensively on your previous threads and if I recall correctly you have working code. Simply use the System.IO namespace to query the file table directory.

If you want to query the actual file table then use entity framework's raw query option which was also covered in your previous threads on this subject.

Raw SQL Queries


0 Votes 0 ·
sblb-6958 avatar image
0 Votes"
sblb-6958 answered sblb-6958 edited

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!







· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.




0 Votes 0 ·

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

207666-capture.jpg

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.




0 Votes 0 ·
capture.jpg (112.7 KiB)
Bruce-SqlWork avatar image
1 Vote"
Bruce-SqlWork answered AgaveJoe edited

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm sorry but that's not the point.

My concern is how I can put entity framework's raw query option in method GetCustomer()?

I've wrote

 FileName= context.FileDescription.FromSqlRaw("SELECT dbo.wSerie" .ToList()

0 Votes 0 ·

Read the official documentation to learn how to execute a raw query. Unfortunately, the one line of code you've shared is so poorly written that I have no idea what you're intension is.

  • FileDescription returns a collection of FileDescriptions. I have no idea what FileName is but I assume it is not a List<FileDescriptions>. Maybe a string???

  • The SQL Script "SELECT dbo.wSerie" is not valid T-SQL. Is dbo.wSerie a table and you want; "SELECT * FROM dbo.wSerie"?

  • Or maybe dbo.wSerie is a stored procedure? If so, share the stored procedure.

  • Lastly, you're missing an ending ")".







0 Votes 0 ·
bhanujkn avatar image
0 Votes"
bhanujkn answered sblb-6958 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@bhanujkn, 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

1 Vote 1 ·
ArminDurakovi-6322 avatar image
0 Votes"
ArminDurakovi-6322 answered

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

Best regards

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sblb-6958 avatar image
0 Votes"
sblb-6958 answered sblb-6958 commented

@ 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 },
                           
         ]
    
     });
 });




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?










0 Votes 0 ·


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








0 Votes 0 ·
AgaveJoe avatar image
0 Votes"
AgaveJoe answered sblb-6958 commented

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


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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?


0 Votes 0 ·