How to implement dataTables Server side Side Paging/Searching/Sorting in ASP.net Core

Question

I am trying to perform Searching, Sorting, Paging from server-side of jQuery Datatable, I have written Following Code but, i cannot get parameters for sorting, Searching From datatble to my MVC Controller. Here is my code.

My datatable Looks Like...

<script>
    $(document).ready(function () {
        $("#newT").DataTable({
            ajax: {
                url: '@Url.Action("Prods", "NewTest")',
                method: 'post',
                dataType: 'json',
                dataSrc : ''
            },
            columns: [
                {
                    title: 'Id',
                    
                    data: 'id',
                    searchable: false,
                    sortable: false,
                    visible: false
                },
                {
                    title: 'Name',
                   
                    data: 'name',
                    render: function (data, type, row) {
                        return '<a id="' + row.id + '" href="javascript:void(0)" onclick="detailsClick(this)">' + data + '</a>'

                    }
                },
                
                ....Other Columns....
            ],
            serverSide: true,
            processing: true,
            language: {
                processing : "Please Wait ..."
            }
        })
    })
    </script>

Here is My MVC Controller

public IActionResult Prods(){
int draw = Convert.ToInt32(Request.Query["draw"]);
            int StartIndex = Convert.ToInt32(Request.Query["start"]);
            int PageSize = Convert.ToInt32(Request.Query["length"]);
            int SortCol = Convert.ToInt32(Request.Query["order[0][column]"]);
            string SortDir = Request.Query["order[0][dir]"];
            string SearchField = Request.Query["search[value]"].FirstOrDefault()?.Trim();
            
            ..... Further Implementation .....
             
            return new JsonResult(new
            {
                data = filteredData,
                draw = Request.Query["draw"],
                recordsFiltered = filteredData.Count(),
                recordsTotal = 13
            }
                    );
}
            

I got null values in Controller, Help me with this.

Answer 1

There are couple .NET Core implementations for jquery datatables already in NuGet. It has all the models and bindings you need to hook up datatables requests to MVC.

I've used this one: https://www.nuget.org/packages/DataTables.AspNet.AspNetCore/.

Here is how you use it in an ASP.NET Core MVC application:

1. Install DataTables.js

Install DataTables.js library (as well as its dependency jQuery) using your favorite client-side package manager. DataTables also comes with couple styles so they will have their own packages. You need to get that as well.

I used npm and did it directly inside package.json:

{
    "version": "1.0.0",
    ...,
    "dependencies": {
        ...,
        "jquery": "3.5.1",
        "datatables.net": "1.10.21",
        "datatables.net-bs4": "1.10.21"
    },
    "devDependencies": {
        ...
    }
}

Those packages will be pulled into the hidden folder node_modules in your project.

2. Move static files to wwwroot

In an ASP.NET Core MVC application, in order to serve any JavaScript or css file, you need to register app.UseStaticFiles() in the Startup class, and use any Task Runner to move files to the wwwroot folder.

namespace DL.NetCore.EmptySolution.Web.UI
{
    public class Startup
    {
        ...

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            ...
            app.UseStaticFiles();
            ...
        }
    }
}

I used gulp but you can use whatever you want. I just have a gulpfile.js in the project which the Task Runner of the Visual Studio will pick it up:

const bundleAndMinify = (srcFiles, destFileName, destPath, minifier) => {
    src(srcFiles)
        .pipe(concat(destFileName))
        .pipe(dest(destPath))
        .pipe(minifier())
        .pipe(rename({ suffix: '.min' }))
        .pipe(dest(destPath));
};

const copyDatatablesScripts = done => {
    bundleAndMinify(
        [
            'node_modules/datatables.net/js/jquery.dataTables.js',
            'assets/js/dataTables.bootstrap4.custom.js'
        ],
        'jquery.dataTables.js',
        'wwwroot/js',
        terser
    );

    done();
};

const copyDatatablesStyles = done => {
    bundleAndMinify(
        [
            'node_modules/datatables.net-bs4/css/dataTables.bootstrap4.css',
            'assets/css/dataTables.bootstrap4.custom.css'
        ],
        'jquery.dataTables.css',
        'wwwroot/css',
        cssmin
    );

    done();
};

I had custom setup because I wanted to change the sorting icons as well as add a search filer delay extension so I had my own custom dataTables.bootstrap4.custom.js and assets/css/dataTables.bootstrap4.custom.css from my assets folder.

But for basic installation, you only need:

• Scripts:
  • node_modules/jquery/dist/jquery.js
  • node_modules/datatables.net/js/jquery.dataTables.js
  • node_modules/datatables.net-bs4/js/dataTables.bootstrap4.js
• Styles:
  • node_modules/datatables.net-bs4/css/dataTables.bootstrap4.css

Once you have those static files in the wwwroot, you can include those scripts and styles in any page you want to run DataTables.js library:

@section css {
    <!-- jquery.dataTables styles -->
    <environment include="development">
        <link rel="stylesheet" href="~/css/jquery.dataTables.css" />
    </environment>
    <environment exclude="development">
        <link rel="stylesheet" href="~/css/jquery.dataTables.min.css" />
    </environment>
}

@section scripts {
    <!-- jquery.dataTables scripts -->
    <environment include="development">
        <script src="~/js/jquery.dataTables.js"></script>
    </environment>
    <environment exclude="development">
        <script src="~/js/jquery.dataTables.min.js"></script>
    </environment>

    ...

Note: in the gulpfile.js, you can see I bundled the script files into just 1 single file called jquery.dataTables.js, and the styles into just 1 single file called jquery.dataTables.css. That's why here on the page I only reference those 2 files.

3. Install DataTables NuGet Package

Right click your project(s) and open the NuGet Manager. Search for "DataTables.AspNet.AspNetCore" and click Install. It will automatically download its dependencies as well.

4. Register DataTables NuGet Package

Before you can use the NuGet package, you need to register it. This might be where your error came from:

namespace DL.NetCore.EmptySolution.Web.UI
{
    public class Startup
    {
        ...

        public void ConfigureServices(IServiceCollection services)
        {
            services
                .AddRouting(options => options.LowercaseUrls = true)
                .AddControllersWithViews();

            services.RegisterDataTables();
        }
    }
}

It will add the modal binding for you so that you don't have to manually bind those many DataTables request parameters on your own.

5. Have fun using it!

Now you can simply declare any MVC action that is served as the AJAX call from the DataTables.js library with IDataTablesRequest parameter. This will capture things like Request.Query["start"], Request.Query["length"] etc. Thanks for the DataTables NuGet package!

And after you're done processing your data, you just need to create a DataTablesJsonResult() back to the client:

namespace DL.NetCore.EmptySolution.Web.UI.Controllers
{
    public class ProductController : Controller
    {
        public IActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public IActionResult GetList(IDataTablesRequest request)
        {
            // Get products from your persistance store
            var products = GetFakeProducts();

            // Convert them into view models
            var rows = new List<ProductListRowViewModel>();
            // foreach product in products ...

            // Filter them
            var filteredRows = rows
                .AsQueryable()
                .GlobalFilterBy(request.Search, request.Columns);

            // Sort and paginate them
            var pagedRows = filteredRows
                .SortBy(request.Columns)
                .Skip(request.Start)
                .Take(request.Length);
            
            var response = DataTablesResponse.Create(request, rows.Count,
                filteredRows.Count(), pagedRows);

            return new DataTablesJsonResult(response);
        }
    }
}

You can see all the information DataTables.js sends from the client is captured and turned into IDataTablesRequest and you can grab whatever you need:

@{
    ViewBag.Title = "Products";
}

<h2>Products</h2>
...
<div class="table-responsive">
    <table id="table-products" class="table">
        ...
    </table>
</div>
...

@section scripts {
    ...

    <script type="text/javascript">
        $(function() {
            $('#table-products').dataTable({
                serverSide: true,
                ajax: {
                    url: '@Url.Action("getList", "product", new { area = "" })',
                    type: 'POST'
                },
                processing: true,
                order: [1, 'asc'],
                columns: [...]
            }).fnSetFilteringDelay(1000);
        });
    </script>
}

Screenshots

Working Demo

https://github.com/davidliang2008/DL.NetCore.EmptySolution

Bonus: extension methods to apply DataTables filtering and sorting

I also created 2 extension methods against IQueryable collection to take care of the filtering and sorting from DataTables.js:

namespace DL.NetCore.EmptySolution.Web.Common.DataTables.Extensions
{
    public static class QueryableExtensions
    {
        public static IQueryable<T> SortBy<T>(this IQueryable<T> source, 
            IEnumerable<IColumn> columns)
        {
            Expression expression = source.Expression;
            bool firstTime = true;

            var sortedColumns = columns
                .Where(x => x.IsSortable && x.Sort != null)
                .OrderBy(x => x.Sort.Order);

            foreach (var sortedColumn in sortedColumns)
            {
                var parameter = Expression.Parameter(typeof(T), "x");
                var selector = Expression.PropertyOrField(parameter, sortedColumn.Field);
                var lambda = Expression.Lambda(selector, parameter);
                var method = sortedColumn.Sort.Direction == SortDirection.Descending
                    ? firstTime ? "OrderByDescending" : "ThenByDescending"
                    : firstTime ? "OrderBy" : "ThenBy";

                expression = Expression.Call(
                    typeof(Queryable), 
                    method,
                    new Type[] { source.ElementType, selector.Type },
                    expression, 
                    Expression.Quote(lambda)
                );

                firstTime = false;
            }
            return firstTime 
                ? source
                : source.Provider.CreateQuery<T>(expression);
        }

        public static IQueryable<T> GlobalFilterBy<T>(this IQueryable<T> source,
            ISearch search, IEnumerable<IColumn> columns)
        {
            if (search == null || String.IsNullOrWhiteSpace(search.Value))
            {
                return source;
            }

            var searchableColumns = columns
                .Where(x => x.IsSearchable);
            if (!searchableColumns.Any())
            {
                return source;
            }

            Expression predicateBody = null;
            var parameter = Expression.Parameter(typeof(T), "x");

            foreach (var column in searchableColumns)
            {
                // We want to build
                //  x.Field != default 
                //  && x.Field.ToString().IndexOf(search.Value, StringComparison.InvariantCultureIgnoreCase) >= 0
                // for each searchable column
                var selector = Expression.PropertyOrField(parameter, column.Field);

                var left = Expression.NotEqual(
                    selector,
                    Expression.Default(selector.Type)
                );

                var toString = Expression.Call(
                    selector,
                    selector.Type.GetMethod("ToString", System.Type.EmptyTypes)
                );

                var indexOf = Expression.Call(
                    toString,
                    typeof(string).GetMethod("IndexOf", new Type[] { typeof(string), typeof(StringComparison) }),
                    Expression.Constant(search.Value),
                    Expression.Constant(StringComparison.InvariantCultureIgnoreCase)
                );

                var right = Expression.GreaterThanOrEqual(
                    indexOf,
                    Expression.Constant(0)
                );

                var andExpression = Expression.AndAlso(left, right);

                predicateBody = predicateBody == null
                    ? andExpression
                    : Expression.OrElse(predicateBody, andExpression);
            }

            if (predicateBody == null)
            {
                return source;
            }

            var lambda = Expression.Lambda<Func<T, bool>>(predicateBody, parameter);

            var whereCallExpression = Expression.Call(
                typeof(Queryable),
                "Where",
                new Type[] { source.ElementType },
                source.Expression,
                lambda
            );

            return source.Provider.CreateQuery<T>(whereCallExpression);
        }
    }
}

They're super handy to take searchable and sortable columns from DataTables.js, dynamically build the expression tree and apply to your source collection!

Sorry this is getting too long ...

Answer 2

i cannot get parameters for sorting, Searching From datatble to my MVC Controller.

If you use F12 developer tool Network to capture the request to NewTest/Prods action method, you would find the server-side processing related data are passed through form data, not in query string part of url.

To get these data in your controller action, you can try:

int draw = Convert.ToInt32(Request.Form["draw"]);
int StartIndex = Convert.ToInt32(Request.Form["start"]);
int PageSize = Convert.ToInt32(Request.Form["length"]);
int SortCol = Convert.ToInt32(Request.Form["order[0][column]"]);
string SortDir = Request.Form["order[0][dir]"];
string SearchField = Request.Form["search[value]"].FirstOrDefault()?.Trim();

Test Result