Generic IQueryable With PredicateBuilder To Search Every Column of jquery.DataTables

Vote it up on StackOverflow

Every once in a while I become obsessed with a coding problem to the point I cannot sleep or focus on anything until I have found a way to do what I want to do.  This just happened to me in what became a concussion of problems I needed to solve.  At the same time I was looking to implement jquery.DataTables because flexiGrid is dead and DataTables seems like the new obvious open source choice I also stumbled upon David Fowlers DynamicLINQ article at http://weblogs.asp.net/davidfowler/archive/2010/08/19/dynamic-linq-part-2-evolution.aspx.

I had been using some earlier code he had for an expression tree to dynamically sort a query before sending to a grid saving grid/table specific code needing to be written for each table.  When I saw his new article I immediately knew that I wanted to be able to do the same thing for filtering/searching as the new little experimental library he has up at bitbucket had some awesome stuff in it that would make it possible to use a column name list that DataTables needs anyway be used to get the column type to be searched and even build the linq expression.

To get started I was able to easily concat all the where expressions the same way Fowler's example code did it.

var query = northwind.Products.DynamicWhere(p => p["UnitsInStock"] < 20)
                              .DynamicWhere(p => 40 > p["UnitsInStock"])
                              .DynamicWhere(p => 10 != p["UnitsInStock"])
                              .DynamicWhere(p => 4 == p["UnitsInStock"])
                              .Select(p => new
                              {
                                  p.ProductName
                              });

But that had the serious flaw that all the where statements where "and" together, so if one column doesn't have the searched for value it all fails.  Now I know, I know I could have used the DynamicQueryable route, but working with strings is just ickey, but if you can stomach that part of it it is pretty cool in that it is dynamic, meaning one filter function can filter any table of data.  And yes I could have used the illustrious Albahari PredicateBuilder but then I would have to make a different filter function every time I want to whip out a datatables, not going to do it.

I stayed stumped here for days, floating listlessly on every page of StackOverflow dealing with these matters, trying pseudo code and grinding and grinding.  I even gave up at one point but when I went back to work I could not leave my quest.  So, it is a happy ending, a pretty clean way to get this done.  A combination of things happened, I found a PredicateBuilder that does not rely on the Expand features the Albahari one does and works will with entity. 

My real problem here was how to get the dynamic expressions converted from LambdaExpressions to Expression<Func<T, bool>>.  I had already added several methods to the Fowler DynamicLinq at this point for OrderThenBy and so on so I added one more function.

   1: private static Expression<Func<T, bool>> GetFuncTbool<T>(IQueryable source, Func<dynamic, dynamic> expressionBuilder)

   2: {

   3:     ParameterExpression parameterExpression = Expression.Parameter(GetElementType(source), expressionBuilder.Method.GetParameters()[0].Name);

   4:     DynamicExpressionBuilder dynamicExpression = expressionBuilder(new DynamicExpressionBuilder(parameterExpression));

   5:  

   6:     Expression body = dynamicExpression.Expression;

   7:     return Expression.Lambda<Func<T, bool>>(body, parameterExpression);

   8: }

The magical line here is line 7 above, basically we are converting the Func<dynamic, dynamic> LamdaExpression into a Linq Expression and then returning it cast as the necessary Expression<Func<T, bool>> so that we can use it with a PredicateBuilder.

The DynamicWhere method in the Fowler library then has two flavors, one the normal way and the other for use with a PredicateBuilder.  The new method for PredicateBuilder is.

   1: public static Expression<Func<T, bool>> DynamicWhereForPredicateBuilder<T>(this IQueryable<T> source, Func<dynamic, dynamic> predicate)

   2: {            

   3:     return GetFuncTbool<T>(source, predicate);

   4: }

And that allows us to make a DataTables filter like this.

   1: public static IQueryable SearchForDataTables<T>(this IQueryable<T> query, DataTablesRequest dataTablesRequest, string[] columnNames)

   2: {

   3:     if (!String.IsNullOrEmpty(dataTablesRequest.sSearch))

   4:     {

   5:         #region Search All Fields

   6:         var predicate = PredicateBuilder.False<T>();

   7:         if (dataTablesRequest.sSingleSearchField == "All")

   8:         {

   9:             Expression<Func<T, bool>> predicateToAdd = null;

  10:             for (int i = 0; i < dataTablesRequest.iColumns; i++)

  11:             {

  12:                 if (dataTablesRequest.bSearchable[i]) // If the column is marked as searchable

  13:                 {

  14:                     string columnName = columnNames[i];

  15:                     Type columnType = query.DynamicType(z => z[columnName]);

  16:                     if (columnType == typeof(System.Int32))

  17:                     {

  18:                         var intToSearchFor = int.Parse(dataTablesRequest.sSearch);

  19:                         //for integer we do equals

  20:                         predicateToAdd = query.DynamicWhereForPredicateBuilder(z => z[columnName] == intToSearchFor);

  21:                     }

  22:                     else if (columnType == typeof(System.String))

  23:                     {   //for string we do contains

  24:                         predicateToAdd = query.DynamicWhereForPredicateBuilder(z => z[columnName].Contains(dataTablesRequest.sSearch)); //contains or

  25:                     }

  26:                     predicate = predicate.Or(predicateToAdd);

  27:                 }

  28:             }

  29:             query = query.Where(predicate);

  30:         }

  31:         #endregion

  32:  

  33:         #region Search Single Field

  34:         else if (!String.IsNullOrEmpty(dataTablesRequest.sSingleSearchField))

  35:         {

  36:             string columnName = dataTablesRequest.sSingleSearchField;

  37:             Type columnType = query.DynamicType(z => z[columnName]);

  38:             if (columnType == typeof(System.Int32))

  39:             {

  40:                 try

  41:                 {

  42:                     var intToSearchFor = int.Parse(dataTablesRequest.sSearch);

  43:                     //for integer we do equals

  44:                     query = query.DynamicWhere(z => z[columnName] == intToSearchFor);

  45:                 }

  46:                 catch { }

  47:             }

  48:             else if (columnType == typeof(System.String))

  49:             {

  50:                 //for string we do contains

  51:                 query = query.DynamicWhere(z => z[columnName].Contains(dataTablesRequest.sSearch));

  52:             }

  53:         }

  54:         #endregion

  55:     }

  56:     return query;

  57: }

I am sure I will be enjoying whipping out grids for small tables without doing hardly any coding that are all column searchable for some time.  You can find the full source for all of this at codeplex embedded in the MvcCms project, it is being used in the Opt-In Email AddOn.  The code as of now is on codeplex at.

http://mvccms.codeplex.com/SourceControl/changeset/view/1f50c394253d#src%2fMvcCms.Data%2fDynamicLINQ%2fDynamicQueryable.cs

Hope you find this useful.

Jon

Comments You must be Loged On to make a comment.

Edit Comment

Please wait...

Reply To Comment

Please wait...