Server-Side Processing With Custom Range Filtering Using MVC And Entity Framework

public ActionResult Filter()
{
    return View();
}

@{
    ViewBag.Title = "Satyaprakash - Filter Records";
}
<h2 style="color: blue">Satyaprakash-Server Side Processing With Custom Range Filtering</h2>
<style>
    table {
        font-family: arial, sans-serif;
        border-collapse: collapse;
        width: 100%;
    }
    td, th {
        border: 1px solid #dddddd;
        text-align: left;
        padding: 8px;
    }
    tr:nth-child(even) {
        background-color: #dddddd;
    }
    .custom-loader-color {
        color: #fff !important;
        font-size: 40px !important;
    }
    .custom-loader-background {
        background-color: #f60 !important;
    }
    .custom-middle-align {
        vertical-align: middle !important;
    }
</style>
<div style="width:90%; margin:0 auto">
    <div style="background-color:#f5f5f5; padding:20px">
        <h2 style="color: blue">Filter Records</h2>
        <table>
            <tbody>
                <tr>
                    <td style="color: blue">City</td>
                    <td><input type="text" class="form-control" id="txtCity" /></td>
                    <td style="color: blue">State</td>
                    <td>
                        <select id="ddState" class="btn btn-primary dropdown-toggle" type="button" data-toggle="dropdown">
                            <option value="">All States</option>
                            <option value="Karnataka">Karnataka</option>
                            <option value="Andhra Pradesh">Andhra Pradesh</option>
                            <option value="Georgia">Georgia</option>
                            <option value="Uttar Pradesh">Uttar Pradesh</option>
                            <option value="Odisha">Odisha</option>
                        </select>
                    </td>
                    <td>
                        <input type="button" class="btn btn-success btn-md" value="Filter" id="btnFilter" />
                    </td>
                </tr>
            </tbody>
        </table>
    </div>
    @* jQuery DataTables *@
    <div style="width:90%; margin:0 auto;">
        <table id="myTable" class="table table-responsive table-striped">
            <thead>
                <tr>
                    <th style="background-color: Yellow;color: blue">First Name</th>
                    <th style="background-color: Yellow;color: blue">Last Name</th>
                    <th style="background-color: Yellow;color: blue">Age</th>
                    <th style="background-color: Yellow;color: blue">Address</th>
                    <th style="background-color: Yellow;color: blue">City</th>
                    <th style="background-color: Yellow;color: blue">State</th>
                </tr>
            </thead>
        </table>
    </div>
</div>
@* Load bootstrap datatable css *@
<link href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/css/bootstrap.css" rel="stylesheet" />
<link href="//cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css" rel="stylesheet" />
@* Load bootstrap datatable js and initialize DataTable *@
@section Scripts {
    <script src="//code.jquery.com/jquery-3.3.1.js"></script>
    <script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script src="//cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>
    <script>
        $(document).ready(function () {
            // jQuery DataTables initialization
            $('#myTable').DataTable({
                "language": {
                    "processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"
                },
                "processing": true,
                "serverSide": true,
                "orderMulti": false,
                "dom": '<"top"i>rt<"bottom"lp><"clear">',
                "ajax": {
                    "url": "/home/FilterData",
                    "type": "POST",
                    "datatype": "json"
                },
                "columns": [
                    { "data": "FirstName", "name": "FirstName", "autoWidth": true },
                    { "data": "LastName", "name": "LastName", "autoWidth": true },
                    { "data": "Age", "name": "Age", "autoWidth": true },
                    { "data": "Address", "name": "Address", "autoWidth": true },
                    { "data": "City", "name": "City", "autoWidth": true },
                    { "data": "State", "name": "State", "autoWidth": true }
                ]
            });
            
            // DataTables filtering on button click
            var oTable = $('#myTable').DataTable();
            $('#btnFilter').click(function () {
                oTable.columns(4).search($('#txtCity').val().trim());
                oTable.columns(5).search($('#ddState').val().trim());
                oTable.draw();
            });
        });
    </script>
}

Here, I have added a textbox for City and a dropdown for State to filter the records. I used some static records in the dropdown for basic understanding and in my next session, I will fill the dropdown from the database using Entity Framework.

<div style="background-color:#f5f5f5; padding:20px;">
    <h2 style="color: blue;">Filter Records</h2>
    <table>
        <tbody>
            <tr>
                <td style="color: blue;">City</td>
                <td><input type="text" class="form-control" id="txtCity" /></td>
                <td style="color: blue;">State</td>
                <td>
                    <select id="ddState" class="btn btn-primary dropdown-toggle" type="button" data-toggle="dropdown">
                        <option value="">All States</option>
                        <option value="Karnataka">Karnataka</option>
                        <option value="Andhra Pradesh">Andhra Pradesh</option>
                        <option value="Georgia">Georgia</option>
                        <option value="Uttar Pradesh">Uttar Pradesh</option>
                        <option value="Odisha">Odisha</option>
                    </select>
                </td>
                <td>
                    <input type="button" class="btn btn-success btn-md" value="Filter" id="btnFilter" />
                </td>
            </tr>
        </tbody>
    </table>
</div>

I have updated the code to implement custom multicolumn server-side filtering in jQuery DataTables by removing the default global search box. This initialization variable allows you to specify where in the DOM you want DataTables to introduce the various controls it composes to the page.

• <"top"i> means it is showing the info of entries.
• rt means it is showing the progress bar with loading records in DataTable.
• <"bottom"lp> means it is showing the length of records and also, the paging in the page.
• <"clear"> means it clears the controls or any data inside div element.

  "dom": '<"top"i>rt<"bottom"lp><"clear">'
  

The following piece of code will enable the data loading from server-side. The path "/home/FilterData" is the function that will be returning data from server side. The columns here are the exact names of the properties that we have created in the table and uploaded using the Entity Data Model file. Here, we can get the index number of the table's columns.

$('#myTable').DataTable({
    "language": {
        "processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"
    },
    "processing": true,
    "serverSide": true,
    "orderMulti": false,
    "dom": '<"top"i>rt<"bottom"lp><"clear">',
    "ajax": {
        "url": "/home/FilterData",
        "type": "POST",
        "datatype": "json"
    },
    "columns": [
        { "data": "FirstName", "name": "FirstName", "autoWidth": true }, //index 0
        { "data": "LastName", "name": "LastName", "autoWidth": true }, //index 1
        { "data": "Age", "name": "Age", "autoWidth": true }, //index 2
        { "data": "Address", "name": "Address", "autoWidth": true }, //index 3
        { "data": "City", "name": "City", "autoWidth": true }, //index 4
        { "data": "State", "name": "State", "autoWidth": true } //index 5
    ]
});

The following piece of code is used to apply custom search on jQuery DataTables. I have applied search for the city name using DataTable column index 4 and search for state name using DataTable column index 5.

oTable = $('#myTable').DataTable();
$('#btnFilter').click(function () {
    oTable.columns(4).search($('#txtCity').val().trim());
    oTable.columns(5).search($('#ddState').val().trim());
    oTable.draw();
});
});

Step 3. Add another action (here "FilterData") for fetching the data from the database and implementing the logic for server-side paging and sorting.

Code

[HttpPost]
public ActionResult FilterData()
{
    // Initialization.
    JsonResult result = new JsonResult();
    try
    {
        var draw = Request.Form.GetValues("draw").FirstOrDefault();
        var start = Request.Form.GetValues("start").FirstOrDefault();
        var length = Request.Form.GetValues("length").FirstOrDefault();
        var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault()
                        + "][name]").FirstOrDefault();
        var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
        var city = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault();
        var state = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault();
        int pageSize = length != null ? Convert.ToInt32(length) : 0;
        int skip = start != null ? Convert.ToInt16(start) : 0;
        int recordsTotal = 0;
        using (SatyaDBEntities dc = new SatyaDBEntities())
        {
            var v = (from a in dc.employees select a);

            if (!string.IsNullOrEmpty(city))
            {
                v = v.Where(a => a.City.Contains(city));
            }
            if (!string.IsNullOrEmpty(state))
            {
                v = v.Where(a => a.State == state);
            }
            if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
            {
                v = v.OrderBy(sortColumn + " " + sortColumnDir);
            }
            recordsTotal = v.Count();
            var data = v.Skip(skip).Take(pageSize).ToList();

            return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data },
                        JsonRequestBehavior.AllowGet);
        }
    }
    catch (Exception ex)
    {
        // Handle exception (log or display error)
        Console.WriteLine(ex); // Log exception details to console
    }
    // Return empty or default result if exception occurs
    return result;
}

Code description

I have described the code using the comment line in every line of code. It will be easy for a quick understanding of the code flow. In this piece of code, which is based on searching, sorting, and pagination information sent from the DataTable plugin, the following has been done: The data is being loaded first. It is being churned out based on the search criteria. Data is then sorted by a provided column in a provided order. Lastly, it is paginated and returned.

I have declared two variables which contain the informaion of two columns for filtering records with their index values as I have described in the View section.

var city = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault();
var state = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault();

The following is the piece of code for filtering records using city and state columns.

if (!string.IsNullOrEmpty(city))
{
    v = v.Where(a => a.City.Contains(city));
}
if (!string.IsNullOrEmpty(state))
{
    v = v.Where(a => a.State == state);
}

For sorting, we need to add a reference of System.Linq.Dynamic.

if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
    v = v.OrderBy(sortColumn + " " + sortColumnDir);
}

Output

During the initial load, the processing loader will look like below.

Filter records using state dropdown, as shown below.

Filter records using city textbox, as shown below.

Filter records using both, City and State, as shown below.

Summary

In this write-up, we have learned how to

• Filter records using custom multicolumn server-side features.
• Remove default global search box of jQuery DataTable by using initialisation variable in DOM.
• Get record's length, sorting and pagination information with the DataTable plugin.
• Get Server-side integration of DataTable plugin with ASP.NET MVC 5.