Friday 24 April 2015

Build Where clause dynamically in LINQ

Imagine a scenario in which we have a collection of objects and want to allow the user to filter the collection by filtering on combination of properties. To make the scenario concrete, let's assume that our object is declared as follows:


public class Person
{
    public string Name { getset; }
    public string Surname { getset; }
    public int Age { getset; }
    public string City { getset; }
    public double Salary { getset; }
    public bool IsHomeOwner { getset; }
}

Now suppose we have a collection of Persons like this (this is just for explanation purposes, in real life you would get a much bigger collection from database):


List<Person> persons = new List<Person>
{
    new  Person  { Name = "Flamur" , Surname = "Dauti" ,    Age = 39,
                    City = "Prishtine" , IsHomeOwner = true ,  Salary = 12000.0 },
 
    new  Person  { Name = "Blerta" , Surname = "Frasheri" , Age = 25,
                    City = "Mitrovice" , IsHomeOwner = false , Salary = 9000.0 },
 
    new  Person  { Name = "Berat" ,  Surname = "Dajti" ,    Age = 45,
                    City = "Peje" ,      IsHomeOwner = true ,  Salary = 10000.0 },
 
    new  Person  { Name = "Laura" ,  Surname = "Morina" ,   Age = 23,
                    City = "Mitrovice" , IsHomeOwner = true ,  Salary = 25000.0 },
 
    new  Person  { Name = "Olti" ,   Surname = "Kodra" ,    Age = 19,
                    City = "Prishtine" , IsHomeOwner = false , Salary = 8000.0 },
 
    new  Person  { Name = "Xhenis" , Surname = "Berisha" ,  Age = 26,
                    City = "Gjakove" ,   IsHomeOwner = false , Salary = 7000.0 },
 
    new  Person  { Name = "Fatos" ,  Surname = "Gashi" ,    Age = 32,
                    City = "Peje" ,      IsHomeOwner = true ,  Salary = 6000.0 },
 
};

Suppose we want to allow the user to filter the collection on any property or any combination of properties (on our UI form). One way would be to have a function for each property and each combination of properties, something like:


public IList<Person> FilterByName(string name)
{
    return persons.Where(p => p.Name == name).ToList();
}
 
public IList<Person> FilterBySurname(string surname)
{
    return persons.Where(p => p.Surname == surname).ToList();
}
 
public IList<Person> FilterByNameAndSurname(string name, string surname)
{
    return persons.Where(p => p.Name == name && p.Surname == surname).ToList();
}

. . .


As you can see, this becomes a very tedious job since the number of functions to cover all possible combinations is quite big.

The other way to filter the collection, which is much more convenient and tidier is to build an expression tree dynamically and pass it to the where clause for filtering. The function signature that will build expression trees will look like:


public Func<Personbool> Build(IList<Filter> filters)
{
    return null;
}

where Filter class is declared as:


public class Filter
{
    public string Property { getset; }
    public object Value { getset; }
}

And it is used to contain the name of the property and the value that we want to filter our collection on. I won’t go into the details of how to build expression trees, since there is a lot of information about it on the web, so the class for building the expression trees looks like:


public class PersonExpressionBuilder
{
    public static Func<Personbool> Build(IList<Filter> filters)
    {
        ParameterExpression param = Expression.Parameter(typeof(Person), "t");
        Expression exp = null;
 
        if (filters.Count == 1)
            exp = GetExpression(param, filters[0]);
        else if (filters.Count == 2)
            exp = GetExpression(param, filters[0], filters[1]);
        else
        {
            while (filters.Count > 0)
            {
                var f1 = filters[0];
                var f2 = filters[1];
 
                if (exp == null)
                    exp = GetExpression(param, filters[0], filters[1]);
                else
                    exp = Expression.AndAlso(exp, GetExpression(param, filters[0], filters[1]));
 
                filters.Remove(f1);
                filters.Remove(f2);
 
                if (filters.Count == 1)
                {
                    exp = Expression.AndAlso(exp, GetExpression(param, filters[0]));
                    filters.RemoveAt(0);
                }
            }
        }
 
        return Expression.Lambda<Func<Personbool>>(exp, param).Compile();
    }
 
    private static Expression GetExpression(ParameterExpression param, Filter filter)
    {
        MemberExpression member = Expression.Property(param, filter.PropertyName);
        ConstantExpression constant = Expression.Constant(filter.Value);
        return Expression.Equal(member, constant);
    }
 
    private static BinaryExpression GetExpression
    (ParameterExpression param, Filter filter1, Filter filter2)
    {
        Expression bin1 = GetExpression(param, filter1);
        Expression bin2 = GetExpression(param, filter2);
 
        return Expression.AndAlso(bin1, bin2);
    }
}


To test our expression builder, we would use it inside a method as follows:


List<Filter> filter = new List<Filter>
{
    new  Filter  { PropertyName = "City" , Value = "Mitrovice"  },
    new  Filter  { PropertyName = "IsHomeOwner" , Value = false  }
};
 
var deleg = PersonExpressionBuilder.Build(filter);
var filteredCollection = persons.Where(deleg).ToList();

As it is, the expression builder builds expression trees that check only if the value of the property is equal to the provided value, but we can take this approach one step further and make it generic so it can be used in other places with other types as well and also extend it so it can check for other comparisons as well. So our generic expression builder class will look like:


public static class ExpressionBuilder
{
    private static MethodInfo containsMethod = typeof(string).GetMethod("Contains");
    private static MethodInfo startsWithMethod =
    typeof(string).GetMethod("StartsWith"new Type[] { typeof(string) });
    private static MethodInfo endsWithMethod =
    typeof(string).GetMethod("EndsWith"new Type[] { typeof(string) });
 
 
    public static Expression<Func<T,bool>> GetExpression<T>(IList<Filter> filters)
    {
        if (filters.Count == 0)
            return null;
 
        ParameterExpression param = Expression.Parameter(typeof(T), "t");
        Expression exp = null;
 
        if (filters.Count == 1)
            exp = GetExpression<T>(param, filters[0]);
        else if (filters.Count == 2)
            exp = GetExpression<T>(param, filters[0], filters[1]);
        else
        {
            while (filters.Count > 0)
            {
                var f1 = filters[0];
                var f2 = filters[1];
 
                if (exp == null)
                    exp = GetExpression<T>(param, filters[0], filters[1]);
                else
                    exp = Expression.AndAlso(exp, GetExpression<T>(param, filters[0], filters[1]));
 
                filters.Remove(f1);
                filters.Remove(f2);
 
                if (filters.Count == 1)
                {
                    exp = Expression.AndAlso(exp, GetExpression<T>(param, filters[0]));
                    filters.RemoveAt(0);
                }
            }
        }
 
        return Expression.Lambda<Func<Tbool>>(exp, param);
    }
 
    private static Expression GetExpression<T>(ParameterExpression param, Filter filter)
    {
        MemberExpression member = Expression.Property(param, filter.PropertyName);
        ConstantExpression constant = Expression.Constant(filter.Value);
 
        switch (filter.Operation)
        {
            case Op.Equals:
                return Expression.Equal(member, constant);
 
            case Op.GreaterThan:
                return Expression.GreaterThan(member, constant);
 
            case Op.GreaterThanOrEqual:
                return Expression.GreaterThanOrEqual(member, constant);
 
            case Op.LessThan:
                return Expression.LessThan(member, constant);
 
            case Op.LessThanOrEqual:
                return Expression.LessThanOrEqual(member, constant);
 
            case Op.Contains:
                return Expression.Call(member, containsMethod, constant);
 
            case Op.StartsWith:
                return Expression.Call(member, startsWithMethod, constant);
 
            case Op.EndsWith:
                return Expression.Call(member, endsWithMethod, constant);
        }
 
        return null;
    }
 
    private static BinaryExpression GetExpression<T>
    (ParameterExpression param, Filter filter1, Filter filter2)
    {
        Expression bin1 = GetExpression<T>(param, filter1);
        Expression bin2 = GetExpression<T>(param, filter2);
 
        return Expression.AndAlso(bin1, bin2);
    }
}


And the filter class has been extended to take a comparison operation as well:


public class Filter
{
    public string PropertyName { getset; }
    public Op Operation { getset; }
    public object Value { getset; }
}


And the operation is declared as enumeration:


public enum Op
{
    Equals,
    GreaterThan,
    LessThan,
    GreaterThanOrEqual,
    LessThanOrEqual,
    Contains,
    StartsWith,
    EndsWith
}


Then the new generic expression builder would be used as follows:


List<Filter> filter = new List<Filter>()
{
    new Filter { PropertyName = "City" ,
        Operation = Op .Equals, Value = "Mitrovice"  },
    new Filter { PropertyName = "Name" ,
        Operation = Op .StartsWith, Value = "L"  },
    new Filter { PropertyName = "Salary" ,
        Operation = Op .GreaterThan, Value = 9000.0 }
};
 
var deleg = ExpressionBuilder.GetExpression<Person>(filter).Compile();
var filteredCollection = persons.Where(deleg).ToList();


he ExpressionBuilder class can be extended for other Linq operations. It can also be easily used to remotely execute Linq statements (by making the class Filter serializable, it can be passed to a WCF service and so on). The most important thing to remember when adding features is that the expression generates a C# executable code. So for example if your property is Nullable<int> then you need to remember that Nullable<int> is a different type from int, and more importantly it does NOT implement all the properties/methods that int does, or if the property is string then the operations <, > and >= might return unexpected results.

No comments:

Post a Comment