Creating dynamic expression for entity framework

Question

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 this comment. I really appreciate if you could look over what I have done and let me know if I made any mistake?

Here is the code for ExpressionBuilder class:

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<Func<T, bool>> GetExpression<T>(IList<ExpressionModel> filters)
    {
        if (filters == null)
            return null;

        IList<ExpressionModel> nullFreeCollection = filters.OfType<ExpressionModel>().ToList();

        if (nullFreeCollection.Count == 0)
            return null;

        ParameterExpression param = Expression.Parameter(typeof(T), "item");
        Expression exp = null;

        if (nullFreeCollection.Count == 1)
            exp = GetExpression<T>(param, nullFreeCollection[0]);
        else if (nullFreeCollection.Count == 2)
            exp = GetExpression<T>(param, nullFreeCollection[0], nullFreeCollection[1]);
        else
        {
            while (nullFreeCollection.Count > 0)
            {
                var f1 = nullFreeCollection[0];
                var f2 = nullFreeCollection[1];

                if (exp == null)
                    exp = GetExpression<T>(param, nullFreeCollection[0], nullFreeCollection[1]);
                else
                    exp = Expression.AndAlso(exp, GetExpression<T>(param, nullFreeCollection[0], nullFreeCollection[1]));

                nullFreeCollection.Remove(f1);
                nullFreeCollection.Remove(f2);

                if (nullFreeCollection.Count == 1)
                {
                    exp = Expression.AndAlso(exp, GetExpression<T>(param, nullFreeCollection[0]));
                    nullFreeCollection.RemoveAt(0);
                }
            }
        }

        return Expression.Lambda<Func<T, bool>>(exp, param);
    }

    private static Expression GetExpression<T>(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<T>(ParameterExpression param, ExpressionModel filter1, ExpressionModel filter2)
    {
        Expression bin1 = GetExpression<T>(param, filter1);
        Expression bin2 = GetExpression<T>(param, filter2);

        return Expression.AndAlso(bin1, bin2);
    }

    public enum ExpressionOperators
    {
        Equals,
        GreaterThan,
        LessThan,
        GreaterThanOrEqual,
        LessThanOrEqual,
        Contains,
        StartsWith,
        EndsWith
    }
}

And here is the generic repository method:

    public IEnumerable<TEntity> RetrieveCollectionAsync(Expression<Func<TEntity, bool>> predicate)
    {
        try
        {
            return DataContext.Set<TEntity>().Where(predicate);
        }
        catch (Exception ex)
        {
            Logger.Error(ex);
            throw ex;
        }
    }

And generated script by Entity Framework for Sql (I expect a select query with a few where clause):

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

I am using

• EntityFramework 6.0
• .Net Framework 4.6
• ASP.NET MVC 5

Update the model for expression:

public class ExpressionModel
{
    public string PropertyName { get; set; }
    public ExpressionOperators Operator { get; set; }
    public object Value { get; set; }
}

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.

Lining

Looks overcomplicated. Can you include the missing parts - ExpressionModel class and other GetExpression functions that you are calling from inside the method posted. Just looking at the code it's quite unclear why you have a function with 2 model arguments. – Manly

@IvanStoev I have updated the question as per request. – Lining

Answer 1

As I mentioned in the comments, the implementation is too overcomplicated.

First, this method

private static BinaryExpression GetExpression<T>(ParameterExpression param, ExpressionModel filter1, ExpressionModel filter2)

and the whole logic for checking filters count, removing processed items, etc. is redundant. AND conditions can easily be chained like this

((Condition1 AND Condition2) AND Condition3) AND Condition4 ...

So just remove that function.

Second, this function

private static Expression GetExpression<T>(ParameterExpression param, ExpressionModel filter)

is poorly named and does not need a generic T because it's not used inside.

Instead, change the signature to

private static Expression MakePredicate(ParameterExpression item, ExpressionModel filter)
{
    // implementation (same as posted)
}

Finally, the public method is simple as that:

public static Expression<Func<T, bool>> MakePredicate<T>(IEnumerable<ExpressionModel> filters)
{
    if (filters == null) return null;
    filters = filters.Where(filter => filter != null);
    if (!filters.Any()) return null;
    var item = Expression.Parameter(typeof(T), "item");
    var body = filters.Select(filter => MakePredicate(item, filter)).Aggregate(Expression.AndAlso);
    var predicate = Expression.Lambda<Func<T, bool>>(body, item);
    return predicate;
}

P.S. And don't forget to do null check in the usage:

// should not be called Async
public IEnumerable<TEntity> RetrieveCollectionAsync(Expression<Func<TEntity, bool>> predicate)
{
    try
    {
        var query = DataContext.Set<TEntity>().AsQueryable();
        if (predicate != null)
            query = query.Where(predicate);
        return query;
    }
    catch (Exception ex)
    {
        Logger.Error(ex);
        throw ex; // should be: throw;
    }
}

Manly

Thank you for your answer Ivan. If I'm not mistaken in the repository you are retrieving the whole table and using Linq To Object you running a filter by the predicate. I believe you overlooked the part that I mentioned I am expecting entity framework generates an script with a few where clause. I am looking for Linq to Entity solution not Linq to Object. – Lining

@DeveloperX Read the answer carefully. It's using LINQ to Object only to easy build the expressions. But the expressions are EF compatible. Basically it's simplified implementation of your code. The public MakePredicate method corresponds to your public GetExpression. You can call it your way if you like. – Manly

If you mean this line var query = DataContext.Set<TEntity>().AsQueryable();, it doesn't execute the query. It's just a good practice to check for null argument. Even your expression builder method has branches that return null. – Manly

Very clever use of Linq with this line filters.Select(filter => MakePredicate(item, filter)).Aggregate(Expression.AndAlso); +1 :) – Allaallah

@Ivan Thanks for clarification. If you look at the generated script by EF (using SQL profiler) from your code and my what would you see is no difference. What I need is a "SELECT" statement with One "Where" Clause and "a few number of condition" respective to predicates that I passed into repository. Please refer to the SQL script in the question generated by EF and captured by (using SQL profiler). – Lining

@DeveloperX First off, the question is about predicates, i.e. where, not for select. And remember, we don't have your environment and can't see what you see. I don't have your repository, neither entity, nor criteria you use for testing. May be you should post a small verifiable sample. – Manly

@Allaallah Thank you! Aggregate is our friend. If you like this, you may find also this similar trick interesting:) – Manly

@DeveloperX predicate builder that you wrote in the question is correct. @Ivan gave you a optimized snippet of the same. The reason you're getting Where 1=0 in your Trace might because of the Mapper you mentioned. I tried it at my end and it's giving correct where condition. – Allaallah

@IvanStoev Read the question again! probably you overlooked this "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." – Lining

@Allaallah I am passing more than 12 conditions as predicate but the where clause in SQL statement still is only Where 1=0 May I know what you get in your Trace? – Lining

Checkout the output here gist.github.com/vendettamit/d1ca4d501d4a43423bd9 Which version of EF you're using? – Allaallah

@Allaallah I'm using EF 6.0 and .Net Framework 4.5. I cannot comprehend why it's not functioning probably . . . thanks anyway. I will try to find the root cause again! – Lining

@DeveloperX Hi. In case you haven't demystified it yet :) Recently I found that EF generates a fake SQL query like the one you see when the Where clause is a constant false - see Entity Framework mysterious error. Check the conditions that you pass to the function and make sure none of them is evaluating constantly to false (which will cause the whole filter to be a constant false because of the And combinator). – Manly

@IvanStoev that's a great hint. I will work on it. Thanks again. – Lining