Razor Pages return Json for DataTables

Question

i have a razor project where i implemented DataTables. I'm trying to retrieve data from the database dynamically with ajax in this way:

$('#orariDipendenti').DataTable({
            'orderMulti': false,
            'stateSave': true,
            'paging': true,
            'pageLength': 10,
            'filter': false,
            'processing': true,
            'serverSide': true,
            'ajax': {
                url: '?handler=LoadData',
                type: 'POST',
                dataType: 'json',
                beforeSend: function (xhr) {
                    xhr.setRequestHeader("XSRF-TOKEN",
                        $('input:hidden[name="__RequestVerificationToken"]').val());
                },
            },
        });

my back code is this:

public JsonResult LoadTable()
    {
        try
        {
            var idDipendente = HttpContext.Session.GetInt32("IdDipendente");

            var draw = HttpContext.Request.Form["draw"].FirstOrDefault();

            var length = Request.Form["length"].FirstOrDefault();

            var start = Request.Form["start"].FirstOrDefault();

            int pagesize = length != null ? Convert.ToInt32(length) : 0;

            int skip = start != null ? Convert.ToInt32(start) : 0;

            int recordstotal = 0;

            var recordData = context.Pres_Orari.Where(x => x.IdDipendente == idDipendente).ToList();

            recordstotal = recordData.Count;

            var data = recordData.Skip(skip).Take(pagesize).ToList();

            return new JsonResult(new { draw = draw, recordsFiltered = recordstotal, recordsTotal = recordstotal, data = data });
        }
        catch (Exception)
        {
            throw;
        }
    }

the error i get when i open the page is this:

DataTables warning: table id={id} - Invalid JSON response.

I know the broblem is with the returned json but i don't know where. somebody can help me?

Answer 1

Firstly,your handler name is LoadTable,your url in ajax is url: '?handler=LoadData'.You need to change url to url: '?handler=LoadTable'.And here is a working demo(I also change xhr.setRequestHeader("XSRF-TOKEN" to xhr.setRequestHeader("RequestVerificationToken",if your code can work,you don't need to changexhr.setRequestHeader("XSRF-TOKEN"):

cshtml:

@Html.AntiForgeryToken()
    <table id="orariDipendenti" class="table table-striped table-bordered" style="width:100%">
        <thead class="thead-dark">
            <tr class="table-info">
                <th>IdDipendente</th>
                <th>Name</th>
               
            </tr>
        </thead>
    </table>
    <script src="~/lib/jquery/dist/jquery.min.js"></script>
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.css">
    
        <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script>
        <script>
    <script>
            $(function () {
                $('#orariDipendenti').DataTable({
                    'orderMulti': false,
                    'stateSave': true,
                    'paging': true,
                    'pageLength': 10,
                    'filter': false,
                    'processing': true,
                    'serverSide': true,
    
                    ajax: {
                        url: '?handler=LoadTable',
                        type: 'POST',
                        beforeSend: function (xhr) {
                            xhr.setRequestHeader("RequestVerificationToken",
                                $('input:hidden[name="__RequestVerificationToken"]').val());
                        },
                    },
                    columns: [
                        { data: 'idDipendente' },
                        { data: 'name' },
                    ]
                });
            });
           
    
        </script>

Model:

 public class MyModel1 {
        public int IdDipendente { get; set; }
        public string Name { get; set; }

    }

cshtml.cs(I use fake data to test):

public static List<MyModel1> list = new List<MyModel1> { new MyModel1 { IdDipendente = 1, Name = "name1" }, new MyModel1 { IdDipendente = 2, Name = "name2" }, new MyModel1 { IdDipendente = 3, Name = "name3" } };
public JsonResult OnPostLoadTable()
        {
            try
            {
                var idDipendente = 1;

                var draw = HttpContext.Request.Form["draw"].FirstOrDefault();

                var length = Request.Form["length"].FirstOrDefault();

                var start = Request.Form["start"].FirstOrDefault();

                int pagesize = length != null ? Convert.ToInt32(length) : 0;

                int skip = start != null ? Convert.ToInt32(start) : 0;

                int recordstotal = 0;

                var recordData = list.Where(x => x.IdDipendente==idDipendente).ToList();

                recordstotal = recordData.Count;

                var data = recordData.Skip(skip).Take(pagesize).ToList();

                return new JsonResult(new { draw = draw, recordsFiltered = recordstotal, recordsTotal = recordstotal, data = data });
            }
            catch (Exception)
            {
                throw;
            }
            
            
        }
    }

result：