Linq Lambda Expression could not be translated

Question

I have the following model

public class Entity
{
  public List<FirstLevel> FirstLevel { get; set; }
}

public class FirstLevel
{
  public int ElementId { get; set; }
}

I am trying to fetch every Entities that have a FirstLevel element with a specific ElementId using Linq ExpressionTree. The idea is for the expression to be translated as SQL only request because I don't want to load every Entities client-side to then do the filtering.

I currently have that Expressions

var parameter = Expression.Parameter(typeof(Entity), "e");
var anyPredicate = Expression.Lambda<Func<FirstLevel, bool>>(
    Expression.Equal(Expression.Property(Expression.Parameter(typeof(FirstLevel), "fl"), "ElementId"), Expression.Constant(3)),
    Expression.Parameter(typeof(FirstLevel), "fl"));
var anyMethod = typeof(Enumerable).GetMethods().Where(m => m.Name == "Any" && m.GetParameters().Length == 2).Single().MakeGenericMethod(typeof(FirstLevel));
var wherePredicate = Expression.Lambda<Func<Entity, bool>>(
    Expression.Call(anyMethod, Expression.Property(parameter, "firstLevel"), anyPredicate),
    parameter);
var result = context.Entities.Where(wherePredicate);

But when it executes I get the following error:

The LINQ expression 'fl' could not be translated

In the debugger I can see the created expression which is

{e => e.FirstLevel.Any(fl => (fl.ElementId == 1))}

I am surprised that lambda expression are not supported. Is there an other way to filter on a list using expressions? Am I missing something?

I am using Entity Framework, .Net Core 6, SQL Server 2016.

Note: The use of ExpressionTree is not mandatory but I figured I would get a translatable expression more easily.

Answer 1

When translating an Expression Tree, parameters are compared only by reference. In your case, parameters with the same name and type are treated as different. To fix this issue, you should correct your code to reuse the same parameter instance.

var parameter = Expression.Parameter(typeof(Entity), "e");
var flParam = Expression.Parameter(typeof(FirstLevel), "fl");
var anyPredicate = Expression.Lambda<Func<FirstLevel, bool>>(
    Expression.Equal(Expression.Property(flParam, "ElementId"), Expression.Constant(3)),
    flParam);
// ...