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.

 

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



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

<link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" />
  <link href="@Url.Content("~/Content/bootstrap.min.css")" rel="stylesheet" />
  <link href="@Url.Content("~/Content/jquery.dataTables.css")" rel="stylesheet" />


  <script src="@Url.Content("~/Scripts/jquery-3.2.1.min.js")" type="text/javascript"></script>
  <script src="@Url.Content("~/Scripts/jquery.validate.min.js")" type="text/javascript"></script>
  <script src="@Url.Content("~/Scripts/jquery.validate.unobtrusive.min.js")" type="text/javascript"></script>
  <script src="@Url.Content("~/Scripts/bootstrap.min.js")" type="text/javascript"></script>
  <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

 

 

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.



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

 

 

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.

<script>
    $(document).ready(function () {

        if ($.fn.DataTable.isDataTable('#tblOrder')) {
            $('#tblOrder').dataTable().fnDestroy();
            $('#tblOrder').dataTable().empty();

        }

        var complete = $('#tblOrder').DataTable(
                {
                    "serverSide": true,
                    "destroy": true,
                    "processing": true,
                    "ajax":
                    {
                        url: "/Orders/OrderList",
                        method: "POST"
                    },
                    "columns": [
                        {"data": "CompanyName" },
                        {"data": "ContactName" },
                        {"data": "OrderDate"   },
                       { "data": "RequiredDate" },
                       { "data": "ShippedDate" },
                        { "data": "Freight" },
                       { "data": "ShipName" },
                        { "data": "ShipAddress" },
                         { "data": "ShipCity" }

                    ]
                }

            );

        /// Following code is for filter input to apply filter only on Enter
        var itm = $("#tblOrder_filter input")

        itm.unbind();
        itm.keyup(function (e) {
            //enter or tab
            if (e.keyCode == 13) {
                complete.search(this.value).draw();
            }
        });


    });
</script>
<h2>Orders</h2>


<table class="table" id="tblOrder">
    <thead>
        <tr>
            <th>
                CompanyName

            </th>
            <th>
                ContactName

            </th>
            <th>
                OrderDate

            </th>
            <th>
                RequiredDate

            </th>
            <th>
                ShippedDate

            </th>
            <th>
                Freight

            </th>
            <th>
                ShipName

            </th>
            <th>
                ShipAddress

            </th>
            <th>
                ShipCity

            </th>


        </tr>
    </thead>
    <tbody></tbody>
</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.

public List<Order> GetOrderFiltered(string search, string sortOrder, int start, int length, out int TotalCount)
       {


           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())
               || p.Customer.ContactName != null && p.Customer.ContactName.ToLower().Contains(search.ToLower())
               || p.OrderDate != null && p.OrderDate.ToString().ToLower().Contains(search.ToLower())
               || p.ShippedDate != null && p.ShippedDate.ToString().ToLower().Contains(search.ToLower())
               || p.RequiredDate != null && p.RequiredDate.ToString().ToLower().Contains(search.ToLower())

               || p.Freight != null && p.Freight.ToString().Contains(search)
               || p.ShipAddress != null && p.ShipAddress.ToLower().Contains(search.ToLower())
               || p.ShipName != null && p.ShipName.ToLower().Contains(search.ToLower())
               || p.ShipCity != null && p.ShipCity.ToLower().Contains(search.ToLower())))

               ).ToList();

           TotalCount = result.Count;

           result = result.Skip(start).Take(length).ToList();


           switch (sortOrder)
           {
               case "ContactName":
                   result = result.OrderBy(a => a.Customer.ContactName).ToList();
                   break;
               case "CompanyName":
                   result = result.OrderBy(a => a.Customer.CompanyName).ToList();
                   break;
               case "Freight":
                   result = result.OrderBy(a => a.Freight).ToList();
                   break;
               case "ShipName":
                   result = result.OrderBy(a => a.ShipName).ToList();
                   break;
               case "ShipCity":
                   result = result.OrderBy(a => a.ShipCity).ToList();
                   break;
               case "ShipAddress":
                   result = result.OrderBy(a => a.ShipAddress).ToList();
                   break;
               case "CompanyName DESC":
                   result = result.OrderByDescending(a => a.Customer.CompanyName).ToList();
                   break;
               case "ContactName DESC":
                   result = result.OrderByDescending(a => a.Customer.ContactName).ToList();
                   break;
               case "Freight DESC":
                   result = result.OrderByDescending(a => a.Freight).ToList();
                   break;
               case "ShipName DESC":
                   result = result.OrderByDescending(a => a.ShipName).ToList();
                   break;
               case "ShipCity DESC":
                   result = result.OrderByDescending(a => a.ShipCity).ToList();
                   break;
               case "ShipAddress DESC":
                   result = result.OrderByDescending(a => a.ShipAddress).ToList();
                   break;
               case "OrderDate":
                   result = result.OrderBy(a => a.OrderDate).ToList();
                   break;
               case "RequiredDate":
                   result = result.OrderBy(a => a.RequiredDate).ToList();
                   break;
               case "ShippedDate":
                   result = result.OrderBy(a => a.ShippedDate).ToList();
                   break;
               case "OrderDate DESC":
                   result = result.OrderByDescending(a => a.OrderDate).ToList();
                   break;
               case "RequiredDate DESC":
                   result = result.OrderByDescending(a => a.RequiredDate).ToList();
                   break;
               case "ShippedDate DESC":
                   result = result.OrderByDescending(a => a.ShippedDate).ToList();
                   break;
               default:
                   result = result.AsQueryable().ToList();
                   break;
           }
           return result.ToList();
       }

Then OrderList will return Json object.

DTResult<OrderListView> finalresult = new DTResult<OrderListView>
{
draw = param.Draw,
data = OrderList.ToList(),
recordsFiltered = TotalCount,
recordsTotal = filtered.Count
};

Run Project. 

After running the project you will see the below result.

 

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