Click here to Skip to main content
11,579,093 members (63,166 online)
Click here to Skip to main content

Build Where Clause Dynamically in Linq

, 6 May 2014 CPOL 89.1K 63
Rate this:
Please Sign up or sign in to vote.
Build where clause dynamically in Linq

Introduction

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        { get ; set ; }
     public string Surname     { get ; set ; }
     public int    Age         { get ; set ; }
     public string City        { get ; set ; }
     public double Salary      { get ; set ; }
     public bool   IsHomeOwner { get ; set ; }
}

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 <Person , bool > Build(IList <Filter > filters)

where Filter class is declared as:

 public  class  Filter 
 {
     public  string  Property { get ; set ; }
     public  object  Value { get ; set ; }
 }

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<Person, bool> Build(IList<Filter2> 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<Person, bool>>(exp, param).Compile();
     }
 
     private static Expression GetExpression(ParameterExpression param, Filter2 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, Filter2 filter1, Filter2 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<T, bool>>(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 { get ; set ; }
     public Op Operation { get ; set ; }
     public object Value { get ; set ; }
 }

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();

The 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).

UPDATE:

There are quite a few questions about implementing other features. 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, of if the property is string then the operations <, > >= might return unexpected results.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Fitim Skenderi
Software Developer (Senior) KOS-LIGHTS.COM
Sweden Sweden
No Biography provided

You may also be interested in...

Comments and Discussions

 
GeneralBuild Where Clause Dynamically in Linq Pin
Member 1134071931-May-15 22:48
memberMember 1134071931-May-15 22:48 
GeneralRe: Build Where Clause Dynamically in Linq Pin
Fitim Skenderi1-Jun-15 0:59
memberFitim Skenderi1-Jun-15 0:59 
AnswerExcellent work Pin
SIVA RAMAMOORTHY29-May-15 0:29
memberSIVA RAMAMOORTHY29-May-15 0:29 
GeneralRe: Excellent work Pin
Fitim Skenderi29-May-15 2:52
memberFitim Skenderi29-May-15 2:52 
QuestionDynamic object instead of Static class object Pin
HM India26-May-15 5:30
memberHM India26-May-15 5:30 
AnswerRe: Dynamic object instead of Static class object Pin
Fitim Skenderi29-May-15 2:44
memberFitim Skenderi29-May-15 2:44 
QuestionThank you! Pin
Elizma12-Apr-15 0:57
memberElizma12-Apr-15 0:57 
AnswerRe: Thank you! Pin
Fitim Skenderi12-Apr-15 21:00
memberFitim Skenderi12-Apr-15 21:00 
QuestionThanks for sharing. Here is a minor update to handle nullable types Pin
Member 22488059-Apr-15 11:23
memberMember 22488059-Apr-15 11:23 
AnswerRe: Thanks for sharing. Here is a minor update to handle nullable types Pin
Fitim Skenderi10-Apr-15 1:09
memberFitim Skenderi10-Apr-15 1:09 
AnswerRe: Thanks for sharing. Here is a minor update to handle nullable types Pin
Elizma12-Apr-15 0:59
memberElizma12-Apr-15 0:59 
GeneralMy vote of 3 Pin
xr280xr31-Mar-15 9:33
memberxr280xr31-Mar-15 9:33 
GeneralGreat Work Pin
Member 1145582616-Feb-15 3:06
memberMember 1145582616-Feb-15 3:06 
GeneralRe: Great Work Pin
Fitim Skenderi17-Feb-15 22:57
memberFitim Skenderi17-Feb-15 22:57 
GeneralVery helpful.. just what I was looking for.. Pin
coolguy_anky150525-Dec-14 20:01
membercoolguy_anky150525-Dec-14 20:01 
GeneralRe: Very helpful.. just what I was looking for.. Pin
Fitim Skenderi28-Dec-14 22:35
memberFitim Skenderi28-Dec-14 22:35 
QuestionContains doesn't work for me. Any idea? Pin
shellhelix9-Dec-14 5:11
membershellhelix9-Dec-14 5:11 
AnswerRe: Contains doesn't work for me. Any idea? Pin
Fitim Skenderi10-Dec-14 5:04
memberFitim Skenderi10-Dec-14 5:04 
GeneralRe: Contains doesn't work for me. Any idea? Pin
P-VX12-Jun-15 11:34
memberP-VX12-Jun-15 11:34 
GeneralRe: Contains doesn't work for me. Any idea? Pin
Fitim Skenderi13-Jun-15 13:13
memberFitim Skenderi13-Jun-15 13:13 
GeneralGood One Pin
Anurag Gandhi24-Sep-14 5:29
professionalAnurag Gandhi24-Sep-14 5:29 
GeneralRe: Good One Pin
Fitim Skenderi21-Oct-14 5:47
memberFitim Skenderi21-Oct-14 5:47 
QuestionTime question Pin
k56ty15-Sep-14 10:20
memberk56ty15-Sep-14 10:20 
AnswerRe: Time question Pin
Fitim Skenderi18-Sep-14 6:29
memberFitim Skenderi18-Sep-14 6:29 
QuestionFilter Excluding Pin
Randy Maloney4-Sep-14 7:50
memberRandy Maloney4-Sep-14 7:50 
AnswerRe: Filter Excluding Pin
Fitim Skenderi10-Sep-14 22:46
memberFitim Skenderi10-Sep-14 22:46 
QuestionAwesome job Pin
hamid nabati19-Aug-14 8:04
memberhamid nabati19-Aug-14 8:04 
AnswerRe: Awesome job Pin
Fitim Skenderi19-Aug-14 22:41
memberFitim Skenderi19-Aug-14 22:41 
Questiondynamic linq The LINQ expression node type 'Invoke' is not supported Pin
msanaei13-Jul-14 5:30
membermsanaei13-Jul-14 5:30 
AnswerRe: dynamic linq The LINQ expression node type 'Invoke' is not supported Pin
Fitim Skenderi13-Jul-14 21:58
memberFitim Skenderi13-Jul-14 21:58 
QuestionIf there is no filter its giving error Pin
venkatmca00825-Jun-14 4:28
professionalvenkatmca00825-Jun-14 4:28 
AnswerRe: If there is no filter its giving error Pin
Fitim Skenderi25-Jun-14 8:15
memberFitim Skenderi25-Jun-14 8:15 
GeneralRe: If there is no filter its giving error Pin
venkatmca00825-Jun-14 23:18
professionalvenkatmca00825-Jun-14 23:18 
GeneralRe: If there is no filter its giving error Pin
Fitim Skenderi26-Jun-14 11:27
memberFitim Skenderi26-Jun-14 11:27 
Question".Equals" vs. ".Equal" Pin
KentuckyEnglishman4-Jun-14 10:46
memberKentuckyEnglishman4-Jun-14 10:46 
AnswerRe: ".Equals" vs. ".Equal" Pin
Fitim Skenderi4-Jun-14 22:40
memberFitim Skenderi4-Jun-14 22:40 
GeneralRe: ".Equals" vs. ".Equal" Pin
KentuckyEnglishman5-Jun-14 4:32
memberKentuckyEnglishman5-Jun-14 4:32 
GeneralRe: ".Equals" vs. ".Equal" Pin
Fitim Skenderi5-Jun-14 6:53
memberFitim Skenderi5-Jun-14 6:53 
GeneralRe: ".Equals" vs. ".Equal" Pin
KentuckyEnglishman6-Jun-14 0:14
memberKentuckyEnglishman6-Jun-14 0:14 
QuestionNice! Pin
Volynsky Alex6-May-14 22:31
professionalVolynsky Alex6-May-14 22:31 
AnswerRe: Nice! Pin
Fitim Skenderi4-Jun-14 22:40
memberFitim Skenderi4-Jun-14 22:40 
QuestionRe: Nice! Pin
Volynsky Alex5-Jun-14 3:16
professionalVolynsky Alex5-Jun-14 3:16 
GeneralGood Article... Pin
Stuart_King6-May-14 6:11
memberStuart_King6-May-14 6:11 
GeneralRe: Good Article... Pin
Fitim Skenderi6-May-14 10:25
memberFitim Skenderi6-May-14 10:25 
QuestiondynamicLinq from MS Pin
jogibear99886-May-14 5:32
memberjogibear99886-May-14 5:32 
AnswerRe: dynamicLinq from MS Pin
Fitim Skenderi6-May-14 10:27
memberFitim Skenderi6-May-14 10:27 
QuestionMejora Pin
Member 863676627-Feb-14 4:52
memberMember 863676627-Feb-14 4:52 
AnswerRe: Mejora Pin
Fitim Skenderi5-May-14 23:36
memberFitim Skenderi5-May-14 23:36 
GeneralRe: Mejora Pin
daniel_maldonado20-May-14 10:05
memberdaniel_maldonado20-May-14 10:05 
QuestionGreat Article - DateTime question... Pin
Chuck Barest27-Feb-14 2:32
memberChuck Barest27-Feb-14 2:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150603.1 | Last Updated 6 May 2014
Article Copyright 2013 by Fitim Skenderi
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid