C#

Filtering jQuery Data Table Server Side Using MVC And Entity Framework

Introduction

There are many ways to represent data in your web application. One of them is using Data Tables using MVC and Entity Framework. In most of the examples available data filtering, sorting and paging has been done on Client-Side. But in a real time scenario when you are working with large data it is not feasible to filter, sort or page data at client side.

Hence, this article shows user step by step how to use Data Table and apply server side filtering, sorting and paging using MVC and Entity Framework.

Tools Used
  • Visual Studio 2015
  • SQL Server Management Studio 2012 with Northwind Database
  • JQuery 3.2.1 (Included in Visual Studio 2015)
  • Bootstrap V3.0 (Included in Visual Studio 2015)
  • JQuery Data Table V1.10.16 (Download from https://datatables.net/download/index)
Create Project in Visual Studio 2015

Open Visual Studio 2015 and go to File - New - Project - Select Visual C# from installed Templates  - Select ASP.Net Web Applications from right side window - Enter Name of your project and Select Location and Click OK.

Image1 

Then from next page select Template MVC add core references for MVC by selecting check boxes below.

Image2

Then Click OK and it will create a web application for you. Build and run project to make sure it runs properly.

Add Libraries to your project

Once the project is created you will see it has automatically added JQuery and Bootstrap javascript libraries to Script folder and all the associated Stylesheet files to Content folder.

If you have downloaded jquery datatable package from https://datatables.net/download/index then copy jquery.datatable.min.js file to Script Table and jquery.dataTables.css file to Content folder.

Make sure you include all the files added to project.

Then go to View folder à select Shared à select _Layout.cshtml and add following code in head element after title element
  1. <link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" />  
  2.   <link href="@Url.Content("~/Content/bootstrap.min.css")" rel="stylesheet" />  
  3.   <link href="@Url.Content("~/Content/jquery.dataTables.css")" rel="stylesheet" />  
  4.   
  5.   
  6.   <script src="@Url.Content("~/Scripts/jquery-3.2.1.min.js")" type="text/javascript"></script>  
  7.   <script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>  
  8.   <script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>  
  9.   <script src="@Url.Content("~/Scripts/bootstrap.min.js")" type="text/javascript"></script>  
  10.   <script src="@Url.Content("~/Scripts/jquery.dataTables.js")" type="text/javascript"></script>  

Setup Database Class using Entity Framework

Once web application and up and running we want to add Data component to it. In real world we would like to create a separate Web api project to create data agnostic web applications but here we are taking beginners approach, hence we will add datamodel to same project.

Create New Folder called DataApi in same project by right clicking on Project Name - Add - New Folder 

Right click on DataApi - Add - ADO.NET Entity Data Model - Specify Name for Data Model - Click OK

Image3 
 
Then Choose Model Content Code First from Database. You can create other Model Contents too based on your requirement. Then create your connection string which will be saved in Web.Config file. Now from Choose your Database Objects and Settings select Tables you need and click Finish.

 

This will add all necessary files to your project.

Add Controllers

Now right click on Controller folder and click on Add - Controller from Menu. Select MVC 5 Controller with actions, using Entity Framework - Click Add.

Image6

Now select Model Class - Data context Class - Click Add.

Image7 
 
Since Generate view has been selected it will automatically generate views under View Folder of project.

Add/Update Razor View

Replace Views - Orders - Index.cshtml code with following.
  1. <script>  
  2.     $(document).ready(function () {  
  3.   
  4.         if ($.fn.DataTable.isDataTable('#tblOrder')) {  
  5.             $('#tblOrder').dataTable().fnDestroy();  
  6.             $('#tblOrder').dataTable().empty();  
  7.   
  8.         }  
  9.   
  10.         var complete = $('#tblOrder').DataTable(  
  11.                 {  
  12.                     "serverSide": true,  
  13.                     "destroy": true,  
  14.                     "processing": true,  
  15.                     "ajax":  
  16.                     {  
  17.                         url: "/Orders/OrderList",  
  18.                         method: "POST"  
  19.                     },  
  20.                     "columns": [  
  21.                         {"data": "CompanyName" },  
  22.                         {"data": "ContactName" },  
  23.                         {"data": "OrderDate"   },  
  24.                        { "data": "RequiredDate" },  
  25.                        { "data": "ShippedDate" },  
  26.                         { "data": "Freight" },  
  27.                        { "data": "ShipName" },  
  28.                         { "data": "ShipAddress" },  
  29.                          { "data": "ShipCity" }  
  30.   
  31.                     ]  
  32.                 }  
  33.   
  34.             );  
  35.   
  36.         /// Following code is for filter input to apply filter only on Enter  
  37.         var itm = $("#tblOrder_filter input")  
  38.   
  39.         itm.unbind();  
  40.         itm.keyup(function (e) {  
  41.             //enter or tab  
  42.             if (e.keyCode == 13) {  
  43.                 complete.search(this.value).draw();  
  44.             }  
  45.         });  
  46.   
  47.   
  48.     });  
  49. </script>  
  50. <h2>Orders</h2>  
  51.   
  52.   
  53. <table class="table" id="tblOrder">  
  54.     <thead>  
  55.         <tr>  
  56.             <th>  
  57.                 CompanyName  
  58.   
  59.             </th>  
  60.             <th>  
  61.                 ContactName  
  62.   
  63.             </th>  
  64.             <th>  
  65.                 OrderDate  
  66.   
  67.             </th>  
  68.             <th>  
  69.                 RequiredDate  
  70.   
  71.             </th>  
  72.             <th>  
  73.                 ShippedDate  
  74.   
  75.             </th>  
  76.             <th>  
  77.                 Freight  
  78.   
  79.             </th>  
  80.             <th>  
  81.                 ShipName  
  82.   
  83.             </th>  
  84.             <th>  
  85.                 ShipAddress  
  86.   
  87.             </th>  
  88.             <th>  
  89.                 ShipCity  
  90.   
  91.             </th>  
  92.   
  93.   
  94.         </tr>  
  95.     </thead>  
  96.     <tbody></tbody>  
  97. </table>  
Add Controller Code for Serverside filtering

Now here is where all the magic happens.

Data table ajax post will call OrderList Action from controller. This will call GetOrderFiletered Method where serverside paging, filtering and sorting will take place.
  1. public List<Order> GetOrderFiltered(string search, string sortOrder, int start, int length, out int TotalCount)  
  2.        {  
  3.   
  4.   
  5.            var result = db.Orders.Include(o => o.Customer).Include(o => o.Employee).Include(o => o.Shipper).Where(p => (search == null || (p.Customer.CompanyName != null && p.Customer.CompanyName.ToLower().Contains(search.ToLower())  
  6.                || p.Customer.ContactName != null && p.Customer.ContactName.ToLower().Contains(search.ToLower())  
  7.                || p.OrderDate != null && p.OrderDate.ToString().ToLower().Contains(search.ToLower())  
  8.                || p.ShippedDate != null && p.ShippedDate.ToString().ToLower().Contains(search.ToLower())  
  9.                || p.RequiredDate != null && p.RequiredDate.ToString().ToLower().Contains(search.ToLower())  
  10.   
  11.                || p.Freight != null && p.Freight.ToString().Contains(search)  
  12.                || p.ShipAddress != null && p.ShipAddress.ToLower().Contains(search.ToLower())  
  13.                || p.ShipName != null && p.ShipName.ToLower().Contains(search.ToLower())  
  14.                || p.ShipCity != null && p.ShipCity.ToLower().Contains(search.ToLower())))  
  15.   
  16.                ).ToList();  
  17.   
  18.            TotalCount = result.Count;  
  19.   
  20.            result = result.Skip(start).Take(length).ToList();  
  21.   
  22.   
  23.            switch (sortOrder)  
  24.            {  
  25.                case "ContactName":  
  26.                    result = result.OrderBy(a => a.Customer.ContactName).ToList();  
  27.                    break;  
  28.                case "CompanyName":  
  29.                    result = result.OrderBy(a => a.Customer.CompanyName).ToList();  
  30.                    break;  
  31.                case "Freight":  
  32.                    result = result.OrderBy(a => a.Freight).ToList();  
  33.                    break;  
  34.                case "ShipName":  
  35.                    result = result.OrderBy(a => a.ShipName).ToList();  
  36.                    break;  
  37.                case "ShipCity":  
  38.                    result = result.OrderBy(a => a.ShipCity).ToList();  
  39.                    break;  
  40.                case "ShipAddress":  
  41.                    result = result.OrderBy(a => a.ShipAddress).ToList();  
  42.                    break;  
  43.                case "CompanyName DESC":  
  44.                    result = result.OrderByDescending(a => a.Customer.CompanyName).ToList();  
  45.                    break;  
  46.                case "ContactName DESC":  
  47.                    result = result.OrderByDescending(a => a.Customer.ContactName).ToList();  
  48.                    break;  
  49.                case "Freight DESC":  
  50.                    result = result.OrderByDescending(a => a.Freight).ToList();  
  51.                    break;  
  52.                case "ShipName DESC":  
  53.                    result = result.OrderByDescending(a => a.ShipName).ToList();  
  54.                    break;  
  55.                case "ShipCity DESC":  
  56.                    result = result.OrderByDescending(a => a.ShipCity).ToList();  
  57.                    break;  
  58.                case "ShipAddress DESC":  
  59.                    result = result.OrderByDescending(a => a.ShipAddress).ToList();  
  60.                    break;  
  61.                case "OrderDate":  
  62.                    result = result.OrderBy(a => a.OrderDate).ToList();  
  63.                    break;  
  64.                case "RequiredDate":  
  65.                    result = result.OrderBy(a => a.RequiredDate).ToList();  
  66.                    break;  
  67.                case "ShippedDate":  
  68.                    result = result.OrderBy(a => a.ShippedDate).ToList();  
  69.                    break;  
  70.                case "OrderDate DESC":  
  71.                    result = result.OrderByDescending(a => a.OrderDate).ToList();  
  72.                    break;  
  73.                case "RequiredDate DESC":  
  74.                    result = result.OrderByDescending(a => a.RequiredDate).ToList();  
  75.                    break;  
  76.                case "ShippedDate DESC":  
  77.                    result = result.OrderByDescending(a => a.ShippedDate).ToList();  
  78.                    break;  
  79.                default:  
  80.                    result = result.AsQueryable().ToList();  
  81.                    break;  
  82.            }  
  83.            return result.ToList();  
  84.        }  
Then OrderList will return Json object.
  1. DTResult<OrderListView> finalresult = new DTResult<OrderListView>  
  2. {  
  3. draw = param.Draw,  
  4. data = OrderList.ToList(),  
  5. recordsFiltered = TotalCount,  
  6. recordsTotal = filtered.Count  
  7. };  

Run Project. 

After running the project you will see the below result.

Image9
 
Please have a look at the attached entire code for more information. 

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs