Dynamic LINQ on DataTable

 

I’ve seen lots of examples of dynamic LINQ on the web. But all of them were using either LINQ to SQL or LINQ to Entity. I did not see any good example on net for using Dynamic LINQ on Data table.

My application was written in C# 2.0 with heavy use of data tables. Now since i’ve migrated my code to C# 4.0, i want to leverage LINQ and other cool features by not breaking my existing code base.

My Problem: In my data table, i’ve a property name “ARRIVE_TIME”; user has the option to filter the arrival time by selecting check box: Morning (up to 12 PM) , Afternoon (after 12 PM to 4 PM)  and Evening (after 4 PM). Based on their selection i would need to filter my data table which by default fetch all the times from the database. User has the option to select either one or all of the options. Initially i thought of using the Row Filter property of data table by dynamically creating the condition, but its an old school method. I wanted to use LINQ power to filter the data. Having said that, Microsoft did not make it any simple for using Dynamic LINQ on data tables. I even checked the System.Linq.Dynamic.DynamicQueryable class and it was useless for data tables.

So i had to write my own implementation for using Dynamic LINQ on data tables or data sets.

Solution: First we export data table as List<DataRow>. And then we define an expression of function datarow and boolean.

var data = (from t in datatable1.AsEnumerable().AsQueryable<DataRow>()
                   select t).ToList<DataRow>();

Expression<Func<DataRow, bool>> condition = null;

Now we add the conditions to expression based on the check box checked;

if (chkMorning.Checked)
{
  condition = (p => p.Field<DateTime>("ARRIVE_TIME") <= Convert.toDateTime("12:00 PM"));
}
if (chkAfternoon.Checked)
{
  condition = condition.Or(p => p.Field<DateTime>("ARRIVE_TIME") > Convert.toDateTime("12:00 PM"));
  condition = condition.And(p => p.Field<DateTime>("ARRIVE_TIME") <= Convert.toDateTime("04:00 PM"));
}
if (chkEvening.Checked)
{
  condition = condition.Or(p => p.Field<DateTime>("ARRIVE_TIME") > Convert.toDateTime("04:00 PM"));
}

You may need to tweak the above logic a bit based on your condition as what if the Morning check box is not checked then condition.Or in the next line will throw an error. So based on your requirements you need to set the first expression with out using Or/And.

I’ve written the Or and And extension methods to make this expression more readable.

public static Expression<Func<DataRow, bool>> And(this Expression<Func<DataRow, bool>> _leftside, Expression<Func<DataRow, bool>> _rightside)
    {
      ParameterExpression param = Expression.Parameter(typeof(DataRow));
      return Expression.Lambda<Func<DataRow, bool>>
             (Expression.AndAlso(Expression.Invoke(_leftside, param),Expression.Invoke(_rightside, param)), param);
    }

    public static Expression<Func<DataRow, bool>> Or(this Expression<Func<DataRow, bool>> _leftside, Expression<Func<DataRow, bool>> _rightside)
    {
      ParameterExpression param = Expression.Parameter(typeof(DataRow));
      return Expression.Lambda<Func<DataRow, bool>>
             (Expression.OrElse(Expression.Invoke(_leftside, param), Expression.Invoke(_rightside, param)), param);
    }

Finally, you use the expression in you list. List.Where method will only accept the predicate of type FUNC<DataRow, bool>. so we just need to compile the expression.

data = data.Where(condition.Compile()).ToList<DataRow>();

You can use my Linq to data table method in order to get the data table back with filtered data.