Post
Ask Question

jQuery DataTables With ASP.NET Core Server Side Processing

Hello developers, welcome to my first ever article. I feel privileged and happy to help and teach you this. In this article we will try to look at Jquery DataTables with Asp.Net Core Server Side Processing. The primary goal of this article is to make multiple column server-side sorting and searching along with pagination and excel export to be dynamic and easy to implement. There is a plethora of articles available on the same topic. This article is an attempt to explain in my own words with my own implementation using the Nuget Package that I developed.
 

Background

 
DataTables is a plug-in for the jQuery JavaScript library. It helps us to add amazing functionalities to a simple HTML table with minimal lines of code. That’s cool and great for simple client-side data. Having said that, once the data grows, it becomes difficult to render all the data in the client side as it has performance issues. So, we’re going to focus on how to handle this on the server side with my implementation. This method is super simple to implement and saves you effort and time.
 
jQuery DataTables With ASP.NET Core Server Side Processing
 
Note
This tutorial contains example for both Ajax Get and Ajax Post Server-Side Configuration.
 
Warning
 
If we are RESTful strict, we should use GET Method to get information not POST but I prefer this way to avoid limitations related to form data through the query string, so up to you if you want to use GET. I recommend using AJAX GET only if your DataTable has very less number of columns. As Jquery DataTables AJAX GET requests produces too large query string which will be rejected by server. 
 
Download sample demo code for this article from my GitHub Repo - JqueryDataTablesServerSideDemo
 

A look at Asp.Net Core Server Side

 
To add the above functionality to your DataTable all you need to do is to write a little bit of client side jquery logics and add few attributes to your model properties. That’s very simple right? Trust me I have written all the processing logics as JqueryDataTables.ServerSide.AspNetCoreWeb Nuget Package which will do all the heavy lifting works for you. You just need to add this package into your project.
 
Root Model
  1. public class Demo  
  2.     {  
  3.         [SearchableInt]  
  4.         [Sortable]  
  5.         public int Id { get; set; }  
  6.   
  7.         [SearchableString]  
  8.         [Sortable(Default = true)]  
  9.         public string Name { get; set; }  
  10.   
  11.         [SearchableString]  
  12.         [Sortable]  
  13.         public string Position { get; set; }  
  14.   
  15.         [Display(Name = "Office")]
  16.         [SearchableString(EntityProperty = "Office")]  
  17.         [Sortable(EntityProperty = "Office")]  
  18.         public string Offices { get; set; }  
  19.   
  20.         [NestedSearchable]  
  21.         [NestedSortable]  
  22.         public DemoNestedLevelOne DemoNestedLevelOne { get; set; }  
  23.     }  
Nested Level One Model
  1. public class DemoNestedLevelOne  
  2.     {  
  3.         [SearchableShort]  
  4.         [Sortable]  
  5.         public short? Experience { get; set; }  
  6.   
  7.         [DisplayName("Extn")]
  8.         [SearchableInt(EntityProperty = "Extn")]  
  9.         [Sortable(EntityProperty = "Extn")]  
  10.         public int? Extension { get; set; }  
  11.   
  12.         [NestedSearchable(ParentEntityProperty = "DemoNestedLevelTwo")]  
  13.         [NestedSortable(ParentEntityProperty = "DemoNestedLevelTwo")]  
  14.         public DemoNestedLevelTwo DemoNestedLevelTwos { get; set; }  
  15.     }  
Nested Level Two Model 
  1. public class DemoNestedLevelTwo  
  2.     {  
  3.         [SearchableDateTime(EntityProperty = "StartDate")]  
  4.         [Sortable(EntityProperty = "StartDate")]  
  5.         [DisplayName("Start Date")]  
  6.         public DateTime? StartDates { get; set; }  
  7.   
  8.         [SearchableLong]  
  9.         [Sortable]  
  10.         public long? Salary { get; set; }  
  11.     }  
Note
If view model properties have different name than entity model then, you can still do mapping using (EntityProperty = 'YourEntityPropertyName'). If they are same then you can ignore this.

As shown in the above code, you can enable searching/sorting to the columns by adding [Searchable]/[Sortable] attributes to your model properties. [NestedSortable]/[NestedSearchable] attributes adds sorting/searching to complex model/properties.

[Sortable] adds the sorting functionality to the columns while [Sortable(Default = true)] will make a default initial sorting of your records.

    • [Sortable] 
    • [Sortable(Default = true)]
    • [NestedSortable]

[Searchable] adds the searching functionality to the columns. Searching has some flavors added to it based on the data type of the column to help build the search expression dynamically. I have created

    • [Searchable] 
    • [SearchableString]
    • [SearchableInt]
    • [SearchableShort]
    • [SearchableDecimal]
    • [SearchableDouble]
    • [SearchableDateTime]
    • [SearchableLong]
    • [NestedSearchable]

Column Names: 

Column names in HTML Table/Excel Export can be configured using the below attributes
* `[Display(Name = "")]`
* `[DisplayName(“”)]`

 

Using the Code

 
Though I will be explaining this demo with Asp.Net Core MVC, this tutorial is applicable to Asp.Net Core Razor Pages and Asp.Net Core Web API applications as well.

Note
I’m using Asp.Net Core 3.0

  1. I started with an empty solution and added the necessary Nuget Packages to get the demo up and running.

    jQuery DataTables With ASP.NET Core Server Side Processing
  1. Let’s begin with the server-side configuration and database setup. For jQuery DataTables to work with Asp.Net Core, we first need to call setup ConfigureServices in the Startup.cs. I have also added AutoMapper to take care of mappings and AddSession which I’ll explain later.

    For AutoMapper to work add the following Nuget Packages:
    • AutoMapper
    • Extensions.Microsoft.DependencyInjection       

             For Asp.Net Core 3.0:

      If you are using System.Text.Json, then setup your ConfigureServices as below:

      1. services.AddControllersWithViews()  
      2.             .AddJsonOptions(options =>  
      3.             {  
      4.                 options.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());  
      5.                 options.JsonSerializerOptions.PropertyNamingPolicy = null;  
      6.             });  
      7.     services.AddSession();  
      8.     services.AddAutoMapper(typeof(Startup));  
      If you are using Json.Net, then setup your ConfigureServices as below:

      1. services.AddControllersWithViews()  
      2.             .AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());  
      3.     services.AddSession();  
      4.     services.AddAutoMapper(typeof(Startup));  
      For Asp.Net Core 2.x, setup your ConfigureServices as below:
      1. services.AddMvc()
      2.         .AddJsonOptions(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());  
      3. services.AddSession(); 
      4. services.AddAutoMapper(typeof(Startup));  
  1. I’ll be using an InMemoryDatabase for this demo.

    Note
    If you already have a datatabase setup and you’re familiar with database implementation you can skip to step 8.
    1.             // Use in-memory database for quick dev and testing  
    2.             services.AddDbContext<Fingers10DbContext>(  
    3.                 options =>  
    4.                 {  
    5.                     options.UseInMemoryDatabase("fingers10db");  
    6.                 });  
  1. The Fingers10DbContext class has a DemoEntityDbSet, which will be used to query the datatabase.
    1.     public class Fingers10DbContext:DbContext  
    2.     {  
    3.         public Fingers10DbContext(DbContextOptions options)  
    4.             : base(options) { }  
    5.   
    6.         public DbSet<DemoEntity> Demos { get; set; }  
    7.     }  
  1. The DemoEntity class has the following fields.

    Root Entity
    1. public class DemoEntity  
    2.     {  
    3.         [Key]  
    4.         public int Id { get; set; }  
    5.         public string Name { get; set; }  
    6.         public string Position { get; set; }  
    7.         public string Office { get; set; }  
    8.   
    9.         public DemoNestedLevelOneEntity DemoNestedLevelOne { get; set; }  
    10.     }  
    Nested Level One Entity
    1. public class DemoNestedLevelOneEntity  
    2.     {  
    3.         [Key]  
    4.         public int Id { get; set; }  
    5.   
    6.         public short? Experience { get; set; }  
    7.         public int? Extn { get; set; }  
    8.   
    9.         public DemoNestedLevelTwoEntity DemoNestedLevelTwo { get; set; }  
    10.     }  
    Nested Level Two Entity
    1. public class DemoNestedLevelTwoEntity  
    2.     {  
    3.         [Key]  
    4.         public int Id { get; set; }  
    5.   
    6.         public DateTime? StartDate { get; set; }  
    7.         public long? Salary { get; set; }  
    8.     }  
  1. Now let’s add a static SeedData class to seed the data and call it from the Program.cs.
    1. public static class SeedData  
    2.     {  
    3.         public static async Task InitializeAsync(IServiceProvider services)  
    4.         {  
    5.             await AddTestData(  
    6.                 services.GetRequiredService<Fingers10DbContext>());  
    7.         }  
    8.   
    9.         public static async Task AddTestData(Fingers10DbContext context)  
    10.         {  
    11.             if(context.Demos.Any())  
    12.             {  
    13.                 // Already has data  
    14.                 return;  
    15.             }  
    16.   
    17.             var testData = new List<DemoEntity>()  
    18.             {  
    19.                 new DemoEntity {  
    20.                     Name = "Airi Satou",  
    21.                     Position = "Accountant",  
    22.                     Office = "Tokyo",  
    23.                     DemoNestedLevelOne = new DemoNestedLevelOneEntity  
    24.                     {  
    25.                         Experience = null,  
    26.                         Extn = null,  
    27.                         DemoNestedLevelTwo = new DemoNestedLevelTwoEntity  
    28.                         {  
    29.                             StartDate = null,  
    30.                             Salary = null  
    31.                         }  
    32.                     }  
    33.                 },  
    34.                 new DemoEntity {  
    35.                     Name = "Angelica Ramos",  
    36.                     Position = "Chief Executive Officer (CEO)",  
    37.                     Office = "London",  
    38.                     DemoNestedLevelOne = new DemoNestedLevelOneEntity  
    39.                     {  
    40.                         Experience = 1,  
    41.                         Extn = 5797,  
    42.                         DemoNestedLevelTwo = new DemoNestedLevelTwoEntity  
    43.                         {  
    44.                             StartDate = new DateTime(2009,10,09),  
    45.                             Salary = 1200000  
    46.                         }  
    47.                     }  
    48.                 },  
    49.                 new DemoEntity {  
    50.                     Name = "Ashton Cox",  
    51.                     Position = "Junior Technical Author",  
    52.                     Office = "San Francisco",  
    53.                     DemoNestedLevelOne = new DemoNestedLevelOneEntity  
    54.                     {  
    55.                         Experience = 2,  
    56.                         Extn = 1562,  
    57.                         DemoNestedLevelTwo = new DemoNestedLevelTwoEntity  
    58.                         {  
    59.                             StartDate = new DateTime(2009,01,12),  
    60.                             Salary = 86000  
    61.                         }  
    62.                     }  
    63.                 },  
    64.                 new DemoEntity {  
    65.                     Name = "Bradley Greer",  
    66.                     Position = "Software Engineer",  
    67.                     Office = "London",  
    68.                     DemoNestedLevelOne = new DemoNestedLevelOneEntity  
    69.                     {  
    70.                         Experience = 3,  
    71.                         Extn = 2558,  
    72.                         DemoNestedLevelTwo = new DemoNestedLevelTwoEntity  
    73.                         {  
    74.                             StartDate = new DateTime(2012,10,13),  
    75.                             Salary = 132000  
    76.                         }  
    77.                     }  
    78.                 },  
    79.                 new DemoEntity {  
    80.                     Name = "Brenden Wagner",  
    81.                     Position = "Software Engineer",  
    82.                     Office = "San Francisco",  
    83.                     DemoNestedLevelOne = new DemoNestedLevelOneEntity  
    84.                     {  
    85.                         Experience = 4,  
    86.                         Extn = 1314,  
    87.                         DemoNestedLevelTwo = new DemoNestedLevelTwoEntity  
    88.                         {  
    89.                             StartDate = new DateTime(2011,06,07),  
    90.                             Salary = 206850  
    91.                         }  
    92.                     }  
    93.                 },  
    94.                 new DemoEntity {  
    95.                     Name = "Brielle Williamson",  
    96.                     Position = "Integration Specialist",  
    97.                     Office = "New York",  
    98.                     DemoNestedLevelOne = new DemoNestedLevelOneEntity  
    99.                     {  
    100.                         Experience = 5,  
    101.                         Extn = 4804,  
    102.                         DemoNestedLevelTwo = new DemoNestedLevelTwoEntity  
    103.                         {  
    104.                            StartDate = new DateTime(2012,12,02),  
    105.                            Salary = 372000  
    106.                         }  
    107.                     }  
    108.                 },  
    109.                 new DemoEntity {  
    110.                     Name = "Bruno Nash",  
    111.                     Position = "Software Engineer",  
    112.                     Office = "London",  
    113.                     DemoNestedLevelOne = new DemoNestedLevelOneEntity  
    114.                     {  
    115.                         Experience = 6,  
    116.                         Extn = 6222,  
    117.                         DemoNestedLevelTwo = new DemoNestedLevelTwoEntity  
    118.                         {  
    119.                             StartDate = new DateTime(2011,05,03),  
    120.                             Salary = 163500  
    121.                         }  
    122.                     }  
    123.                 },  
    124.                 new DemoEntity {  
    125.                     Name = "Caesar Vance",  
    126.                     Position = "Pre-Sales Support",  
    127.                     Office = "New York",  
    128.                     DemoNestedLevelOne = new DemoNestedLevelOneEntity  
    129.                     {  
    130.                         Experience = 7,  
    131.                         Extn = 8330,  
    132.                         DemoNestedLevelTwo = new DemoNestedLevelTwoEntity  
    133.                         {  
    134.                             StartDate = new DateTime(2011,12,12),  
    135.                             Salary = 106450  
    136.                         }  
    137.                     }  
    138.                 },  
    139.                 new DemoEntity {  
    140.                     Name = "Cara Stevens",  
    141.                     Position = "Sales Assistant",  
    142.                     Office = "New York",  
    143.                     DemoNestedLevelOne = new DemoNestedLevelOneEntity  
    144.                     {  
    145.                         Experience = 8,  
    146.                         Extn = 3990,  
    147.                         DemoNestedLevelTwo = new DemoNestedLevelTwoEntity  
    148.                         {  
    149.                             StartDate = new DateTime(2011,12,06),  
    150.                             Salary = 145600  
    151.                         }  
    152.                     }  
    153.                 },  
    154.                 new DemoEntity {  
    155.                     Name = "Cedric Kelly",  
    156.                     Position = "Senior Javascript Developer",  
    157.                     Office = "Edinburgh",  
    158.                     DemoNestedLevelOne = new DemoNestedLevelOneEntity  
    159.                     {  
    160.                         Experience = 9,  
    161.                         Extn = 6224,  
    162.                         DemoNestedLevelTwo = new DemoNestedLevelTwoEntity  
    163.                         {  
    164.                             StartDate = new DateTime(2012,03,29),  
    165.                             Salary = 433060  
    166.                         }  
    167.                     }  
    168.                 }  
    169.             };  
    170.             context.Demos.AddRange(testData);  
    171.   
    172.             await context.SaveChangesAsync();  
    173.         }  
    174.     }  
  1. Inside the Program.cs, let’s call InitializeDatabase method from the Main method to seed data into the database.
    1.     public class Program  
    2.     {  
    3.         public static void Main(string[] args)  
    4.         {  
    5.             var host = CreateWebHostBuilder(args).Build();  
    6.             InitializeDatabase(host);  
    7.             host.Run();  
    8.         }  
    9.   
    10.         public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>  
    11.             WebHost.CreateDefaultBuilder(args)  
    12.                 .UseStartup<Startup>();  
    13.   
    14.         private static void InitializeDatabase(IWebHost host)  
    15.         {  
    16.             using (var scope = host.Services.CreateScope())  
    17.             {  
    18.                 var services = scope.ServiceProvider;  
    19.   
    20.                 try  
    21.                 {  
    22.                     SeedData.InitializeAsync(services).Wait();  
    23.                 }  
    24.                 catch (Exception ex)  
    25.                 {  
    26.                     var logger = services.GetRequiredService<ILogger<Program>>();  
    27.                     logger.LogError(ex, "An error occurred seeding the database.");  
    28.                 }  
    29.             }  
    30.         }  
    31.     }  
  1. Here comes the DataTables part. First install the required client-side libraries for Jquery DataTables to work. I’m using LibMan to install JQuery DataTables along with Bootstrap, jQuery and js. And don’t forget to add reference to the files in your HTML.

    jQuery DataTables With ASP.NET Core Server Side Processing
      9.Now add a reference to the JqueryDataTables.AspNetCoreWeb TagHelpers in the _ViewImports.cshtml fille.
  1. @addTagHelper *, JqueryDataTables.ServerSide.AspNetCoreWeb     

  1.      Now add <jquery-datatables> in your html as shown below.

    1. <jquery-datatables id="fingers10"    
    2.                    class="table table-sm table-dark table-bordered table-hover"    
    3.                    model="@Model"    
    4.                    thead-class="text-center"    
    5.                    enable-searching="true"    
    6.                    search-row-th-class="p-0"    
    7.                    search-input-class="form-control form-control-sm"    
    8.                    search-input-style="width:100%"    
    9.                    search-input-placeholder-prefix="Search">    
    10. </jquery-datatables>  
    * id - to add id to the html table
    * class - to apply the given css class to the html table
    * model - view model with properties to generate columns for html table
    * thead-class - to apply the given css class to the `<thead>` in html table
    * enable-searching - `true` to add search inputs to the `<thead>` and `false` to remove search inputs from the `<thead>`
    * search-row-th-class - to apply the given css class to the search inputs row of the `<thead>` in the html table
    * search-input-class - to apply the given css class to the search input controls added in each column inside `<thead>`
    * search-input-style - to apply the given css styles to the search input controls added in each column inside `<thead>`
    * search-input-placeholder-prefix - to apply your placeholder text as prefix in search input controls in each column inside `<thead>` 
     
  2. Now initialize DataTable as shown below, make sure to add serverSide: true and orderCellsTop: true; serverSide informs DataTable that the data will be coming from the server from the URL mentioned in ajax post and orderCellsTop places the sorting icons to the first row inside the thead. You can also pass additional parameters to the server using AdditionalValues property as string and cast back to required type in server and use those for any manipulations.

    Ajax POST Configuration
    1. var table = $('#fingers10').DataTable({  
    2.             language: {  
    3.                 processing: "Loading Data...",  
    4.                 zeroRecords: "No matching records found"  
    5.             },  
    6.             processing: true,  
    7.             serverSide: true,  
    8.             orderCellsTop: true,  
    9.             autoWidth: true,  
    10.             deferRender: true,  
    11.             lengthMenu: [5, 10, 15, 20],  
    12.             dom: '<"row"<"col-sm-12 col-md-6"B><"col-sm-12 col-md-6 text-right"l>><"row"<"col-sm-12"tr>><"row"<"col-sm-12 col-md-5"i><"col-sm-12 col-md-7"p>>',  
    13.             buttons: [  
    14.                 {  
    15.                     text: 'Export to Excel',  
    16.                     className: 'btn btn-sm btn-dark',  
    17.                     action: function (e, dt, node, config) {  
    18.                         window.location.href = "/Home/GetExcel";  
    19.                     },  
    20.                     init: function (api, node, config) {  
    21.                         $(node).removeClass('dt-button');  
    22.                     }  
    23.                 }  
    24.             ],  
    25.             ajax: {  
    26.                 type: "POST",  
    27.                 url: '/Home/LoadTable/',  
    28.                 contentType: "application/json; charset=utf-8",  
    29.                 async: true,  
    30.                 headers: {  
    31.                     "XSRF-TOKEN": document.querySelector('[name="__RequestVerificationToken"]').value  
    32.                 },  
    33.                 data: function (data) {  
    34.                     let additionalValues = [];  
    35.                     additionalValues[0] = "Additional Parameters 1";  
    36.                     additionalValues[1] = "Additional Parameters 2";  
    37.                     data.AdditionalValues = additionalValues;  
    38.   
    39.                     return JSON.stringify(data);  
    40.                 }  
    41.             },  
    42.             columns: [  
    43.                 ...
    44.             ]  
    45.         });  
    Ajax GET Configuration

    For AJAX GET configuration, simply change the `ajax` and `buttons` options as follows,
    1. buttons: [  
    2.                 {  
    3.                     text: 'Export to Excel',  
    4.                     className: 'btn btn-sm btn-dark',  
    5.                     action: function (e, dt, node, config) {  
    6.                         var data = table.ajax.params();  
    7.                         var x = JSON.stringify(data, null, 4);  
    8.                         window.location.href = "/Home/GetExcel?" + $.param(data);  
    9.                     },  
    10.                     init: function (api, node, config) {  
    11.                         $(node).removeClass('dt-button');  
    12.                     }  
    13.                 }  
    14.             ],  
    15.     ajax: {  
    16.                 url: '/Home/LoadTable/',  
    17.                 data: function (data) {  
    18.                     return $.extend({}, data, {  
    19.                         "additionalValues[0]": "Additional Parameters 1",  
    20.                         "additionalValues[1]": "Additional Parameters 2"  
    21.                     });  
    22.                 }  
    23.            }  
Now inside the columns array, configure the columns by specifying the data and name property for the individual column as shown below.
  1. columns: [  
  2.                 {  
  3.                     data: "Id",  
  4.                     name: "eq",  
  5.                     visible: false,  
  6.                     searchable: false  
  7.                 },  
  8.                 {  
  9.                     data: "Name",  
  10.                     name: "co"  
  11.                 },  
  12.                 {  
  13.                     data: "Position",  
  14.                     name: "co"  
  15.                 },  
  16.                 {  
  17.                     data: "Offices",  
  18.                     name: "eq"  
  19.                 },  
  20.                 {  
  21.                     data: "DemoNestedLevelOne.Experience",  
  22.                     name: "eq"  
  23.                 },  
  24.                 {  
  25.                     data: "DemoNestedLevelOne.Extension",  
  26.                     name: "eq"  
  27.                 },  
  28.                 {  
  29.                     data: "DemoNestedLevelOne.DemoNestedLevelTwos.StartDates",  
  30.                     render: function (data, type, row) {  
  31.                         if (data)  
  32.                             return window.moment(data).format("DD/MM/YYYY");  
  33.                         else  
  34.                             return null;  
  35.                     },  
  36.                     name: "gt"  
  37.                 },  
  38.                 {  
  39.                     data: "DemoNestedLevelOne.DemoNestedLevelTwos.Salary",  
  40.                     name: "lte"  
  41.                 }  
  42.             ]  
  1. The data property must match the name of the property in the DemoModel.cs and this is case sensitive. Title is the readable form of your Model property describing the column.
  1. I’m using the name property to send the type of search that I need to perform in each column as I’m not able to find any other properties with DataTable Columns.

    jQuery DataTables With ASP.NET Core Server Side Processing

    Note
    If you’re not using name property, then this will default to eq search operation.
  1. Before we move to server-side implementation, add the following script to perform search on press of enter key. Make sure to replace id with your table id.
    1. table.columns().every(function (index) {  
    2.             $('#fingers10 thead tr:last th:eq(' + index + ') input')  
    3.                 .on('keyup',  
    4.                     function (e) {  
    5.                         if (e.keyCode === 13) {  
    6.                             table.column($(this).parent().index() + ':visible').search(this.value).draw();  
    7.                         }  
    8.                     });  
    9.         });  
  1. If you need to perform search on press of Tab Key instead of Enter Key, ignore the above script and add the below script.
    1. $('#fingers10 thead tr:last th:eq(' + index + ') input')  
    2.     .on('blur',  
    3.     function () {  
    4.     table.column($(this).parent().index() + ':visible').search(this.value).draw();  
    5.        });  
  2. Now for this to work on the server side, install the Nuget Package – JqueryDataTables.ServerSide.AspNetCoreWeb which I have created to do all the heavy lifting for you.


  3. Now add JqueryDataTablesParameters class as a parameter to your action method as shown below. And return the data back to DataTable as a JsonResult using JqueryDataTablesResult<T> class as shown below.

    Note
    Return data is of IEnumerable<T> Type.
 AJAX POST Configuration
  1. [HttpPost]    
  2.         public async Task<IActionResult> LoadTable([FromBody]JqueryDataTablesParameters param)    
  3.         {    
  4.             try    
  5.             {    
  6.                 // `param` is stored in session to be used for excel export. This is required only for AJAX POST.  
  7.                 // Below session storage line can be removed if you're not using excel export functionality.  
  8.                 HttpContext.Session.SetString(nameof(JqueryDataTablesParameters), JsonSerializer.Serialize(param));   
  9.                 var results = await _demoService.GetDataAsync(param);    
  10.     
  11.                 return new JsonResult(new JqueryDataTablesResult<Demo> {    
  12.                     Draw = param.Draw,    
  13.                     Data = results.Items,    
  14.                     RecordsFiltered = results.TotalSize,    
  15.                     RecordsTotal = results.TotalSize    
  16.                 });    
  17.             } catch(Exception e)    
  18.             {    
  19.                 Console.Write(e.Message);    
  20.                 return new JsonResult(new { error = "Internal Server Error" });    
  21.             }    
  22.         }   

Note
Serialize and save the param model in Session to be used for Excel Export. This needs to be done for Post Request only.

AJAX GET Configuration
  1. public async Task<IActionResult> LoadTable([ModelBinder(typeof(JqueryDataTablesBinder))] JqueryDataTablesParameters param)  
  2.         {  
  3.             try  
  4.             {  
  5.                 var results = await _demoService.GetDataAsync(param);  
  6.   
  7.                 return new JsonResult(new JqueryDataTablesResult<Demo> {  
  8.                     Draw = param.Draw,  
  9.                     Data = results.Items,  
  10.                     RecordsFiltered = results.TotalSize,  
  11.                     RecordsTotal = results.TotalSize  
  12.                 });  
  13.             } catch(Exception e)  
  14.             {  
  15.                 Console.Write(e.Message);  
  16.                 return new JsonResult(new { error = "Internal Server Error" });  
  17.             }  
  18.         }  
Now let’s focus on the Demo Service to read the data from the database. I’m using Asp.Net Core built in dependency injection to inject dbcontext and automapper mapping configuration.
  1. public class DefaultDemoService:IDemoService  
  2.     {  
  3.         private readonly Fingers10DbContext _context;  
  4.         private readonly IConfigurationProvider _mappingConfiguration;  
  5.   
  6.         public DefaultDemoService(Fingers10DbContext context,IConfigurationProvider mappingConfiguration)  
  7.         {  
  8.             _context = context;  
  9.             _mappingConfiguration = mappingConfiguration;  
  10.         }  
  11.   
  12.         public async Task<JqueryDataTablesPagedResults<Demo>> GetDataAsync(JqueryDataTablesParameters table)  
  13.         {  
  14.             IQueryable<DemoEntity> query = _context.Demos  
  15.                                                    .AsNoTracking()  
  16.                                                    .Include(x => x.DemoNestedLevelOne)  
  17.                                                    .ThenInclude(y => y.DemoNestedLevelTwo);  
  18.   
  19.             query = SearchOptionsProcessor<Demo,DemoEntity>.Apply(query,table.Columns);  
  20.             query = SortOptionsProcessor<Demo,DemoEntity>.Apply(query,table);  
  21.   
  22.             var size = await query.CountAsync();  
  23.   
  24.             var items = await query  
  25.                 .AsNoTracking()  
  26.                 .Skip((table.Start / table.Length) * table.Length)  
  27.                 .Take(table.Length)  
  28.                 .ProjectTo<Demo>(_mappingConfiguration)  
  29.                 .ToArrayAsync();  
  30.   
  31.             return new JqueryDataTablesPagedResults<Demo> {  
  32.                 Items = items,  
  33.                 TotalSize = size  
  34.             };  
  35.         }  
  36.     }  
Let’s first create an IQueryable<TEntity> query variable to hold the DemoEntity DbSet. Now use Static SearchOptionsProcessor<T,TEntity> with DemoModel and DemoEntity and call the Apply method with the query and table columns as parameters to dynamically generate and apply search expression to the query. After filtering do the same with SortOptionsProcessor<T,TEntity> to dynamically generate and apply sort expression to the query. Now let’s apply pagination from the table parameter which will have the DataTable state and use automapperProjectTo to project to the DemoModel. For this mapping to happen we need to write a mapping profile which I have declared inside Infrastructure folder inside the solution. And return the result as JqueryDataTablesPagedResults.         
  1. public class MappingProfile : Profile  
  2.     {  
  3.         public MappingProfile()  
  4.         {  
  5.             CreateMap<DemoEntity, Demo>()  
  6.                 .ForMember(dest => dest.Offices, opts => opts.MapFrom(src => src.Office));  
  7.   
  8.             CreateMap<DemoNestedLevelOneEntity, DemoNestedLevelOne>()  
  9.                 .ForMember(dest => dest.Extension, opts => opts.MapFrom(src => src.Extn))  
  10.                 .ForMember(dest => dest.DemoNestedLevelTwos, opts => opts.MapFrom(src => src.DemoNestedLevelTwo));  
  11.   
  12.             CreateMap<DemoNestedLevelTwoEntity, DemoNestedLevelTwo>()  
  13.                 .ForMember(dest => dest.StartDates, opts => opts.MapFrom(src => src.StartDate));  
  14.   
  15.             CreateMap<Demo, DemoExcel>();  
  16.         }  
  17.     }  
    Note
    If you’re having Services in a separate project, then create an instance of SearchOptionsProcessor and SortOptionsProcessor inside the controller action method and pass it as parameters to your service calls.
  1. That’s it -- now your DataTable works with server-side dynamic multiple column searching and sorting with pagination.

    Now for exporting the filtered and sorted data as an excel file, add GetExcel action method in your controller as shown below. Return the data as JqueryDataTablesExcelResult<T> by passing filtered/ordered data, excel sheet name and excel file name. My Nuget package will take care of converting your data as excel file and return it back to browser.

    AJAX POST Configuration
    1. public async Task<IActionResult> GetExcel()  
    2.         {  
    3.             var param = HttpContext.Session.GetString(nameof(JqueryDataTablesParameters));  
    4.   
    5.             var results = await _demoService.GetDataAsync(JsonSerializer.Deserialize<JqueryDataTablesParameters>(param)); 
    6.             return new JqueryDataTablesExcelResult<DemoExcel>(_mapper.Map<List<DemoExcel>>(results.Items), "Demo Sheet Name", "Fingers10");  
    7.         }  
    2. Note
    Get the params stored in Session as shown in step 17 and Deserialize and use it to get the filtered/ordered data. This needs to be done for Post Request only. If you want all the results in excel export without pagination, then please write a separate service method to retrive data without using Take() and Skip().

    AJAX GET Configuration

    1. public async Task<IActionResult> GetExcel([ModelBinder(typeof(JqueryDataTablesBinder))] JqueryDataTablesParameters param)  
    2.         {  
    3.             var results = await _demoService.GetDataAsync(param);  
    4.             return new JqueryDataTablesExcelResult<DemoExcel>(_mapper.Map<List<DemoExcel>>(results.Items), "Demo Sheet Name", "Fingers10");  
    5.         }   

Point of Interest

 
This article was created as a tutorial for developers who are working in Jquery DataTables with Asp.Net Core as a Server Side and are totally new to this. Experienced developers might find this article quite mundane. This article is more like a first step for the beginners to explore and expand. I hope this article is useful for the beginners and intermediate developers and this has a lot of scope to improve.

Thanks for reading.

Add a star to my repo if this saved you effort and time. 

Share via LinkedIn, Twitter, Facebook and WhatsApp to spread the knowledge.


Similar Articles