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

Not functional means the Link of the file in column name is not functional.
So this means it 's not possible to have the route for the path and doc for the same table.

FYI the is functional with the relativePath.
So the link in column will be functional if I change the route. Is that right?

Answer 3

Thanks for your all informations!

let's move on!

I have another topic.
I would like to add in the same code "input select" with which I could change the view in the same page ie have the possibility to have several datatable depending on the "select input".
Currently I have the view with FilePath which could be associated with an input item and I would like to add several items which would allow to change view to display other views (so several table in sql).
Do you think this is possible?