jQuery DataTables Plugin Meets C#

Over the weekend, I was doing some work on the internal CMS we use over at eagleenvision.net and I wanted to scrap my custom table implementation for a table system that would use JSON to return data rather than have the data be statically allocated on the page. Basically I wanted to have the ability to refresh, etc for editing purposes. My little table marker was too much code for something so simple.

I’m a HUGE fan of jQuery, especially of the recent changes in jQuery 1.4. We have already made a significant investment in jQuery code for our CMS and I didn’t want to simply add another library or framework into the mix… by that I mean I didn’t want to throw in the Ext framework, which specialized in UI controls rather than general purpose JavaScript.

I stumbled upon the jQuery DataTables plugin. It has a lot of great features… one of which is the ability to have server-side processing of your data. The examples on the site are written with PHP, as are the downloadable demos. I don’t use PHP, I use ASP.NET :). So I had to write my own library to process the incoming request. DataTables has a set of pre-defined request variables that are passed to the server for processing. A successful implementation will take all these variables into account and send the correct data back per these variables. (To see a complete list, check out the server-side usage page).

 

Our friend, IQueriable

If you’re a C# developer, there’s no way you don’t already know about LINQ… (double negatives… oops…. every C# knows about LINQ, or they’re not really current with technology… that’s better :) ). This includes VB people as well. IQueriable is the fundamental type of LINQ that drives the functionality of the various sequence operators. In my implementation of DataTables processing, I wanted to leverage LINQ so that you could throw this thing an IQueriable from Linq2Sql, Linq to NHibernate, Entity Framework, LightSpeed, in-memory list, or ANYTHING that has IQueriable functionality and it would just work.

 

How to output

The DataTables plugin accepts either JSON or XML… whichever jQuery will parse. My opinion is never use XML with JavaScript. It’s slower and there’s no point to using XML over JSON… especially in .NET where there are built-in JSON serializers. Having said that, you could certainly use XML… although I haven’t tested my code for this, I think (in theory) it will work the same. He’s the output type, which should be serialized into JSON or XML, which I will cover in a minute.

public class FormatedList
{
    public FormatedList()
    {
    }
    public int sEcho { get; set; }
    public int iTotalRecords { get; set; }
    public int iTotalDisplayRecords { get; set; }
    public List<List<string>> aaData { get; set; }
    public string sColumns { get; set; }
    public void Import(string[] properties)
    {
        sColumns = string.Empty;
        for (int i = 0; i < properties.Length; i++)
        {
            sColumns += properties[i];
            if (i < properties.Length - 1)
                sColumns += ",";
        }
    }
}

Basically the only interesting thing here is the output of the columns. I made a custom Import method that just takes a list of properties and forms the column string that DataTables will parse. Other than that the code here is just basic property holding.

 

ASP.NET MVC

Readers of my blog and twitter will know I am also a HUGE fan of ASP.NET MVC. I don’t think I’ll ever return to ASP.NET WebForms. But who knows. Anyways, here’s how you will output the thing in MVC

public ActionResult List()
{
    IQueriable<User> users = Session.Linq<User>();
    if (Request["sEcho"] != null)
    {
        var parser = new DataTableParser<User>(Request, users);
        return Json(parser.Parse());
    }
    return Json(users);
}

You’ll notice that I referenced DataTableParser, which I will get to in a minute. This takes an HttpRequestBase (or an HttpRequest) and an IQueriable of whatever type. It will output a new FormattedList in the parse method, which you will return via JSON (which is serialized in the Json method for MVC).

 

ASP.NET Webservices

While I don’t claim to be an expert at ASP.NET Webservice, this I can handle :). He’s how would would do the same thing in ASP.NET Webservices.

using System.Web.Script.Serialization;
using System.Linq;
...
public class MyWebservice : System.Web.Services.WebService
{
    public string MyMethod()
    {
        // change the following line per your data configuration
        IQueriable<User> users = Session.Linq<User>();
        
        response.ContentType = "application/json";
        
        if(Request["sEcho"] != null)
        {
            var parser = new DataTableParser<User>(Request, users);
            return new JavaScriptSerializer().Serialize(parser.Parse());
        }
        
        return new JavaScriptSerializer().Serialize(users); 
    }
}

 

This is the same code… it just uses webservices and the JavaScriptSerializer (which MVC uses under the covers) to serialize the FormatedList object.

 

It should be noted that you should ALWAYS check if the request is a DataTables request (which is what that sEcho business is all about).

 

The Parser

Now is the time to show you my parser. I have taken out my code comments to keep this short on my blog… but you can download my code from here and the comments should explain what is going on.

 

public class DataTableParser<T>
{
    private const string INDIVIDUAL_SEARCH_KEY_PREFIX = "sSearch_";
    private const string INDIVIDUAL_SORT_KEY_PREFIX = "iSortCol_";
    private const string INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX = "sSortDir_";
    private const string DISPLAY_START = "iDisplayStart";
    private const string DISPLAY_LENGTH = "iDisplayLength";
    private const string ECHO = "sEcho";
    private const string ASCENDING_SORT = "asc";
    private IQueryable<T> _queriable;
    private readonly HttpRequestBase _httpRequest;
    private readonly Type _type;
    private readonly PropertyInfo[] _properties;
    public DataTableParser(HttpRequestBase httpRequest, IQueryable<T> queriable)
    {
        _queriable = queriable;
        _httpRequest = httpRequest;
        _type = typeof(T);
        _properties = _type.GetProperties();
    }
    public DataTableParser(HttpRequest httpRequest, IQueryable<T> queriable)
        : this(new HttpRequestWrapper(httpRequest), queriable)
    { }
    
    public FormatedList Parse()
    {
        var list = new FormatedList();
        list.Import(_properties.Select(x => x.Name).ToArray());
        
        list.sEcho = int.Parse(_httpRequest[ECHO]);
        
        list.iTotalRecords = _queriable.Count();
        
        ApplySort();
        
        int skip = 0, take = 10;
        int.TryParse(_httpRequest[DISPLAY_START], out skip);
        int.TryParse(_httpRequest[DISPLAY_LENGTH], out take);
        
        list.aaData = _queriable.Where(ApplyGenericSearch)
                                .Where(IndividualPropertySearch)
                                .Skip(skip)
                                .Take(take)
                                .Select(SelectProperties)
                                .ToList();
                                
        list.iTotalDisplayRecords = list.aaData.Count;
        return list;
    }
    private void ApplySort()
    {
        foreach (string key in _httpRequest.Params.AllKeys.Where(x => x.StartsWith(INDIVIDUAL_SORT_KEY_PREFIX)))
        {
            int sortcolumn = int.Parse(_httpRequest[key]);
            if (sortcolumn < 0 || sortcolumn >= _properties.Length)
                break;
                
            string sortdir = _httpRequest[INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX + key.Replace(INDIVIDUAL_SORT_KEY_PREFIX, string.Empty)];
            
            var paramExpr = Expression.Parameter(typeof(T), "val");
            var propertyExpr = Expression.Lambda<Func<T, object>>(Expression.Property(paramExpr, _properties[sortcolumn]), paramExpr);
            
            if (string.IsNullOrEmpty(sortdir) || sortdir.Equals(ASCENDING_SORT, StringComparison.OrdinalIgnoreCase))
                _queriable = _queriable.OrderBy(propertyExpr);
            else
                _queriable = _queriable.OrderByDescending(propertyExpr);
        }
    }
    
    private Expression<Func<T, List<string>>> SelectProperties
    {
        get
        {
            // 
            return value => _properties.Select
                                        (
                                            prop => (prop.GetValue(value, new object[0]) ?? string.Empty).ToString()
                                        )
                                       .ToList();
        }
    }
    
    private Expression<Func<T, bool>> IndividualPropertySearch
    {
        get
        {
            var paramExpr = Expression.Parameter(typeof(T), "val");
            Expression whereExpr = Expression.Constant(true); // default is val => True
            foreach (string key in _httpRequest.Params.AllKeys.Where(x => x.StartsWith(INDIVIDUAL_SEARCH_KEY_PREFIX)))
            {
                int property = -1;
                if (!int.TryParse(_httpRequest[key].Replace(INDIVIDUAL_SEARCH_KEY_PREFIX, string.Empty), out property) 
                    || property >= _properties.Length || string.IsNullOrEmpty(_httpRequest[key]))
                    break; // ignore if the option is invalid
                string query = _httpRequest[key].ToLower();
                
                var toStringCall = Expression.Call(
                                    Expression.Call(
                                        Expression.Property(paramExpr, _properties[property]), "ToString", new Type[0]),
                                    typeof(string).GetMethod("ToLower", new Type[0]));
                
                whereExpr = Expression.And(whereExpr, 
                                           Expression.Call(toStringCall, 
                                                           typeof(string).GetMethod("Contains"), 
                                                           Expression.Constant(query)));
                
            }
            return Expression.Lambda<Func<T, bool>>(whereExpr, paramExpr);
        }
    }
    
    private Expression<Func<T, bool>> ApplyGenericSearch
    {
        get
        {
            string search = _httpRequest["sSearch"];
            
            if (string.IsNullOrEmpty(search) || _properties.Length == 0)
                return x => true;
                
            var searchExpression = Expression.Constant(search.ToLower());
            var paramExpression = Expression.Parameter(typeof(T), "val");
            
            var propertyQuery = (from property in _properties
                                let tostringcall = Expression.Call(
                                                    Expression.Call(
                                                        Expression.Property(paramExpression, property), "ToString", new Type[0]),
                                                        typeof(string).GetMethod("ToLower", new Type[0]))
                                select Expression.Call(tostringcall, typeof(string).GetMethod("Contains"), searchExpression)).ToArray();
                                
            Expression compoundExpression = propertyQuery[0];
            
            for (int i = 1; i < propertyQuery.Length; i++)
                compoundExpression = Expression.Or(compoundExpression, propertyQuery[i]);
                
            return Expression.Lambda<Func<T, bool>>(compoundExpression, paramExpression);
        }
    }
}

 

Caveat

Currently there’s a bug here, that I need to research :). If you have a boolean property and apply a sort on it, you’ll get an exception because I am trying to cast it as an object with the Expression.Lambda<Func<T, object>> call. I’ll look into this and update this blog post accordingly. If you can provide any help, that would be great :)

 

Conclusion

This is just a simple example of parsing a request and mutating IQueriable acordingly. I hope this helps someone out there who would like to use C# with the DataTables plugin. Again, you can download my code from here with full comments.

Published Tuesday, January 19, 2010 3:32 PM by zowens

Comments

# re: jQuery DataTables Plugin Meets C#

Thank for the great post!

I download the code only see the server side file, can you please also post a client side code?

Again great job and thank very much!

Thursday, January 21, 2010 5:53 PM by danny

# re: jQuery DataTables Plugin Meets C#

There really isn't any... you can just copy it from here.

Thursday, January 21, 2010 6:11 PM by zowens

# re: jQuery DataTables Plugin Meets C#

Can you kindly post your .aspx page which consumes the above web service or mvc. and co ordinating with data tables.. it would be more useful..

Regarding that bool is there any way to set as a read only property???

Wednesday, January 27, 2010 9:05 PM by Abdul Kader Jeelani

# re: jQuery DataTables Plugin Meets C#

In order to fix the bug you mentioned, just call Expression.Convert in ApplySort like this:

...

var paramExpr = Expression.Parameter(typeof(T), "val");

var expression = Expression.Convert(Expression.Property(paramExpr, propertyName), typeof(Object));

var propertyExpr = Expression.Lambda<Func<T, object>>(expression, paramExpr);

...

Everything works like a charm then.

Thursday, February 18, 2010 2:09 PM by Miguel De Lathouwer

# re: jQuery DataTables Plugin Meets C#

Never could make it work, keep getting "can't sort a System.Object type" if I comment out sorting then I get an even worst error message.

By default it's trying to sort using the table main key (Int32)

Wednesday, March 24, 2010 12:37 PM by Lewis

# re: jQuery DataTables Plugin Meets C#

Only problem with this is when you have to use stored procedures as they do not return an IQueriable type.

Wednesday, March 24, 2010 2:06 PM by Mike Lujan

# re: jQuery DataTables Plugin Meets C#

OK, so I'm having a couple of issues.

I'm using vanilla LINQ to SQL and I'm getting a:

Cannot Order By "Object

If I comment out the ApplySort section, I'm still having issues:

Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

This problem I solved by separating out the last query. Doing a:

.ToList().AsQueryable().Select(SelectProperties).ToList();

To force the LINQ 2 SQL to execute first. However, I still can't get around the Sorting problem.

Any ideas?

Wednesday, April 21, 2010 6:01 PM by Kevin Lloyd

# re: jQuery DataTables Plugin Meets C#

I get a "Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator." exception on the select query of the of the IQueryable in Parse.

Thursday, May 13, 2010 10:04 PM by Luke Lowrey

# re: jQuery DataTables Plugin Meets C#

When paging the iTotalDisplayRecords is returning the wrong number this can be corrected like this:

...

// setup the data with individual property search, all fields search,

// paging, and property list selection

queriable = queriable.Where(ApplyGenericSearch)

           .Where(IndividualPropertySearch);

// total records that can displayed

list.iTotalDisplayRecords = queriable.Count();

list.aaData = queriable.Skip(skip)

           .Take(take)

           .Select(SelectProperties)

           .ToList();

...

Friday, May 14, 2010 6:18 AM by Adrian Cox

# re: jQuery DataTables Plugin Meets C#

Thanks for this great post!

Unfortunately, I can't get it to work..

I'm using ASP.NET MVC + Entity Framework and calling the parser from my controller like:

<code>

var db = new myEntities();

IQueryable<Roles> data = db.Roles;

if( Request["sEcho"] != null ) {

 var parser = new DataTableParser<Roles> ( Request, data );

 return Json( parser.Parse() );

}

return Json( data );

</code>

At query execution, I get the following System.NotSupportedException:

"Unable to cast the type 'System.String' to type 'System.Object'. LINQ to Entities only supports casting Entity Data Model primitive types."

Where the 'System.String' refers to my entity's first property. Any idea of what's going on here?

I am new to .NET and LINQ so any help would be much appreciated!

Thursday, June 10, 2010 2:15 PM by Olivier

# re: jQuery DataTables Plugin Meets C#

I finally got it to work :)

The previous exception I experienced had nothing to do with the parser but issues on my side.

Using the column filtering, I have applied two fixes in the "IndividualPropertySearch" property getter in the following code section :

...

if( !int.TryParse( key.Replace( INDIVIDUAL_SEARCH_KEY_PREFIX, string.Empty ), out property )

             || property >= _properties.Length || string.IsNullOrEmpty( _httpRequest[key] ) )

           continue; // ignore if the option is invalid

...

Note that "_httpRequest[key].Replace..." is now "key.Replace..." in order to extract the proper index.

Also, I put a "continue" statement instead of "break" to allow filtering on any arbitrary column.

Cheers

Friday, June 25, 2010 4:17 AM by Olivier

# re: jQuery DataTables Plugin Meets C#

Hi,

Thank you for sharing this piece of awesomeness!

How do you handle extra columns e.g. for edit/delete etc?

Regards,

Paul.

Tuesday, September 28, 2010 4:29 AM by Paul Mungai

# re: jQuery DataTables Plugin Meets C#

thanks a lot for this piece of code!!! theres a library on codeplex for datatables but your code is much more simpler and easier to adapt so i will continue with your code as a base.. also works perfectly with Subsonic.. just in case someone wants to try this combination :)

Monday, November 1, 2010 8:48 PM by Bastian

# re: jQuery DataTables Plugin Meets C#

and to fix multi-sort problems:

if (firstSort)

               {

                   if (string.IsNullOrEmpty(sortdir) || sortdir.Equals(ASCENDING_SORT, StringComparison.OrdinalIgnoreCase))

                       _queriable = _queriable.OrderBy(propertyExpr);

                   else

                       _queriable = _queriable.OrderByDescending(propertyExpr);

                   firstSort = false;

               }

               else

               {

                   if (string.IsNullOrEmpty(sortdir) || sortdir.Equals(ASCENDING_SORT, StringComparison.OrdinalIgnoreCase))

                       _queriable = ((IOrderedQueryable<T>)_queriable).ThenBy(propertyExpr);

                   else

                       _queriable = ((IOrderedQueryable<T>)_queriable).ThenByDescending(propertyExpr);

               }

Monday, November 1, 2010 8:54 PM by Bastian

# re: jQuery DataTables Plugin Meets C#

Thanks for a amazing post!!!

The only thing I´m having troubble sorting out is to get the generic search to work with null values (at least I´m guessing this is my problem).

When calling:

Expression.Property(paramExpression, property), "ToString"

in ApplyGenericSearch the code breaks and i have not been able to figure out a way to solve this...

My idea is to check for null and then return an empty string if so but i cant figure out how to do that with expression trees.

Regards,

Tobias

Friday, November 26, 2010 3:12 PM by Tobias Lundin

# re: jQuery DataTables Plugin Meets C#

Is it supposed to work with DataTables 1.7.5 and/or EF4 ? Also, I get the following error when called, whatever which table I query or filter or sort or anything : (Most of my Primary keys are GUID, btw)

Unable to cast the type 'System.Guid' to type 'System.Object'. LINQ to Entities only supports casting Entity Data Model primitive types.

Have this happened to anyone here ?

Thursday, February 3, 2011 3:57 PM by LoganWolfer

# re: jQuery DataTables Plugin Meets C#

Hi

How do i use this code in my website?

Copy the code and then do what?

Monday, April 30, 2012 11:30 AM by tan4prog