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

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

3 Reasons Why Progressive Web Apps (PWAs) Won’t Replace Native Apps

Many people believe Progressive Web Apps (PWAs) are the future of the mobile web, but in my opinion, PWAs are not a replacement for native mobile apps. Here are three reasons why: 1. Native mobile apps provide a smoother & faster experience  Mobile websites, progressive or otherwise are slower and not as smooth. 90% of the time spent is spent using apps vs the browser . The single most significant contributing factor to a smooth experience on mobile is the speed of the network and latency of the data downloaded and uploaded. When you visit websites on desktop or mobile, there is a lot of third-party code/data that gets downloaded to your device, which more often than not has zero impact on the user experience. This includes: CSS (Cascading Style Sheets) JavaScript Ad network code Facebook tracking code Google tracking code The median number of requests a mobile website makes is a shocking  69 . On the other hand, native apps only get the data that is requi

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