Home > Software engineering >  The LINQ expression 'p' could not be translated when building custom expression
The LINQ expression 'p' could not be translated when building custom expression

Time:12-16

I have a simple Order class

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

And I have some dynamically made filters that should be converted to Expressions and queried against a database.

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

Now, let's say this is an entry point and a place where filters are defined (and where the response should be evaluated).

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

I pass filters to some method in repository...

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

And finally, I have a PredicateBuilder that is responsible to generate predicate based on specified filters...

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

But when I run this code I get:

System.InvalidOperationException: 'The LINQ expression 'p' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or '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?

CodePudding user response:

Parameter that you are creating via var parameterReplacerVisitor = new ParameterReplacerVisitor(entity); and the ones in your expressions in PropertySelector are different despite having the same names. You need to replace incoming with the created one. For example:

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);
        ....
     }
}
  • Related