Use LinqKit PredicateBuilder for related model (EF Core)

Question

I want to use LinqKit's PredicateBuilder and pass the predicate into .Any method for related model.

So I want to build a predicate:

var castCondition = PredicateBuilder.New<CastInfo>(true);

if (movies != null && movies.Length > 0)
{
    castCondition = castCondition.And(c => movies.Contains(c.MovieId));
}
if (roleType > 0)
{
    castCondition = castCondition.And(c => c.RoleId == roleType);
}

And then use it to filter model that has relation to model in predicate:

IQueryable<Name> result = _context.Name.AsExpandable().Where(n => n.CastInfo.Any(castCondition));
return await result.OrderBy(n => n.Name1).Take(25).ToListAsync();

But this causes a System.NotSupportedException: Could not parse expression 'n.CastInfo.Any(Convert(__castCondition_0, Func``2))': The given arguments did not match the expected arguments: Object of type 'System.Linq.Expressions.UnaryExpression' cannot be converted to type 'System.Linq.Expressions.LambdaExpression'.

I saw similar question and answer there suggests to use .Compile. Or one more question that build an extra predicate.

So I tried to use extra predicate

var tp = PredicateBuilder.New<Name>(true);
tp = tp.And(n => n.CastInfo.Any(castCondition.Compile()));
IQueryable<Name> result = _context.Name.AsExpandable().Where(tp);

Or use compile directly

IQueryable<Name> result = _context.Name.AsExpandable().Where(n => n.CastInfo.Any(castCondition.Compile()));

But I have an error about Compile: System.NotSupportedException: Could not parse expression 'n.CastInfo.Any(__Compile_0)'

So is it possible to convert the result from PredicateBuilder to pass into Any?

Note: I was able to build the desired behavior combining expressions, but I don't like that I need extra variables.

System.Linq.Expressions.Expression<Func<CastInfo,bool>> castExpression = (c => true);
if (movies != null && movies.Length > 0)
{
    castExpression = (c => movies.Contains(c.MovieId));
}
if (roleType > 0)
{
    var existingExpression = castExpression;
    castExpression = c => existingExpression.Invoke(c) && c.RoleId == roleType;
}
IQueryable<Name> result = _context.Name.AsExpandable().Where(n => n.CastInfo.Any(castExpression.Compile()));
return await result.OrderBy(n => n.Name1).Take(25).ToListAsync();

So I assume I just miss something about builder.

Update about versions: I use dotnet core 2.0 and LinqKit.Microsoft.EntityFrameworkCore 1.1.10

Undecagon

Answer 1

Looking at the code, one will assume that the type of castCondition variable is Expression<Func<CastInfo, bool>> (as it was in earlier versions of PredicateBuilder).

But if that was the case, then n.CastInfo.Any(castCondition) should not even compile (assuming CastInfo is a collection navigation property, so the compiler will hit Enumerable.Any which expects Func<CastInfo, bool>, not Expression<Func<CastInfo, bool>>). So what's going on here?

In my opinion, this is a good example of C# implicit operator abuse. The PredicateBuilder.New<T> method actually returns a class called ExpressionStarter<T>, which has many methods emulating Expression, but more importantly, has implicit conversion to Expression<Func<T, bool>> and Func<CastInfo, bool>. The later allows that class to be used for top level Enumerable / Queryable methods as replacement of the respective lambda func/expression. However, it also prevents the compile time error when used inside the expression tree as in your case - the complier emits something like n.CastInfo.Any((Func<CastInfo, bool>)castCondition) which of course causes exception at runtime.

The whole idea of LinqKit AsExpandable method is to allow "invoking" expressions via custom Invoke extension method, which then is "expanded" in the expression tree. So back at the beginning, if the variable type was Expression<Func<CastInfo, bool>>, the intended usage is:

_context.Name.AsExpandable().Where(n => n.CastInfo.Any(c => castCondition.Invoke(c)));

But now this doesn't compile because of the reason explained earlier. So you have to convert it first to Expression<Func<T, bool> outside of the query:

Expression<Func<CastInfo, bool>> castPredicate = castCondition;

and then use

_context.Name.AsExpandable().Where(n => n.CastInfo.Any(c => castPredicate.Invoke(c)));

or

_context.Name.AsExpandable().Where(n => n.CastInfo.Any(castPredicate.Compile()));

To let compiler infer the expression type, I would create a custom extension method like this:

using System;
using System.Linq.Expressions;

namespace LinqKit
{
    public static class Extensions
    {
        public static Expression<Func<T, bool>> ToExpression<T>(this ExpressionStarter<T> expr) => expr;
    }
}

and then simply use

var castPredicate = castCondition.ToExpression();

It still has to be done outside of the query, i.e. the following does not work:

_context.Name.AsExpandable().Where(n => n.CastInfo.Any(c => castCondition.ToExpression().Invoke(c)));

Norvall

Thanks for detailed explanation. I tried your code, and unfortunately I have a warning The LINQ expression 'where __castCondition_0.Invoke([c])' could not be translated and will be evaluated locally. So while it compiles and runs, the condition is not added to SQL and query selects all rows. Do you have any suggestion? – Undecagon

Actually you mention that ExpressionStarter can be caster to Expression. So this worked var castExpr = (Expression<Func<CastInfo,bool>>)castCondition; context.Name.AsExpandable().Where(n => n.CastInfo.Any(castExpr.Compile())).Count(); Inlining the variable does not work for some reason – Undecagon

Inlining does not work because the "expander" does not recognize ExpressionStarter.Compile(). Sounds like incomplete LinqKit job :( – Norvall

Ahh, this is the type of Q&A that keeps me here! (I sometimes feel like quitting). – Callipygian

The advise above worked for me too. I got another error that hinted that the combination with async/await was not possible. After applying above tips and removing await and To...Async() all worked fine. Thanks! – Recapture

Answer 2

It may not be exactly related to the original question, but considering the following model :

public Class Music
{
    public int Id { get; set; }
    public List<Genre> Genres { get; set; }
}
public Class Genre
{
    public int Id { get; set; }
    public string Title { get; set; }
}

List<string> genresToFind = new() {"Pop", "Rap", "Classical"};

If you are trying to find all Musics that their genres exist in genresToFind list, here's what you can do:

Create PredicateBuilder expressions chain on Genre model :

var pre = PredicateBuilder.New<Genre>();
foreach (var genre in genresToFind)
{
    pre = pre.Or(g => g.Title.Contains(genre));
}

Then execute your query like this :

var result = await _db.Musics.AsExpandable()
    .Where(m => m.Genres
        .Any(g => pre.ToExpression().Invoke(g)))
    .ToListAsync();

ToExpression() is a generic extension method that we've created to convert ExpressionStarter<Genre> type to Expression<Func<Genre, bool>> :

public static class ExpressionExtensions
{
    public static Expression<Func<T, bool>> ToExpression<T> (this 
        ExpressionStarter<T> exp) => exp;
}

Also, you'll need LinqKit.Microsoft.EntityFrameworkCore package.

Neutralism