Linq to Entity with dynamic Where clause

Posted on: 2012-08-02

Some scenario, like searching with criteria, can lead to have custom where clause. You may want to get information from one criteria and some time from more.

You can write many Where clause and query depending of the information received, but this will rapidly lead to n^2 problem.

 public void DisplayData(int status=-1, DateTime creationDate=DateTime.Min, decimal price=0) { 
    var myData =_dataContext.Data;

    if(status!=-1 && creationDate == DateTime.Min && price==0) { 
      Display(myData.Where(d=>d.Status == status); 
    } 
    else if(status!=-1 && creationDate != DateTime.Min && price==0) { 
      Display(myData.Where(d=>d.Status == status && d.Date == creationDate); 
    } else if(...) { 
      //... 
    } 
} 

With 3 filters, it requires to have 9 if statement. This is ain't the good way to do it. They are many ways to work with predicate but the simplest is by using the LinqKit dll. This dll contain a PredicateBuilder that use standard Lambda Expression but abstract the complexity of the syntax.

public void DisplayData(int status=-1, DateTime creationDate=DateTime.Min, decimal price=0) { 
  var predicate = PredicateBuilder.True<MyObject>(); 
  if(status!=-1) { 
    predicate = predicate.And(e=>e.Status == status); 
  } 
  else if(creationDate != DateTime.Min) { 
    predicate = predicate.And(e=>e.Date== creationDate ); 
  } else if(price !=0) { 
    predicate = predicate.And(e=>e.Price== price ); 
  } 
  var myData =_dataContext.Data; DisplaymyData.AsExpandable().Where(predicate)); 
} 

As you can see, now it's 1 if statement per filter. We used AND but we could have used OR. The documentation is simple and the library is free, and open source.

Few details to keep in mind. The first one is that it must be used on a IQueryable collection, not IEnumerable. This will give you the possibility to pass in the Where clause an Expression instead of a simple func. This give the advantage to let Linq To Entity handling the filter to the database instead of running it as an object predicate with Linq to Object.

The second thing is the AsExpandable(). This will convert the IQueryable into a IExpandable. This will give the leverage to Linq to Entity to execute the expression.

With this procedure of using Predicate with Linq to Entity, you can define your filter with Expression in an external class that return an Expression and use the correct filter depending of the situation. It makes your code cleaner.

Concerning unit testing, you need to compile the expression before using it. This is done with a single line of code.

 Expression<Func<MyObject, bool>> expression= GetMyFilterBuiltWithPredicate(); 
 var function = expression.Compile(); 
 var responseInBoolean = function(myObject); 

The first line is where you get your filter. This could have been the predicate builder object. The second line compile the expression into a function and the last line pass an object to test the predicate and receive the answer as a boolean. You can then assert the response.