Saturday, November 1, 2014

Implement Predicate Builder to dynamically generate LINQ

PredicateBuilder class is not a part of Microsoft’s library but it is very useful when we want to generate LINQ query dynamically. Let me give one example. If you see any e-commerce application, you can find strong drill down mechanism where we can filter the exact product.

For example we want to see books which talks about ”Microsoft or Java” technology  AND the price should be “greater then 100 and less than 300”


So, here is multiple condition needs to satisfy at a time and this is the exact scenario where we can implement our own “PredicateBuilder” class just like below.

public static class PredicateBuilder
    {
        public static Expression<Func<T, bool>> True<T>() { return f => true; }
        public static Expression<Func<T, bool>> False<T>() { return f => false; }
        public static Expression<Func<T, bool>> True<T>(IQueryable<T> query) { 
        return f => true; }
        public static Expression<Func<T, bool>> False<T>(IQueryable<T> query) { 
        return f => false; }


        public static Expression<Func<T, bool>> Or<T>(
                                  this Expression<Func<T, bool>> expr1,
                                  Expression<Func<T, bool>> expr2)
        {
          var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
          return Expression.Lambda<Func<T, bool>>
             (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
        }

        public static Expression<Func<T, bool>> And<T>(
                                   this Expression<Func<T, bool>> expr1,
                                   Expression<Func<T, bool>> expr2)
        {
          var invokedExpr = Expression.Invoke(
                      expr2, expr1.Parameters.Cast<Expression>());
                      return Expression.Lambda<Func<T, bool>>
                      (Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters);
        }

    }

Here is the Main() function to built query using PredicateBuilder class.

       class Program
        {
            static void Main(string[] args)
            {

                using (var db = new AppEntities())
                {
                    var returnResut = db.Books.AsQueryable();

                    //Condition to search based on Category
                    var technology = PredicateBuilder.False<Book>();
                    technology = technology.Or(p => p.Category.Contains("Microsoft"));
                    technology = technology.Or(p => p.Category.Contains("Java"));

                    //Condition in price
                    var price = PredicateBuilder.True<Book>();
                    price = price.And(p => p.price > 100);
                    price = price.And(p => p.price < 300);

                    //Query formation
                    var data = db.Books.AsExpandable().
                                Where(technology).
                                Where(price).
                                ToList();

                }
                Console.ReadLine();
            }
        }

Note: please refer this package from nugget package manager.

Install-Package LinqKit

Here is the Book table which we have refer in Entity Framework . In time of predicate building we have specified that the Category is either Microsoft OR Java AND the price is more than 100 and less than 300. So by maintaining this criteria it should return 2 records. 


In addition, here it is giving exactly 2 records. 


1 comment:

  1. Is there any way to index deeper into the Book object?

    I want to do: technology.Or(p => p.Category.CategoryName.Contains("Java"));

    But it won't let me.

    ReplyDelete