MVC4 and JQuery DataTables

The preamble…

So I’m messing with MVC4 lately and I wanted to use the jQuery DataTables plugin (http://www.datatables.net/).

First I had this working by loading rows using the HTML DOM. Great, it just loaded everything up and I could page/sort etc. Whilst the speed was impressive for the DOM manipulation that was happening behind the scenes, it started to feel a little sluggish over 5000 rows (and this is running on a new Core i7 decent piece of kit so…)

I looked at the documentation and I wanted to use an Ajax request to retrieve the data as JSON from a controller in my application. Eventually I came up with a ViewModel implementation which communicated nicely with the DataTables implementation and made it abstract/generic so that it would work with any data type. I’m still looking at using expression trees to discover properties on the data model, as at the moment I’ve had to resort to using the System.Linq.Dynamic add-on that I found on MSDN (it adds some Linq style extension methods that let you query using strings instead of expression trees)

Using the implementation

With the right implementation and an extension method or two the usage is very straightforward and intuitive

Example View

@using JMC.MVC.DataTables 
@model JMC.Dynamics.GP.Expenses.Models.DebtorListViewModel 
@{     
    Layout = null; 
} 
@Html.Raw(Model.ToJson(x => new { x.CustomerNo, x.Contact, x.CustomerName }))
// Extension method here just loops through the rows and runs a callback for each one so you can select the properties. Best to use this format as it's what DataTables expects

And the ViewModel

    public class DebtorListViewModel : DataTablesRequestViewModel
    {
        protected override IEnumerable<Debtor> GetDataInternal()
        {
            // We want to use the data sent by the datatables jQuery plugin to decide how to show the data
            // The request ViewModel will automatically parse the request and fill in the values that the datatables jquery plugin has created
            using (var ctx = new DynamicsContext())
            {
                // Create the query
                IQueryable qry = ctx.Debtors;

                // Add any sorts in the request - use Dynamic Linq library here... either that or figure out how to do this with expression trees!
                foreach (var sort in Sorts)
                    qry = qry.OrderBy(sort.ToString());

                // If a search string is included, add the where clause
                if (!string.IsNullOrWhiteSpace(sSearch))
                    qry = qry.Where(x => x.CustomerName.Contains(sSearch)
                                    || x.CustomerNo.Contains(sSearch));

                // Count the total rows in the data set 
                TotalRowCount = ctx.Debtors.Count();

                // Count the filtered rows our query returns
                FilteredRowCount = qry.Count();

                // Send the request viewmodel with the data to the view 
                // Get the data, skipping the offset requested and returning the number of items requested
                if (Sorts.Any())
                    return qry.Skip(iDisplayStart)
                        .Take(iDisplayLength)
                        .ToList();
                else
                    // Should never really get here, but when testing you can remove the AjaxCall attrib from the controller and run the action to test the JSON and 
                    // it's likely you'll hit this because no sort info will be passed across in the querystring
                    return qry.ToList();
            }
        }
    }

I’ve left my original comments in there but basically, you inherit from the generic DataTablesRequestViewModel<T> and then override the GetDataInternal() method. In this method you query your data (I’ve used Linq to Entities here) and return what DataTables asked for. You can inspect the properties on the ViewModel which will tell you how many rows to offset by and how many to return, and also the search term passed in by the user amongst other things (the implementation could probably use a few more properties from DataTables as it does send a lot of information about what the user requested!)

A few (small) requirements and notes:

• You need to provide column names when building the DataTable using the DataTables API in order for the data to map correctly to the columns.
• You also need to use a custom ModelBinder class in order to map the querystring values that DataTables passes into a meaningful collection of column names for sorting (DataTables passes the column indexes and the sort orders as sSort_0 to sSort_10 etc so you need to parse this)
• The data fetch implementation currently relies on System.Linq.Dynamic – obviously this isn’t a problem in the DataTables view model, it just provides a list of DataTableSort objects which contain a PropertyName and a SortDirection, but I’d like to try and get this working with expression trees so that I can parse the column name from the values DataTables provides.

Also: there isn’t much error handling in the DataTables implementation so it needs testing for potential breaks when strange values are passed

An example jQuery DataTable:

$(document).ready(function () {

        // Create a data table and enable jQuery UI themeing
        $("#dataTable").dataTable({
            "bJQueryUI": true,

            // The Ajax URL to call
            "sAjaxSource": '@Url.Action("List")',
            // Set some grid options, make the processing box appear when the page loads and enable server side processing (required for the ajax requerying)
            "bProcessing": true,
            "bServerSide": true,
            // You need to provide column names for the data to map
            "aoColumns": [
                            { "mData": "CustomerNo", "sName": "CustomerNo", "sTitle": "Account Code" },
                            { "mData": "CustomerName", "sName": "CustomerName", "sTitle": "Client Name" },
                            { "mData": "Contact", "sName": "Contact", "sTitle": "Contact Name" }
            ]
        });

    });

And the result is a fully searchable, sortable grid which is lightning fast, easy to implement and supports any data scenario.

Now if I can just work out how to add attachments to this post…

Aha: https://www.dropbox.com/s/f7xtopj6hbbrog3/JMC.MVC.DataTables.zip