Creating dynamic expression for entity framework

c# dynamic-linq entity-framework expression-trees

Question

A generic expression builder I developed creates a predicate based on a set of circumstances. I provide the predicate to a repository generic method. Even if the SQL script produced by Entity Framework does not match my expectations, I believe that expression builder functions well and provides the appropriate predicate. The most pertinent question and article I found relating this issue's dynamic query, LinqKit, and expression builder was this statement. I would much appreciate it if you could review my work and let me know if there were any mistakes.

The ExpressionBuilder class code is provided below:

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
    }
}

The general repository technique is as follows:

    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 the Entity Framework for SQL produced script (I anticipate a select query with a few where clauses)

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 use

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

Model for expression: Update

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

Another component that is lacking is a generic mapper, which, in my opinion, is not pertinent to this issue since it just translates a set of search parameters to a new ExpressionModel.

1
4
1/26/2018 10:24:52 PM

Popular Answer

The implementation is excessively difficult, as I noted in the comments.

Firstly, this approach

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

Additionally, the whole logic behind counting filters, eliminating processed items, etc. is unnecessary.AND These criteria are simple to link together.

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

So just disable that feature.

Additionally, this feature

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

is misnamed and does not need a generic nameT as it is not used inside.

Change the signature instead to

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

The public approach is as straightforward 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. : Also, remember tonull Verify 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;
    }
}
4
2/11/2016 7:23:31 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow