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

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.

[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

Using the Code

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.
   
   
   

2. 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));
     

3. 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. });

4. 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) { }
   6. public DbSet<DemoEntity> Demos { get; set; }
   7. }

5. 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; }
   9. public DemoNestedLevelOneEntity DemoNestedLevelOne { get; set; }
   10. }
   Nested Level One Entity
   
   1. public class DemoNestedLevelOneEntity
   2. {
   3. [Key]
   4. public int Id { get; set; }
   6. public short? Experience { get; set; }
   7. public int? Extn { get; set; }
   9. public DemoNestedLevelTwoEntity DemoNestedLevelTwo { get; set; }
   10. }
   Nested Level Two Entity
   
   1. public class DemoNestedLevelTwoEntity
   2. {
   3. [Key]
   4. public int Id { get; set; }
   6. public DateTime? StartDate { get; set; }
   7. public long? Salary { get; set; }
   8. }

6. 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. }
   9. public static async Task AddTestData(Fingers10DbContext context)
   10. {
   11. if(context.Demos.Any())
   12. {
   13. // Already has data
   14. return;
   15. }
   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);
   172. await context.SaveChangesAsync();
   173. }
   174. }

7. 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. }
   10. public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>
   11. WebHost.CreateDefaultBuilder(args)
   12. .UseStartup<Startup>();
   14. private static void InitializeDatabase(IWebHost host)
   15. {
   16. using (var scope = host.Services.CreateScope())
   17. {
   18. var services = scope.ServiceProvider;
   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. }

8. 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.
   
   
   

9.Now add a reference to the JqueryDataTables.AspNetCoreWeb TagHelpers in the _ViewImports.cshtml fille.

1. @addTagHelper *, JqueryDataTables.ServerSide.AspNetCoreWeb

11. 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>`
12. 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;
    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.

13. 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.

14. 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.
    
    
    
    
    Note
    If you’re not using name property, then this will default to eq search operation.

15. 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. });

17. 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. });
18. 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.
    
    
    
    
    
19. 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);
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.

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.
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.

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.

20. 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));
    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. }
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

Thanks for reading.

• Full updated documentation can be found here in my GitHub Page - JqueryDataTablesAspNetCoreServerSide
• Download sample demo code for this article from my GitHub Repo - JqueryDataTablesServerSideDemo

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