How to build a lambda expression for a nested ICollection, that can be successfully translated to SQL?

Question

I am trying to build a lambda expression that will do an "ILIKE" search on my models, based on various client-side parameters such as field name. I have something that works pretty well for properties that are not nested. The problem arises when I want to search on a nested ICollection property. The minimum model is at the bottom of the question.

---

What works

Let's say the client sends that he wants to search for

f = {
        "filterdatafield": "name",
        "filtervalue": "test"
    }

Then this code will build the required expression:

string MyType="Field";  
   ParameterExpression p=null;
#nullable enable
    Type? x = Type.GetType(MyType);
    if (x is null)
    {
        throw new Exception("Cannot find type " + MyType);
    }
#nullable disable
    p = Expression.Parameter(x);
    Expression property = Expression.Property(p, f.filterdatafield);
    var functions = Expression.Property(null, typeof(EF).GetProperty(nameof(EF.Functions)));
    var likeFunction = typeof(NpgsqlDbFunctionsExtensions).GetMethod(nameof(NpgsqlDbFunctionsExtensions.ILike), new Type[] { functions.Type, typeof(string), typeof(string) });
    var pattern = Expression.Constant($"%{f.filtervalue}%", typeof(string));
    MethodCallExpression call = Expression.Call(likeFunction,
                                        Expression.Property(null, typeof(EF), nameof(EF.Functions)), property, pattern);
    Expression exp = Expression.Lambda(call, p);
    return exp;

What the problem is

OK. Now, let's say that instead he wanted to search for

f = {
        "filterdatafield": "fieldoperators",
        "filtervalue": "test"
    }

The assumption is that he meant to search in the name field of the operators. That's a nested property. How to get the ILIKE lambda for that?

What I've tried

string MyType="Field";  
   ParameterExpression p=null;
#nullable enable
    Type? x = Type.GetType(MyType);
    if (x is null)
    {
        throw new Exception("Cannot find type " + MyType);
    }
#nullable disable
    p = Expression.Parameter(x);
    Expression property = Expression.Property(p, f.filterdatafield);
    var functions = Expression.Property(null, typeof(EF).GetProperty(nameof(EF.Functions)));
    var likeFunction = typeof(NpgsqlDbFunctionsExtensions).GetMethod(nameof(NpgsqlDbFunctionsExtensions.ILike), new Type[] { functions.Type, typeof(string), typeof(string) });
    var pattern = Expression.Constant($"%{f.filtervalue}%", typeof(string));
    if (property.Type == typeof(ICollection<FieldOperator>)) {
        var fieldParam = Expression.Parameter(typeof(Field),"f");
        var operatorsParam = Expression.Parameter(typeof(FieldOperator), "myops");
    
        var lvl1 = Expression.Property(operatorsParam, "Operator");
        var lvl2 = Expression.Property(lvl1, "Name");
        var compareExpression = Expression.Call(likeFunction,
            Expression.Property(null, typeof(EF), nameof(EF.Functions)), lvl2, pattern);
    
        var lambdaForTheAnyCallPredicate = Expression.Lambda<Func<FieldOperator,Boolean>>(compareExpression, operatorsParam);
        
        var collectionProperty = Expression.Property(fieldParam, "FieldOperators");
    
        var resultExpression = ExpressionExtensions.CallAny(collectionProperty, lambdaForTheAnyCallPredicate);
        Expression exp  = Expression.Lambda<Func<Field, Boolean>>(resultExpression, p);
        return exp;
    }

The ExpressionExtensions.CallAny method is from this answer This does generate a seemingly valid expression, however it fails when trying to be translated to SQL by the Entity Framework:

The LINQ expression 'DbSet<Field>()
    .Where(f => (IEnumerable<FieldOperator>)f.FieldOperators
        .Any(myops => __Functions_0
            .ILike(
                matchExpression: myops.Operator.Name, 
                pattern: "%test%")))' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

---

Model

public partial class Field
{
    public Field()
    {
        FieldOperators = new HashSet<FieldOperator>();
    }

    public long FieldId { get; set; }
    public string Name { get; set; }
    //various other properties
    public virtual ICollection<FieldOperator> FieldOperators { get; set; }
}

public partial class FieldOperator
{
    public long FieldId { get; set; }
    public long OperatorId { get; set; }

    public virtual Field Field { get; set; }
    public virtual Oem Operator { get; set; }
}

public partial class Oem
{
    public long OemId { get; set; }
    public string Name { get; set; }
    //other properties omitted
}

Answer 1

Not sure what is

ParameterExpression p = typeof(Field);

in both places. It doesn't compile, so (according to the output) assuming it is

ParameterExpression p = Expression.Parameter(typeof(Field), "f");

And here is the problem. In the code in question you have

// (1)
ParameterExpression p = Expression.Parameter(typeof(Field), "f");
// ...
// (2)
var fieldParam = Expression.Parameter(typeof(Field), "f");
// ...
// (3)
var collectionProperty = Expression.Property(fieldParam, "FieldOperators");
// ...
// (4)
Expression exp = Expression.Lambda<Func<Field, Boolean>>(resultExpression, p);

in (2) you are creating a new parameter with the same name and type as (1), which you are using in (3) as part of the body of the lambda (4). However you are passing (1) as lambda parameter in (4) while the body uses (2).

And that's the problem. Parameter expressions are identified by instance, not by name. So although the expression looks fine, it isn't - if you try to Compile() it to delegate, you'd get a runtime exception. Similar is when EF Core trying to translate it.

It is a common mistake when manipulating expression trees with code. Because C# does not allow having parameters with the same name inside one and the same scope. But Expression API does not care about ParameterExpression names - as you can see, they are optional argument of Expression.Parameter.

With that being said, simply replace (2) with

var fieldParam = p;

or remove it and just use p in place of fieldParam, and the problem will be solved.

Also the collectionProperty variable is redundant since it is the same as property variable. So the final code should be something like this:

var parameter = Expression.Parameter(typeof(Field), "f");
var property = Expression.Property(parameter, f.filterdatafield);
var functions = Expression.Property(null, typeof(EF).GetProperty(nameof(EF.Functions)));
var likeFunction = typeof(NpgsqlDbFunctionsExtensions).GetMethod(nameof(NpgsqlDbFunctionsExtensions.ILike), new Type[] { functions.Type, typeof(string), typeof(string) });
var pattern = Expression.Constant($"%{f.filtervalue}%", typeof(string));
if (property.Type == typeof(ICollection<FieldOperator>))
{
    var operatorsParam = Expression.Parameter(typeof(FieldOperator), "myops");

    var lvl1 = Expression.Property(operatorsParam, "Operator");
    var lvl2 = Expression.Property(lvl1, "Name");
    var compareExpression = Expression.Call(likeFunction,
        Expression.Property(null, typeof(EF), nameof(EF.Functions)), lvl2, pattern);

    var anyPredicate = Expression.Lambda<Func<FieldOperator, bool>>(compareExpression, operatorsParam);

    var body = Expression.Call(typeof(Enumerable),
        nameof(Enumerable.Any), new[] { operatorsParam.Type },
        property, anyPredicate);

    var predicate = Expression.Lambda<Func<Field, bool>>(body, parameter);
    return predicate;
}