構建自定義運算式時無法翻譯LINQ運算式“p”

我有一個簡單的 Order 類

public abstract class Entity
{
   public int Id { get; set; }
}

public class Order : Entity
{
    public string Description { get; set; }
    public string DeliveryAddress { get; set; }
    public decimal Price { get; set; }
    public int Priority { get; set; }
}

我有一些動態制作的過濾器，它們應該轉換為運算式并針對資料庫進行查詢。

public class Filter<T>
    where T : Entity
{
    public Expression PropertySelector { get; set; }
    public Operator Operator { get; set; }
    public dynamic FilteringValue { get; set; }
}

public enum Operator
{
    Equal = 0,
    GreaterThan = 1,
}

現在，假設這是一個入口點和一個定義過濾器的地方（以及應該評估回應的地方）。

public IEnumerable<Order> GetByFilter()
{
    var filters = new List<Filter<Order>>()
        {
            new()
            {
                PropertySelector = ((Expression<Func<Order, int>>) (p => p.Priority)).Body,
                Operator = Operator.GreaterThan,
                FilteringValue = 1,
            },
            new()
            {
                PropertySelector = ((Expression<Func<Order, string>>) (p => p.Description)).Body,
                Operator = Operator.Equal,
                FilteringValue = "Laptop",
            }
        }

    IQueryable<Order> queryableOrders = _orderRepository.QueryAll();
    queryableOrders = _orderRepository.QueryByCustomerFilter(queryableOrders, filters);

    return queryableOrders.AsEnumerable();
}

我將過濾器傳遞給存盤庫中的某個方法...

public IQueryable<T> QueryByCustomerFilter(IQueryable<T> source, List<Filter<T>> filters)
{
    var entityType = source.ElementType;
    var predicate = PredicateBuilder.GetFilterPredicate(entityType, filters);

    return source.Where(predicate);
}

最后，我有一個 PredicateBuilder 負責根據指定的過濾器生成謂詞......

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> GetFilterPredicate<T>(Type entityType, List<Filter<T>> filters)
        where T : Entity
    {
        var entity = Expression.Parameter(entityType, "p");
        var buildingBlocks = new List<Expression>();

        foreach (var filter in filters)
        {
            var left = filter.PropertySelector;
            var right = Expression.Constant(filter.FilteringValue);

            var buildingBlockExpression = filter.Operator switch
            {
                Operator.Equal => Expression.Equal(left, right),
                Operator.GreaterThan => Expression.GreaterThan(left, right),
                _ => throw new ArgumentOutOfRangeException(nameof(filter.Operator)),
            };

            buildingBlocks.Add(buildingBlockExpression);
        }

        var customFilterExpression = buildingBlocks.Aggregate(Expression.AndAlso);
        return Expression.Lambda<Func<T, bool>>(customFilterExpression, entity);
    }
}

但是當我運行這段代碼時，我得到：

System.InvalidOperationException : '無法翻譯 LINQ 運算式'p'。以可翻譯的形式重寫查詢，或通過插入對“AsEnumerable”、“AsAsyncEnumerable”、“ToList”或“ToListAsync”的呼叫，顯式切換到客戶端評估。

However, here is what confuses me. If I put the next line of the code in QueryByCustomerFilter method, I can clearly see that there is no difference between the expression written for that line of the code and the expression that is generated by the code based on filters.

var testQueryable = Context.Orders.Where(p => p.Priority > 1 && p.Description == "Laptop").AsQueryable();

SQL query for both expressions is identical and I can't see any difference.

"SELECT [o].[Id], [o].[DeliveryAddress], [o].[Description], [o].[Price], [o].[Priority]\r\nFROM [Orders] AS [o]\r\nWHERE ([o].[Priority] > 1) AND ([o].[Description] = N'Laptop')"

Finally, the most confusing part is that if I do

testQueryable.ToList()

before the original query is evaluated everything will work as expected. So, BOTH of the expressions are successfully translated and I am able to get expected results.

So, what's happening here? Why the original expression can't be translated and how are two queryables from the example connected between each other?

uj5u.com熱心網友回復：

盡管名稱相同，但var parameterReplacerVisitor = new ParameterReplacerVisitor(entity);您通過創建的引數和運算式中PropertySelector的引數不同。您需要用創建的替換傳入。例如：

class ParameterReplacerVisitor : ExpressionVisitor
{
    private readonly ParameterExpression _param;

    public ParameterReplacerVisitor(ParameterExpression expression)
    {
        _param = expression;
    }

    protected override Expression VisitParameter(ParameterExpression node) => _param;
}

public static Expression<Func<T, bool>> GetFilterPredicate<T>(Type entityType, List<Filter<T>> filters)
    where T : Entity
{
    var entity = Expression.Parameter(entityType, "p");
    var buildingBlocks = new List<Expression>();

    foreach (var filter in filters)
    { 
        var parameterReplacerVisitor = new ParameterReplacerVisitor(entity);
        
        var left = filter.PropertySelector;
        left = parameterReplacerVisitor.Visit(left);
        ....
     }
}

標籤：c# sql-server entity-framework 实体框架核心 可查询的

上一篇：LINQ日期格式不允許按格式化日期YearMonth分組

下一篇：從電話號碼中洗掉尾隨零