ASPNET CORE 5 - code statut 505 in jquery datatable ?

Question

Hi, I am following up on the post how-i-can-do-raw-query-in-jquery-datatable-to-acce.html
Thanks to @AgaveJoe I was able to set up access to the file table.
This access allowed me to fill jequery datatable to work server side.

In the table below, there are two columns Path and File.
_the Path column uses relative path to get the files from the file table directory and the System.IO.
_the File column uses file stream from the file table.

this set up everything works perfectly.

Now I want to set up a filter and a search, again thanks to @AgaveJoe , I have introduced a filter and a search and I have a code 500 return.
I've implemented the code below and I received statut code 500

I've put the break point in method GetFilePathsFromDocumentStore(), code 500 appears in the in service method at the level of the variable results

I don't understand why I have this error with the code below, have an idea?

wSerieController

  [Route("api/[controller]")]  
    [ApiController]  
    public class wSerieController : ControllerBase  
    {  
          private readonly ApplicationDbContext context;  
        private readonly IFileRepository _filerepository;  
  
        public wSerieController(ApplicationDbContext context, IFileRepository filerepository)  
          {  
              this.context = context;  
             _filerepository = filerepository;  
           }  
  
        [HttpGet]  
        public async Task<IActionResult> GetFileByFilePath(string relativePath)  
        {  
             string directory = await _filerepository.GetFileTableDirectory();  
            string path = $"{directory}\\{relativePath}";  
  
           if (System.IO.File.Exists(path))  
          {  
               return File(System.IO.File.OpenRead(path), "application/octet-stream", Path.GetFileName(path));  
          }  
             return NotFound();  
        }  
  
        [HttpGet("path")]  
        public async Task<List<FilePath>> GetFilePathsFromDocumentStore()  
        {  
            List<FilePath>? results = await _filerepository.GetFilePathsFromDocumentStore();  
            return results;  
        }  
  
        [HttpGet("path/{uid:guid}")]  
        public async Task<IActionResult> GetFileByIdFromDocumentStore(Guid uid)  
        {  
            wSerie? doc = await _filerepository.GetFileByIdFromDocumentStore(uid);  
  
            if (doc == null)  
            {  
                return NotFound();  
            }  
            return File(doc?.file_stream ?? new byte[0], "application/octet-stream", doc?.name);  
        }  
  
        [HttpPost("doc")]  
        public async Task<DataTableResponse> GetFilePathsFromDocumentStore([FromForm] DataTableAjaxPostModel model)  
        {  
                   return await _filerepository.GetDocumentStore(model);  
        }  

GetDocumentStore in Service

  public async Task<DataTableResponse> GetDocumentStore(DataTableAjaxPostModel model)  
        {  
  
            int? draw = model.draw;  
            int? start = model.start;  
            int? length = model.length;  
            string sortColumn = model.columns[model.order[0].column.Value].name;  
            string? sortColumnDirection = model?.order?[0].dir;  
            string? searchValue = model?.search?.value;  
            int pageSize = length != null ? Convert.ToInt32(length) : 0;  
            int skip = start != null ? Convert.ToInt32(start) : 0;  
            int recordsTotal = 0;  
  
            IQueryable<FilePath> query = from filePath in _context.FilePaths select filePath;  
            if (searchValue != null)  
            {  
                query = query.Where(m => m.Name.Contains(searchValue));  
            }  
  
            if (sortColumn != null && sortColumnDirection != null)  
            {  
                query = query.OrderBy(sortColumn + " " + sortColumnDirection);  
            }  
  
            var results = await query.Skip(skip).Take(pageSize).ToListAsync();  
            recordsTotal = results.Count();  
  
            DataTableResponse response = new DataTableResponse()  
            {  
                draw = draw.HasValue ? draw.Value : 0,  
                recordsFiltered = recordsTotal,  
                recordsTotal = recordsTotal,  
                data = results  
            };  
  
            return response;  
        }  

datatable

$(document).ready(function () {  
    $('#wSerieDatatable').dataTable({  
  
        "processing": true,  
        "serverSide": true,  
        "filter": true,  
        "ajax": {  
            "type": "POST",  
            "url": "/api/wSerie/doc",  
           "datatype": "data.json"  
        },  
        "columnDefs": [{  
            "targets": [0],  
            "visible": true,  
            "searchable": true  
        }],  
        "columnDefs": [{  
            "targets": [1],  
            "visible": true,  
            "searchable": true  
        }],  
        "columns": [  
            { "data": "id", "name": "Id", "autoWidth": true },  
            {"data": "path", 'render': function (data, type, row, meta) {  
                    return '<a href="/api/wSerie/?relativePath=' + row.path + '" >' + row.path + '</a>';  
                }  
            },  
             
            {  
                "data": "name", 'render': function (data, type, row, meta) {  
                    console.log(row);  
                    return '<a href="/api/wSerie/doc/' + row.id + '" >' + row.name + '</a>';  
                }  
            },  
            { "data": "created", "name": "Created", "autoWidth": true }  
        ]  
    });  
});  

Answer 1

The detail of query definition. Apparently le problem could come of the FilePath designation. I don't understand why the FilePath is not recognized.
I've put a break on

You did not provide the actual error. Is it the error "Invalid object name 'FilePath'."???

If so, you did not follow the recommendations again and create a View. I provided the View source code in your previous thread. The View DDL is below.

CREATE VIEW [dbo].[FilePath]  
AS  
SELECT [stream_id] AS Id  
    ,[file_stream].GetFileNamespacePath() As [Path]  
    ,[name] As [Name]  
    ,[creation_time] [Created]  
FROM [dbo].[DocumentStore]  
WHERE [is_archive] = 1  
GO  

 

Answer 2

Edit: added condition...

public async Task<DataTableResponse> GetDocumentStore(DataTableAjaxPostModel model)  
{  
    int? draw = model.draw;  
    int? start = model.start;  
    int? length = model.length;  
    string? sortColumn = model?.columns[model.order[0].column]?.name;  
    string? sortColumnDirection = model?.order?[0].dir;  
    string? searchValue = model?.search?.value;  
    int pageSize = length != null ? Convert.ToInt32(length) : 0;  
    int skip = start != null ? Convert.ToInt32(start) : 0;  
              
    int recordsTotal = _context.FilePaths.Count();  
    int filteredRecords = 0;  
  
    IQueryable<FilePath> query = _context.FilePaths.OrderBy(sortColumn + " " + sortColumnDirection);  
  
    if (searchValue != null)  
    {  
        query = query.Where(m => m.Name.Contains(searchValue));  
    }  
        
    var results = await query.Skip(skip).Take(pageSize).ToListAsync();  
    filteredRecords = results.Count();  
  
    DataTableResponse response = new DataTableResponse()  
    {  
        draw = draw.HasValue ? draw.Value : 0,  
        recordsFiltered = searchValue == null ? recordsTotal : filteredRecords,  
        recordsTotal = recordsTotal,  
        data = results  
    };  
    return response;  
}  

Answer 3

This web service it's call by the method GetFilePathsFromDocumentStore(). and the "url": "/api/wSerie/path",

I think that the problem comes from the route. let me explain

I define the rout in ajax by "url": "/api/wSerie/doc", to use the method with filter...

So the method GetFilePathsFromDocumentStore() is called and didn't create the view of the FileTable so it's normal that it doesn't recognise FilePath!
Therefore, FileTable should also be built on the "url": "/api/wSerie/doc",`

webapi

  [HttpPost("doc")]  
        public async Task<DataTableResponse> GetFilePathsFromDocumentStore([FromForm] DataTableAjaxPostModel model)  
        {  
              
            return await _filerepository.GetDocumentStore(model);  
        }  

webApi service

public async Task<DataTableResponse> GetDocumentStore(DataTableAjaxPostModel model)  
        {  
              
                int? draw = model.draw;  
                int? start = model.start;  
                int? length = model.length;  
                string sortColumn = model.columns[model.order[0].column.Value].name;  
                string? sortColumnDirection = model?.order?[0].dir;  
                string? searchValue = model?.search?.value;  
                int pageSize = length != null ? Convert.ToInt32(length) : 0;  
                int skip = start != null ? Convert.ToInt32(start) : 0;  
                int recordsTotal = 0;  
  
                IQueryable<FilePath> query = from temppath in _context.FilePaths select temppath;  
  
                if (searchValue != null)  
                {  
                    query = query.Where(m => m.Name.Contains(searchValue));  
                }  
  
                if (sortColumn != null && sortColumnDirection != null)  
                {  
                //  query = query.OrderBy(sortColumn + " " + sortColumnDirection);  
                query = query.OrderBy("Name DESC");  
                }  
  
                var results = await query.Skip(skip).Take(pageSize).ToListAsync();  
                recordsTotal = results.Count();  
  
                DataTableResponse response = new DataTableResponse()  
                {  
                    draw = draw.HasValue ? draw.Value : 0,  
                    recordsFiltered = recordsTotal,  
                    recordsTotal = recordsTotal,  
                    data = results  
                };  
  
                return response;  
            }  

Answer 4

So the method GetFilePathsFromDocumentStore() is called and didn't create the view of the FileTable so it's normal that it doesn't recognise FilePath!

Seriously!? In your previous thread you specifically asked for a raw query example. I explained that a View is a better option but you ignored this advice. So I showed you how to create raw query.

The next problem you faced is the tutorial you're following uses LINQ. While it is possible to write the raw query, it is easier to use a View based on the file table and an entity. This approach allows you to reuse the tutorial code. I provided all the source and explained that a View is required. I even provided the script to create the View. But you seemly, ignored this advice - again.

At this point, I'm not sure how to help you.

Answer 5

you forgot .Value in sortColumn

Not exactly. I tweaked a few model properties definitions since the DataTable always sends these values.

    public class DataTableAjaxPostModel  
    {  
        public int? draw { get; set; }  
        public int? start { get; set; }  
        public int? length { get; set; }  
        public List<Column> columns { get; set; }  
        public Search search { get; set; }  
        public List<Order> order { get; set; }  
    }  
  
    public class Column  
    {  
        public string? data { get; set; }  
        public string? name { get; set; }  
        public bool? searchable { get; set; }  
        public bool? orderable { get; set; }  
        public Search? search { get; set; }  
    }  
  
    public class Search  
    {  
        public string? value { get; set; }  
        public string? regex { get; set; }  
    }  
  
    public class Order  
    {  
        public int column { get; set; }  
        public string dir { get; set; }  
    }  

The opening of the file is relatively slow. I have not quantified the access time, it remains qualitative. Maybe I have to indexed the view !

You still misunderstand the code and design. The SQL View returns data used by jQuery Datatable library to populate the columns. Two of DataTable columns are programmatically configured to render standard HTML links that return a file. If you followed the instructions correctly, then each link has a unique identifier that either downloads the file using the file system (relative path) or from the file table's file_stream column filtered by the stream_id (UNIQUE NONCLUSTERED). The SQL view has nothing to do with actually downloading a file. Therefore, adding an index to the SQL View will not speed up the file download.

This is another example where you are making assumptions. In my opinion, the best way to fix these holes in your understanding is through learning the fundamentals.

Also, I do not understand why downloading the file is slow since both file download links use a unique identifier. Perhaps it is a bandwidth problem or you did not follow the code example. What I find confusing is you have two perfectly functioning debuggers that can easily find a bottleneck if a bottleneck actually exists.

I would like to propose a post to you to summarize the whole of our exchanges even if that appears simple to you, that could be useful to one of the community which uses ASPNET CORE. it will allow me to deepen some of the knowledge you have given me and to sort out all the web links! What do you think about it?

I think, from a learning perspective, you should summarize the steps and organize the code.