为实体框架创建动态表达式

我创建了一个通用表达式生成器，它根据条件集合构建谓词。我将谓词传递给存储库中的通用方法。我认为表达式生成器工作正常并创建所需的谓词，尽管实体框架生成的 SQL 脚本不符合我的预期。我读过很多关于动态查询或 LinqKit 和表达式生成器的问题和文章，最相关的是这条评论。如果您能检查我所做的事情并让我知道我是否犯了任何错误，我真的很感激？

这是 ExpressionBuilder 类的代码：

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

这是通用存储库方法：

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

并由 Entity Framework for Sql 生成脚本（我期望带有一些 where 子句的选择查询）：

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

我在用

• 实体框架6.0
• .Net框架4.6
• ASP.NET MVC 5

Update表达模型：

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

另一个缺失的部分是一个通用映射器，它将给定的搜索条件映射到一个新的 ExpressionModel，我认为它与这个问题无关。

正如我在评论中提到的，实现过于复杂。

一、这个方法

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

检查过滤器计数、删除已处理项目等的整个逻辑是多余的。AND条件可以很容易地像这样链接起来

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

所以只需删除该功能即可。

二、这个功能

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

命名不好，不需要通用的T因为里面没有使用。

相反，将签名更改为

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

最后，公共方法很简单：

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

附：并且不要忘记做null检查使用情况：

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