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.

Popular posts from this blog

ASP.NET MVC Release Candidate - Upgrade issues - Spec#

First of all, great news that the ASP.NET MVC Release Candidate has finally been released.  Full credit to the team for the hard work on this.  You can get the download here  However this is the first time I have had upgrade issues.  Phil Haack has noted some of the issues here   If like me you have lot's of CTP's and Add-Ins then you might experience some pain in Uninstalling MVC Beta on Vista SP1  This is the list of Add-Ins / CTP's I had to uninstall to get it to work  Spec# PEX Resharper 4.1  Sourcelinks ANTS Profiler 4   Can't say I'm too impressed as it wasted over an hour of my time.  As it turned out Spec# turned out to be the offending culprit, it's forgiveable to have issues with a third party product but a Microsoft one? Guess no-one on the ASP.NET team has Spec# installed. 

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\Temp Delete all EventViewer logs Save to another Disk if you want to keep them Remove any unused programs, e.g. Firefox Remove anything in C:\inetpub\logs Remove any file/folders C:\Windows\System32\LogFiles Remove any file/folders from C:\Users\%UserName%\Downloads Remove any file/folders able to be removed from C:\Users\%UserName%\Desktop Remove any file/folders able to be removed from C:\Users\%UserName%\My Documents Stop Windows Update service and remove all files/folders from C:\Windows\SoftwareDistribution Deleting 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

Consuming the SSRS ReportExecutionService from a .NET Client

  I’ve just finished writing a nice wrapper which internally calls the SSRS ReportExecutionService to generate reports. Whilst it was fairly simple to implement there has been some major changes between 2005 and 2008 and the majority of online and documentation is based on the 2005 implementation. The most important change is that the Report Server and Report Manager are no longer hosted in IIS which will be a welcomed change to Sys Admins but makes the security model and hosting model vastly different. So far I’ve yet to figure out how to allow Anonymous Access, if anyone knows how to do this leave a comment and it will be most appreciated. Getting Started To get started you’ll want to add a service reference to http://localhost/ReportServer_SQL2008/ReportExecution2005.asmx where ReportServer_SQL2008 is the name you configure in the Reporting Services Configuration Manager. The Web Application files are located in C:\Program Files\Microsoft SQL Server\MSRS10.SQL2008\R