Advertisements
jump to navigation

DataTablePager Now Has Multi-Column Sort Capability For DataTables.Net February 9, 2011

Posted by ActiveEngine Sensei in .Net, ActiveEngine, Ajax, ASP.Net, C#, DataTables.Net, jQuery.
Tags: , , , , , , , , ,
trackback

Some gifts just keep on giving, and many times things can just take on a momentum that grow beyond your expectation.  Bob Sherwood wrote to Sensei and pointed out that DataTables.net supports multiple column sorting.  All you do is hold down the shift key and click on any second or third column and DataTables will add that column to sort criteria.  “Well, how come it doesn’t work with the server side solution?”  Talk about the sound of one hand clapping.  How about that for a flub!  Sensei didn’t think of that!  Then panic set in – would this introduce new complexity to the DataTablePager solution, making it too difficult to maintain a clean implementation?  After some long thought it seemed that a solution could be neatly added.  Before reading, you should download the latest code to follow along.

How DataTables.Net Communicates Which Columns Are Involved in a Sort

If you recall, DataTables.Net uses a structure called aoData to communicate to the server what columns are needed, the page size, and whether a column is a data element or a client side custom column.  We covered that in the last DataTablePager post.  aoData also has a convention for sorting:

bSortColumn_X=ColumnPosition

In our example we are working with the following columns:

,Name,Agent,Center,,CenterId,DealAmount

where column 0 is a custom client side column, column 1 is Name (a mere data column), column 2 is Center (another data column), column 3 is a custom client side column, and the remaining columns are just data columns.

If we are sorting just by Name, then aoData will contain the following:

bSortColumn_0=1

When we wish to sort by Center, then by Name we get the following in aoData”

bSortColumn_0=2

bSortColumn_1=1

In other words, the first column we want to sort by is in position 2 (Center) and the second column(Name) is in position 1.  We’ll want to record this some where so that we can pass this to our order routine.  aoData passes all column information to us on the server, but we’ll have to parse through the columns and check to see if one or many of the columns is actually involved in a sort request and as we do we’ll need to preserve the order of that column of data in the sort.

SearchAndSortable Class to the Rescue

You’ll recall that we have a class called SearchAndSortable that defines how the column is used by the client.  Since we iterate over all the columns in aoData it makes sense that we should take this opportunity to see if any column is involved in a sort and store that information in SearchAndSortable as well.  The new code for the class looks like this:

public class SearchAndSortable
    {
        public string Name { get; set; }
        public int ColumnIndex { get; set; }
        public bool IsSearchable { get; set; }
        public bool IsSortable { get; set; }
        public PropertyInfo Property{ get; set; }
        public int SortOrder { get; set; }
        public bool IsCurrentlySorted { get; set; }
        public string SortDirection { get; set; }

        public SearchAndSortable(string name, int columnIndex, bool isSearchable,
                                bool isSortable)
        {
            this.Name = name;
            this.ColumnIndex = columnIndex;
            this.IsSearchable = isSearchable;
            this.IsSortable = IsSortable;
        }

        public SearchAndSortable() : this(string.Empty, 0, true, true) { }
    }

There are 3 new additions:

IsCurrentlySorted – is this column included in the sort request.

SortDirection – “asc” or “desc” for ascending and descending.

SortOrder – the order of the column in the sort request.  Is it the first or second column in a multicolumn sort.

As we walk through the column definitions, we’ll look to see if each column is involved in a sort and record what direction – ascending or descending – is required. From our previous post you’ll remember that the method PrepAOData is where we parse our column definitions. Here is the new code:

//  Sort columns
this.sortKeyPrefix = aoDataList.Where(x => x.Name.StartsWith(INDIVIDUAL_SORT_KEY_PREFIX))
                                            .Select(x => x.Value)
                                            .ToList();

//  Column list
var cols = aoDataList.Where(x => x.Name == "sColumns"
                                            & string.IsNullOrEmpty(x.Value) == false)
                                     .SingleOrDefault();

if(cols == null)
{
  this.columns = new List();
}
else
{
  this.columns = cols.Value
                       .Split(',')
                       .ToList();
}

//  What column is searchable and / or sortable
//  What properties from T is identified by the columns
var properties = typeof(T).GetProperties();
int i = 0;

//  Search and store all properties from T
this.columns.ForEach(col =>
{
  if (string.IsNullOrEmpty(col) == false)
  {
    var searchable = new SearchAndSortable(col, i, false, false);
    var searchItem = aoDataList.Where(x => x.Name == BSEARCHABLE + i.ToString())
                                     .ToList();
    searchable.IsSearchable = (searchItem[0].Value == "False") ? false : true;
    searchable.Property = properties.Where(x => x.Name == col)
                                                    .SingleOrDefault();

    searchAndSortables.Add(searchable);
  }

  i++;
});

//  Sort
searchAndSortables.ForEach(sortable => {
  var sort = aoDataList.Where(x => x.Name == BSORTABLE + sortable.ColumnIndex.ToString())
                                            .ToList();
  sortable.IsSortable = (sort[0].Value == "False") ? false : true;
                sortable.SortOrder = -1;

  //  Is this item amongst currently sorted columns?
  int order = 0;
  this.sortKeyPrefix.ForEach(keyPrefix => {
    if (sortable.ColumnIndex == Convert.ToInt32(keyPrefix))
    {
      sortable.IsCurrentlySorted = true;

      //  Is this the primary sort column or secondary?
      sortable.SortOrder = order;

     //  Ascending or Descending?
     var ascDesc = aoDataList.Where(x => x.Name == "sSortDir_" + order)
                                                    .SingleOrDefault();
     if(ascDesc != null)
     {
       sortable.SortDirection = ascDesc.Value;
     }
   }

   order++;
 });
});

To sum up, we’ll traverse all of the columns listed in sColumns. For each column we’ll grab the PorpertyInfo from our underlying object of type T. This gives only those properties that will be displayed in the grid on the client. If the column is marked as searchable, we indicate that by setting the IsSearchable property on the SearchAndSortable class.  This happens starting at line 28 through 43.

Next we need to determine what we can sort, and will traverse the new list of SearchAndSortables we created. DataTables will tell us what if the column can be sorted by with following convention:

bSortable_ColNumber = True

So if the column Center were to be “sortable” aoData would contain:

bSortable_1 = True

We record the sortable state as shown on line 49 in the code listing.

Now that we know whether we can sort on this column, we have to look through the sort request and see if the column is actually involved in a sort.  We do that by looking at what DataTables.Net sent to us from the client.  Again the convention is to send bSortColumn_0=1 to indicate that the first column for the sort in the second item listed in sColumns property.  aoData will contain many bSortColum’s so we’ll walk through each one and record the order that column should take in the sort.  That occurs at line 55 where we match the column index with the bSortColumn_x value.

We’ll also determine what the sort direction – ascending or descending – should be.  At line 63 we get the direction of the sort and record this value in the SearchAndSortable.

When the method PrepAOData is completed, we have a complete map of all columns and what columns are being sorted, as well as their respective sort direction.  All of this was sent to us from the client and we are storing this configuration for later use.

Performing the Sort

(Home stretch so play the song!!)

If you can picture what we have so far we just basically created a collection of column names, their respective PropertyInfo’s and have recorded which of these properties are involved in a sort.  At this stage we should be able to query this collection and get back those properties and the order that the sort applies.

You may already be aware that you can have a compound sort statement in LINQ with the following statement:

var sortedCustomers = customer.OrderBy(x => x.LastName)
                                           .ThenBy(x => x.FirstName);

The trick is to run through all the properties and create that compound statement. Remember when we recorded the position of the sort as an integer? This makes it easy for us to sort out the messy scenarios where the second column is the first column of a sort. SearchAndSortable.SortOrder takes care of this for us. Just get the data order by SortOrder in descending order and you’re good to go. So that code would look like the following:

var sorted = this.searchAndSortables.Where(x => x.IsCurrentlySorted == true)
                                     .OrderBy(x => x.SortOrder)
                                     .ToList();

sorted.ForEach(sort => {
             records = records.OrderBy(sort.Name, sort.SortDirection,
             (sort.SortOrder == 0) ? true : false);
});

On line 6 in the code above we are calling our extension method OrderBy in Extensions.cs. We pass the property name, the sort direction, and whether this is the first column of the sort. This last piece is important as it will create either “OrderBy” or the “ThenBy” for us. When it’s the first column, you guessed it we get “OrderBy”. Sensei found this magic on a StackOverflow post by Marc Gravell and others.

Here is the entire method ApplySort from DataTablePager.cs, and note how we still check for the initial display of the data grid and default to the first column that is sortable.

private IQueryable ApplySort(IQueryable records)
{
  var sorted = this.searchAndSortables.Where(x => x.IsCurrentlySorted == true)
                                                .OrderBy(x => x.SortOrder)
                                                .ToList();

  //  Are we at initialization of grid with no column selected?
  if (sorted.Count == 0)
  {
    string firstSortColumn = this.sortKeyPrefix.First();
    int firstColumn = int.Parse(firstSortColumn);

    string sortDirection = "asc";
    sortDirection = this.aoDataList.Where(x => x.Name == INDIVIDUAL_SORT_DIRECTION_KEY_PREFIX +                                                                    "0")
                                                    .Single()
                                                    .Value
                                                    .ToLower();

    if (string.IsNullOrEmpty(sortDirection))
    {
      sortDirection = "asc";
    }

    //  Initial display will set order to first column - column 0
    //  When column 0 is not sortable, find first column that is
    var sortable = this.searchAndSortables.Where(x => x.ColumnIndex == firstColumn)
                                                        .SingleOrDefault();
    if (sortable == null)
    {
      sortable = this.searchAndSortables.First(x => x.IsSortable);
    }

    return records.OrderBy(sortable.Name, sortDirection, true);
  }
  else
  {
      //  Traverse all columns selected for sort
      sorted.ForEach(sort => {
                             records = records.OrderBy(sort.Name, sort.SortDirection,
                            (sort.SortOrder == 0) ? true : false);
      });

    return records;
  }
}

It’s All in the Setup

Test it out. Hold down the shift key and select a second column and WHAMO – multiple column sorts! Hold down the shift key and click the same column twice and KAH-BLAMO multiple column sort with descending order on the second column!!!

The really cool thing is that our process on the server is being directed by DataTables.net on the client.  And even awseomer is that you have zero configuration on the server.  Most awesome-est is that this will work with all of your domain objects, because we have used generics we can apply this to any class in our domain.  So what are you doing to do with all that time you just got back?

Advertisements

Comments»

1. jesper - May 2, 2011

Great stuff, do you have the dll you are using? I cannot compile it because you are using a namespace i dont have or something in that matter

2. jesper - May 2, 2011

never mind figured it, great stuff

3. Amrit Pal Singh - September 15, 2011

I have been struggling to implement datatables with asp.net web forms. finally i found your post the solution worked .

But i still have performance issue to fetch only 80 records it taked a lot of time. and when you sort it again take a lot of time.

I switched serverside loading using datatables due to performance reason only. But it has become worst. Earlier it consume time once at the time of loading after that search/ sorting work very smoothly.

now it takes a lot time for each operation.

The dataset provded with sample took no time at all.I have found when i create my IQueryable object from database there it take major time.

Please suggest something to improve performance.

ActiveEngine Sensei - September 15, 2011

First of all Amrit, thanks for reading my blog. Hopefully I can be of help to you.

Let’s start with the database. How many records are you pulling back and converting to IQueryable? Do you need to go to the database each time. or can you cache the recordset as IQueryable and perform a query against the cached record set? My design is essentially going against a repository of some type. However, in production I have used this technique against a 6000 record database and the performance has been acceptable. For anything larger my approach is probably not optimal.

If you can’t cache the results and have a large collection of objects we may have to take a different approach. At the DataTables site there is a solution for building a pass through SQL statement such as:

strRequeteA += “SELECT * FROM (”
strRequeteA += “SELECT TOP ”
strRequeteA += Request(“iDisplayLength”).ToString
strRequeteA += ” * FROM (”
strRequeteB_1 = “SELECT ”
strRequeteB_2 = “TOP ” + (Convert.ToInt32(Request(“iDisplayStart”)) + Convert.ToInt32(Request(“iDisplayLength”))).ToString
strRequeteB_3 += ” id, engine, browser, platform, version, grade ”
strRequeteB_3 += “FROM ajax ”

Here is the link. You’ll want to read through that document to get the idea. But before you give up on the IQueryable approach I’d do some bench marking against your database. If you have to use a grid against 100,000 records, then it’s best to use the SQL technique and let SQL do what it does best. If you’re at 10,000 records you should examine your retrieval speed from the database and find your bottleneck.

Another tactic that you may take is to use the pipeline feature in DataTables.Net. The documentation is here. This allows you to fetch 5 pages of data, but will only display the first page. When you perform paging and other operations, the cached pages are used first, then the request is sent to the server. So in your case, if fetching 400 records takes as long as fetching 80, fetch 400, display the first 80 and you are making less trips to the database.

I hope this helps. I’d focus on the database first and if you do indeed have a large record set to work with, try the SQL statement approach. Let me know how it turns. I have been wondering if I needed to write a SQL Statement generator for the DataTablesPager solution. Any feedback could help towards that goal. Good luck.

4. Amritpal Singh (@amritpalg) - September 17, 2011

Hi and thanks
I forget to mention in earlier comment.
I have figured out performance issue.
It is decent now. but when i try use search it gives null parameter name error.
and i tried use fnRender function to render a hyperlink to delete record but it does not show row id in fnRender.

ActiveEngine Sensei - September 17, 2011

Great news that you found your performance issue. As I said, there is a limit to my solution. Hopefully you haven’t reached that upper limit yet with your system.

As to your error with searching I have found that the expression tree will error when you have a null value in the record. I haven’t had the time to work as conversion of null to string.empty in the lambda expression, but as a work around I replace the null in the record set with string.empty before I apply the pager solution. It drove me crazy for a while trying to figure it out. To ensure that this is indeed the case I wold eliminate columns from your grid and see if it renders.

With respect to fnRender I’ll have to ponder a bit. Can you see the column with the appropriate row id if you do NOT use fnRender? That could be a place to start.

On a different note, I noticed that you replied at 3 am Eastern Standard Time. I hope you are in a different time – I’d hate to think that you are crazy like I am and work all sorts of hours!

Matt - April 13, 2012

In the GenericSearchFilter() expression change the LINQ in the for loop to this:

var propertyQuery = (from property in filterProperties
let toStringMethod = Expression.Call(
Expression.Call(Expression.Coalesce(Expression.Property(paramExpression, property), Expression.Constant(new object())), convertToString, null),
typeof(string).GetMethod(“ToLower”, new Type[0]))
select Expression.Call(toStringMethod, typeof(string).GetMethod(“Contains”), searchExpression)).ToArray();

The coalesce will take a null value and put in an empty object.

ActiveEngine Sensei - April 14, 2012

Awesome – thanks. I had struggled over this and set the concern aside in hopes that someone would point out a solution :).

5. Glenn - June 6, 2012

This is some awesome work. Thank you very much for sharing it!

ActiveEngine Sensei - June 8, 2012

Thanks for reading. I’m glad you can use this.

6. Glenn - June 6, 2012

Ok, a bit of a heads up to (hopefully) save others some time… If you get the following exception:
LINQ to Entities does not recognize the method ‘System.String ToString()’ method, and this method cannot be translated into a store expression

First, look at altering the GenericSearchFilter() method as Matt says above to allow handling null columns. (Note that this did not work on the default sample project guys, due to non-nullable fields in the Tenant entity)

Second, The issue is due to the fact that Linq to Entities does not support as many conversion of functions to server hosted sql. ToString (on anything) is one of them. One way to get around this is to convert from Linq to Entities to Linq to Objects. This is easily done by doing something like this:
IQueryable tenants = myLinqToEntitiesIQueryableVar.ToList().AsQueryable();

Now, while this worked for me, be aware that by doing this, you are effectively pulling all of the rows of data from the database and then forcing Linq to filter them in C# rather than by generating SQL WHERE clauses and letting the database do the work. In my case, I had pre-pended a filter to the original IQueryable before handing it over to the UI, so my max list was only about 300-400 objects making this tolerable.

If anyone finds a better way, please let us all know!

Happy Coding!

ActiveEngine Sensei - June 8, 2012

Good points. I think I may have stated that the inherent weakness to my solution is that you are forced to pull rows from the database first. The upper limit that I used was c. 15000 records with pretty good results. My dream is to be able to produce a where clause the can be used with SQL. Rob Connerys massive does this now.

Are you saying that a list of 300 objects was the upper limit of acceptable performance with Matt’s method for handling null? I’d do some tests but I’m on vacation and don’t have my laptop.

Glenn - June 8, 2012

No, I am not saying that 300 was a limit… just what I am working with.
In our case 300 records server-side is no problem, but when we initially passed all 300 to the client we did see performance issues (FWIW they were caused by us using several jQuery UI manipulated controls within the grid, not the performance of DataTable itself).

7. Glenn - June 7, 2012

Sensei,

I see in the previous blog post that you had not yet gotten to implementing individual column filtering. Farshid had mentioned that he was going to attempt it and post his code, but I have not seen it. Did you or anyone else ever get anywhere with implementing individual column filtering?

Thanks again for this great contribution!

ActiveEngine Sensei - June 8, 2012

With respect to single column query I haven’t had the chance to focus on this. Most of my users / customers like the fact that DataTables.Net has a one box for typing that spans all columns for the search.

Glenn - June 8, 2012

I can see how the single search box is generally all that is needed, and I am using that with it targeting a couple of columns.
In my case, I also have a column showing the Grade of the rows (with values of A, B, or C) and the customer wants to be able to filter out one or more of the Grades, so a filter on just that column seemed like the way to go about it.

8. Morten - October 30, 2012

Thanks for the awsome work.

Had some issues when searching and the IQueryble was coming from nhibernate, it failed to convert from a btiwise or to a boolean.

Seems to work if you modify GenericSearchFilter() so that it used logical or/and instead of bitwise or/and.

e.g

compoundOrExpression = Expression.Or(compoundOrExpression, propertyQuery[j]);

to

compoundOrExpression = Expression.OrElse(compoundOrExpression, propertyQuery[j]);

and

compoundAndExpression = Expression.And(compoundAndExpression, compoundOrExpression)

to
compoundAndExpression = Expression.AndAlso(compoundAndExpression, compoundOrExpression)

ActiveEngine Sensei - October 30, 2012

Awesome tip, thanks. I’m glad you found this useful. If you are interested in more DataTables.Net madness – and I bring this up since you mentioned NHibernate – I have a guest post on paging with RavenDB, DataTables and MVC. Techno Lions, Tigers and Bears oh my! Sorry, it’s almost Halloween.

Here is the link to the paging with RavenDB article:

http://antjanus.com/blog/web-development-tutorials/how-to-with-ravendb-and-datatables-net/

Thanks again for reading.

9. dach - January 9, 2013

Hi Sensei, your project seems to be really powerful but I can not make it work.

I have an error at this line :
sortable = this.searchAndSortables.First(x => x.IsSortable);

Since there is nothing in the searchAndSortables list, I understand the problem.

So I tried to find why the searchAndSortables list is empty and it seems that the searchAndSortables are based on the cols which are contained in the “sColumns” field of aoData.

When I take a look at the “sColumns” field in the aoData object, the value is empty so it seems alright for me that the cols var is empty and then the searchAndSortables is empty too.

Since I haven’t changed anything to your code excepted the paths of the 3 json files, I don’t understand why it works on your side and not on mine.

Do you have an idea to solve my problem?

Thanks.

ActiveEngine Sensei - January 9, 2013

dach,

If you look at my sample you should be able to see the difference. As a start, can you set a default sort order in the aoColumnDef and execute appropriately?

10. Paul Inglis - August 12, 2013

Sensei – you saved me a lot of work trying to get a generic search to work!
I was hitting a lot of the ToString() issues mentioned above and I just wanted to drop you a quick note of code I’ve modified your class with to get it to work….and also utilise the “Like” SQL comparison:

private static MethodInfo miTL = typeof(String).GetMethod(“ToLower”, System.Type.EmptyTypes);
private static MethodInfo miS = typeof(String).GetMethod(“StartsWith”, new Type[] { typeof(String) });
private static MethodInfo miC = typeof(String).GetMethod(“Contains”, new Type[] { typeof(String) });
private static MethodInfo miE = typeof(String).GetMethod(“EndsWith”, new Type[] { typeof(String) });

///
/// Create a Lambda Expression that is chain of Or Expressions
/// for each column. Each column will be tested if it contains the
/// generic search string.
///
/// Query logic = (or … or …) And (or … or …)
///
/// IQueryable of T
public static IQueryable Filter(IQueryable source, string searchTerm)
{
var propNames = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public)
.Where(e => e.PropertyType == typeof(string))
.Select(x => x.Name).ToList();

var predicate = PredicateBuilder.False();

foreach (var name in propNames)
{
predicate = predicate.Or(FilterByString(name, searchTerm));
}

return source.Where(predicate);
}

///
/// Creates a lambda expression for the given property
///
/// Anonymous class
/// property name
/// value of search phrase
/// Expression of T, bool
private static Expression<Func> FilterByString(string property,
string value)
{
var obj = Expression.Parameter(typeof(T), “obj”);
var propertySelector = Expression.PropertyOrField(obj, property);
ParameterExpression parameterExpression = null;
var memberExpression = GetMemberExpression(propertySelector, out parameterExpression);
var dynamicExpression = Expression.Call(memberExpression, miTL);
Expression constExp = Expression.Constant(value.ToLower());

dynamicExpression = Expression.Call(dynamicExpression, miC, constExp);

var pred = Expression.Lambda<Func>(dynamicExpression, new[] { parameterExpression });
//return query.Where(pred);
return pred;
}

///
/// Gets the member expression of given property
///
///
///
/// Expression
private static Expression GetMemberExpression(Expression expression, out ParameterExpression parameterExpression)
{
parameterExpression = null;
if (expression is MemberExpression)
{
var memberExpression = expression as MemberExpression;
while (!(memberExpression.Expression is ParameterExpression))
memberExpression = memberExpression.Expression as MemberExpression;
parameterExpression = memberExpression.Expression as ParameterExpression;
return expression as MemberExpression;
}
if (expression is MethodCallExpression)
{
var methodCallExpression = expression as MethodCallExpression;
parameterExpression = methodCallExpression.Object as ParameterExpression;
return methodCallExpression;
}
return null;
}

Then go back to the public FormattedList Filter() function and swap out the following lines:

var records = this.queryable.Where(GenericSearchFilter());

with

var records = Filter(this.queryable, this.genericSearch);

works like a dream!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: