title: Creating dynamic expression for entity framework
body: <p>I've created a generic expression builder that builds up a predicate based on collection of conditions. I pass the predicate to a generic method in the repository. I think that expression builder works fine and creates the desired predicate although the SQL script generated by Entity Framework is not as I expected. I've read many questions and article regarding dynamic query or LinqKit and expression builder to this issue and the most relevant was <a href="https://stackoverflow.com/questions/14901430/building-dynamic-where-clauses-in-linq-to-ef-queries/14904609#14904609">this comment</a>. I really appreciate if you could look over what I have done and let me know if I made any mistake?</p>
  
  <p>Here is the code for ExpressionBuilder class:</p>
  
  <pre><code>public static class ExpressionBuilder
  {
      private static MethodInfo containsMethod = typeof(string).GetMethod("Contains");
      private static MethodInfo startsWithMethod = typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) });
      private static MethodInfo endsWithMethod = typeof(string).GetMethod("EndsWith", new Type[] { typeof(string) });
  
      public static Expression&lt;Func&lt;T, bool&gt;&gt; GetExpression&lt;T&gt;(IList&lt;ExpressionModel&gt; filters)
      {
          if (filters == null)
              return null;
  
          IList&lt;ExpressionModel&gt; nullFreeCollection = filters.OfType&lt;ExpressionModel&gt;().ToList();
  
          if (nullFreeCollection.Count == 0)
              return null;
  
          ParameterExpression param = Expression.Parameter(typeof(T), "item");
          Expression exp = null;
  
          if (nullFreeCollection.Count == 1)
              exp = GetExpression&lt;T&gt;(param, nullFreeCollection[0]);
          else if (nullFreeCollection.Count == 2)
              exp = GetExpression&lt;T&gt;(param, nullFreeCollection[0], nullFreeCollection[1]);
          else
          {
              while (nullFreeCollection.Count &gt; 0)
              {
                  var f1 = nullFreeCollection[0];
                  var f2 = nullFreeCollection[1];
  
                  if (exp == null)
                      exp = GetExpression&lt;T&gt;(param, nullFreeCollection[0], nullFreeCollection[1]);
                  else
                      exp = Expression.AndAlso(exp, GetExpression&lt;T&gt;(param, nullFreeCollection[0], nullFreeCollection[1]));
  
                  nullFreeCollection.Remove(f1);
                  nullFreeCollection.Remove(f2);
  
                  if (nullFreeCollection.Count == 1)
                  {
                      exp = Expression.AndAlso(exp, GetExpression&lt;T&gt;(param, nullFreeCollection[0]));
                      nullFreeCollection.RemoveAt(0);
                  }
              }
          }
  
          return Expression.Lambda&lt;Func&lt;T, bool&gt;&gt;(exp, param);
      }
  
      private static Expression GetExpression&lt;T&gt;(ParameterExpression param, ExpressionModel filter)
      {
          MemberExpression member = Expression.Property(param, filter.PropertyName);
          ConstantExpression constant = Expression.Constant(filter.Value);
  
          switch (filter.Operator)
          {
              case ExpressionOperators.Equals:
                  return Expression.Equal(member, constant);
              case ExpressionOperators.GreaterThan:
                  return Expression.GreaterThan(member, constant);
              case ExpressionOperators.LessThan:
                  return Expression.LessThan(member, constant);
              case ExpressionOperators.GreaterThanOrEqual:
                  return Expression.GreaterThanOrEqual(member, constant);
              case ExpressionOperators.LessThanOrEqual:
                  return Expression.LessThanOrEqual(member, constant);
              case ExpressionOperators.Contains:
                  return Expression.Call(member, containsMethod, constant);
              case ExpressionOperators.StartsWith:
                  return Expression.Call(member, startsWithMethod, constant);
              case ExpressionOperators.EndsWith:
                  return Expression.Call(member, endsWithMethod, constant);
          }
  
          return null;
      }
  
      private static BinaryExpression GetExpression&lt;T&gt;(ParameterExpression param, ExpressionModel filter1, ExpressionModel filter2)
      {
          Expression bin1 = GetExpression&lt;T&gt;(param, filter1);
          Expression bin2 = GetExpression&lt;T&gt;(param, filter2);
  
          return Expression.AndAlso(bin1, bin2);
      }
  
      public enum ExpressionOperators
      {
          Equals,
          GreaterThan,
          LessThan,
          GreaterThanOrEqual,
          LessThanOrEqual,
          Contains,
          StartsWith,
          EndsWith
      }
  }
  </code></pre>
  
  <p>And here is the generic repository method:</p>
  
  <pre><code>    public IEnumerable&lt;TEntity&gt; RetrieveCollectionAsync(Expression&lt;Func&lt;TEntity, bool&gt;&gt; predicate)
      {
          try
          {
              return DataContext.Set&lt;TEntity&gt;().Where(predicate);
          }
          catch (Exception ex)
          {
              Logger.Error(ex);
              throw ex;
          }
      }
  </code></pre>
  
  <p>And generated script by Entity Framework for Sql (I expect a select query with a few where clause):</p>
  
  <pre><code>SELECT 
      CAST(NULL AS uniqueidentifier) AS [C1], 
      CAST(NULL AS uniqueidentifier) AS [C2], 
      CAST(NULL AS varchar(1)) AS [C3], 
      CAST(NULL AS uniqueidentifier) AS [C4], 
      CAST(NULL AS uniqueidentifier) AS [C5], 
      CAST(NULL AS uniqueidentifier) AS [C6], 
      CAST(NULL AS datetime2) AS [C7], 
      CAST(NULL AS datetime2) AS [C8], 
      CAST(NULL AS varchar(1)) AS [C9], 
      CAST(NULL AS uniqueidentifier) AS [C10], 
      CAST(NULL AS varchar(1)) AS [C11], 
      CAST(NULL AS uniqueidentifier) AS [C12], 
      CAST(NULL AS uniqueidentifier) AS [C13], 
      CAST(NULL AS uniqueidentifier) AS [C14], 
      CAST(NULL AS uniqueidentifier) AS [C15], 
      CAST(NULL AS datetime2) AS [C16], 
      CAST(NULL AS varchar(1)) AS [C17], 
      CAST(NULL AS datetime2) AS [C18], 
      CAST(NULL AS varchar(1)) AS [C19], 
      CAST(NULL AS tinyint) AS [C20]
      FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
      WHERE 1 = 0
  </code></pre>
  
  <p>I am using </p>
  
  <ul>
  <li>EntityFramework 6.0 </li>
  <li>.Net Framework 4.6  </li>
  <li>ASP.NET MVC 5</li>
  </ul>
  
  <p><strong>Update</strong> the model for expression:</p>
  
  <pre><code>public class ExpressionModel
  {
      public string PropertyName { get; set; }
      public ExpressionOperators Operator { get; set; }
      public object Value { get; set; }
  }
  </code></pre>
  
  <p>Another missing part is a generic mapper that maps a given search criteria to a new ExpressionModel which I believe it is not relevant to this problem. </p>