Skip to main content

LINQ to Entities - Dynamic LINQ to Entities AND,OR

Recently I had an Advanced Search requirement which had to enable Users to specify a combination of fields to search for and the operator e.g. AND, OR and the type of comparison to do e.g. LIKE, EQUALS. The DataLayer is built upon the Entity Framework and I'm using LINQ to Entities as the query mechanism. So my overriding goal was to keep this Strongly Typed but maintainable as well. Firstly I created a generic SearchInfo object of which there would be one for each field that needed to be included in the search.
    public class SearchInfo<T>
{
  public Expression<Func<T, object>> PropertySelector { get; set; }
  public QueryOperator Operator { get; set; }
  public StringComparer Comparer { get; set; }
  public string Value { get; set; }
}

public enum QueryOperator
{
  And = 0,
  Or = 1
}

public enum StringComparer
{
  Equals = 0,
  StartsWith = 1,
  EndsWith = 2,
  Contains = 3
}
Now if we just had to deal with the AND operator we could achieve this by conditionally chaining together LINQ queries although this would mean a lot of unnecessary code and would soon become hard to maintain. I knew I would have to dynamically build the predicate and to support this in LINQ to Entities (because it doesn't support Expression.Invoke) and after scouring the interweb for a while I came across this example. This approach works well but I could see that building up the expressions for all the combinations would soon turn into a nightmare. e.g.

Expression<Func<Car, bool>> theCarIsRed = p => p.Color == "Red";

Expression<Func<Car, bool>> theCarIsCheap = c => c.Price <>

Expression<Func<Car, bool>> theCarIsRedOrCheap = theCarIsRed.Or(theCarIsCheap);

var query = carQuery.Where(theCarIsRedOrCheap);

Because I would have many combination's of operators and the comparers it would soon get messy. After some exploring through the endless MSDN docs on the System.Linq.Expressions namespace and with the help of some handy Reflection I came up with a generic PredicateBuilder class.
  public class PredicateBuilder
{
   public static Expression<Func<T, bool>> BuildPredicate<T>(List<SearchInfo<T>> searchInfos)
   {

       //set the default predicate
       Expression<Func<T, bool>> predicate = t => false;

       if (searchInfos == null || searchInfos.Count == 0)
           return predicate;

       ParameterExpression e = Expression.Parameter(typeof(T), "e");

       foreach (var searchInfo in searchInfos)
       {
           if (string.IsNullOrEmpty(searchInfo.Value))
               continue;
                     
           System.Reflection.MethodInfo methodInfo = GetMethodInfo(searchInfo.Comparer);

           string propertyName = ExpressionToPropertyName<T, object>(searchInfo.PropertySelector);

           Expression property = Expression.Property(e, propertyName);

           Expression comparer = Expression.Call(property, methodInfo, Expression.Constant(searchInfo.Value));

           LambdaExpression whereArg = Expression.Lambda(comparer, e);

           var exp = whereArg as Expression<Func<T, bool>>;

           predicate = searchInfo.Operator == QueryOperator.And
                       ? predicate.And(exp)
                       : predicate.Or(exp);

   
       }

       return predicate;
   }

   private static string ExpressionToPropertyName<T, T2>(Expression<Func<T, T2>> selector)
   {
       MemberExpression me = selector.Body as MemberExpression;
       if (me == null)
           throw new ArgumentException("MemberException expected.");

       if (me.Expression.NodeType != ExpressionType.Parameter)
           throw new ArgumentException("Invalid lambda expression");

       if (selector.Parameters[0] != me.Expression)
           throw new ArgumentException("Invalid lambda parameter.");

       return me.Member.Name;
   }


   private static System.Reflection.MethodInfo GetMethodInfo(StringComparer comparer)
   {
       Type[] types = new Type[1];
       types[0] = typeof(string);

       System.Reflection.MethodInfo methodInfo = typeof(string).GetMethod(comparer.ToString(), types);
       if (methodInfo == null)
           throw new ArgumentException("No method info supported for " + comparer.ToString());

       return methodInfo;

   }
}
So the full usage would be similar to this:

public class ProductController
  {
      [AcceptVerbs(HttpVerbs.Post)]
      public ActionResult FindProduct()
      {
           SearchInfo<Product> nameInfo = new SearchInfo<Product>()
          {
              Comparer = StringComparer.Contains, //this would come from a Request["key"] value
              PropertySelector = p => p.Name,
              Operator = QueryOperator.And, //this would come from a Request["key"] value
              Value = "SQL" //this would come from a Request["key"] value
          };

          SearchInfo<Product> descriptionInfo = new SearchInfo<Product>()
          {
              Comparer = StringComparer.Contains, //this would come from a Request["key"] value
              PropertySelector = p => p.Description, //this would come from a Request["key"] value
              Operator = QueryOperator.Or, //this would come from a Request["key"] value
              Value = "database" //this would come from a textbox or some other input
          };

          List<SearchInfo<Product>> searchInfo = new List<SearchInfo<Product>>
          {
              nameInfo,
              descriptionInfo
          };

          ViewData.Model = ProductService.Find(searchInfo);
          return View();

      }

  public class ProductService
  {
      public static List<Product> Find(List<SearchInfo<Product>> searchInfo)
      {
        
          return ProductRepository.Find(searchInfo);

      }
  }

  public class ProductRepository
  {
      public static List<Product> Find(List<SearchInfo<Product>> searchInfo)
      {
          if (searchInfo == null || searchInfo.Count == 0)
              return null;

          using (ProductDemoEntities context = new ProductDemoEntities())
          {

              var query = context.Product.Where(PredicateBuilder.BuildPredicate<Product>(searchInfo));
                        
              return query.ToList();
          }

      }
  }
I've tried to keep this as simple as possible, in reality you would likely want to extend this so that you could search on all Types of fields instead of just the string as shown here. I hope this able to help someone else who has faced this. Until next time.

Comments

  1. Hi Will, im running .net 3.5 and have an error for this line:

    predicate = searchInfo.Operator == QueryOperator.And
    ? predicate.And(exp)
    : predicate.Or(exp);

    Error 1 No overload for method 'And' takes '1' arguments
    Error 2 No overload for method 'Or' takes '1' arguments

    ReplyDelete
  2. I am also running 3.5 and also getting the same error

    ReplyDelete
  3. Right. I got the same error. How I can realize  Contains  or Like search functions  in dynamic  LINQ?

    ReplyDelete

Post a Comment

Popular posts from this blog

Freeing Disk Space on C:\ Windows Server 2008

I just spent the last little while trying to clear space on our servers in order to install .NET 4.5. Decided to post so my future self can find the information when I next have to do this. I performed all the usual tasks: Deleting any files/folders from C:\windows\temp and C:\Users\%UserName%\AppData\Local\TempDelete all EventViewer logs Save to another Disk if you want to keep themRemove any unused programs, e.g. FirefoxRemove anything in C:\inetpub\logsRemove any file/folders C:\Windows\System32\LogFilesRemove any file/folders from C:\Users\%UserName%\DownloadsRemove any file/folders able to be removed from C:\Users\%UserName%\DesktopRemove any file/folders able to be removed from C:\Users\%UserName%\My DocumentsStop Windows Update service and remove all files/folders from C:\Windows\SoftwareDistributionDeleting an Event Logs Run COMPCLN.exe Move the Virtual Memory file to another disk However this wasn’t enough & I found the most space was cleared by using the Disk Cleanup to…

CPF Contribution Rates for new Singapore Permanent Residents (SPR’s)

Recently my wife and I applied and got approved for Singapore Permanent Residency. After completing the formalities the most significant immediate change is the contribution to CPF which is Singapore’s mandatory social security savings scheme requiring contributions from employers and employees. CPF contributions start from the date you obtain SPR status, which is the date of the entry permit.   Being a relentless budgeter I needed to know exactly how much I and my employer would have to contribute so that I could adjust my budget accordingly as the employee contributions get deducted from the monthly salary. After doing some research I discovered that there is a “graduated” approach to CPF contributions for new SPR’s where the contributions gradually increase in the first and second year and then upon reaching the third year are at the full amount. Note: There is an option for employers to contribute the full amount for year 1 and year 2 and the employee can use the graduated rate, b…

Implementing Custom Castle Windsor Facilities

If you’ve been following my posts you would know that I love Castle Windsor. One of the many useful features I have found is the Facility and I’m going to try and give a good example how you can make use of this. In a recent post I showed how you can add Cross-Cutting concerns to your application by using Interceptors.Now when configuring the Container you can explicitly configure each Interceptor per Service but when you have lot’s of components it can get pretty hard to maintain after a while and can also introduce subtle issues if someone forgets to configure it correctly.Below is how you would configure your Container without using a Facility. On the last line we are specifying the Interceptor explicitly. public void Configure() { container = new WindsorContainer(); container.Register( Component.For<CacheInterceptor>(), Component.For<ICacheProvider>() .ImplementedBy<WebCache…