EF Core: filter condition for nested collection (Func<>) as a variable-CodePudding

tl;dr; I .Net6 I want to pass to dbcontext.Entities.Include(host => host.nestedeCollection.Where(element => )) func element => via a variable Func<ElementType, bool>. It works with Z.Entityframework.Extensions, but cannot convert it to EF Core.

Long story. Consider the following model:

public class Attendance {
    public Guid Id { get; set; }
    public Guid StudentId { get; set; }
    public string Subject { get; set; }
    public decimal Rank { get; set; }
}

public class Student {
    public Guid Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Attendance> Attendances { get; set; }
}

public class UniversityDbContext : DbContext {
    public DbSet<Attendance> Attendances { get; set; }
    public DbSet<Student> Students { get; set; }
...
    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity<Student>()
            .HasMany(student => student.Attendances)
            .WithOne()
            .HasForeignKey(x => x.StudentId)
        ;
    }
}

I want to:

var students = db
    .Students
    .Include(student => student.Attendances.Where(att => att.Rank < 3.0m))
    .ToList()
;

At the moment I am using Z.EntityFramework.Extensions .IncludeOptimized. Since Net5 there is an EF-native feature to perform the same task, and the code above just works fine for scenarios like above {{Where(att => att.Rank < 3.0m)}} . In real life I have quite a complex condition for .Where clause and I have to repeat it within multiple .Include and .ThenInclude statements when loading my data, and unfortunately global filters do not help in this case unless I totally refactor the app. What I did for Z.EntityFramework.Extensions is:

Func<Attendance, bool> func = attendance => attendance.Rank < 3.0m;
var students = db
    .Students
    .IncludeOptimized(student => student.Attendances.Where(func))
    .ToList()
;

It worked.

Now, I try the same with NET6 and latest available EF Core. All attempts fail:

        Func<Attendance, bool> func_x = attendance => true;
        Func<Attendance, bool> func_y = attendance => attendance.Rank < 3.0m;
        Expression<Func<Attendance, bool>> expr = att => att.Rank < 3.0m; 

        //*
        using (var db = new UniversityDbContext(connString)) {
            var students_x = db
                .Students
                .Include(student => student.Attendances.Where(func_x))
                .ToList()
            ;
        }

        //**
        //using (var db = new UniversityDbContext(connString)) {
        //    var students_z = db
        //        .Students
        //        .Include(student => student.Attendances.Where(x => func_x(x)))
        //        .ToList()
        //    ;
        //}
        
        //***
        //using (var db = new UniversityDbContext(connString)) {
        //    var students_y = db
        //        .Students
        //        .Include(student => student.Attendances.Where(func_y))
        //        .ToList()
        //    ;
        //}

        //****
        //using (var db = new UniversityDbContext(connString)) {
        //    var students_z = db
        //        .Students
        //        .Include(student => student.Attendances.Where(Func_z))
        //        .ToList()
        //    ;
        //}
        
        //*****
        //using (var db = new UniversityDbContext(connString)) {
        //    var students_expr = db
        //        .Students
        //        .Include(student => student.Attendances.Where(expr))
        //        .ToList()
        //    ;
        //}

//* fails with

System.ArgumentException: Expression of type 'System.Func`2[FuncInEFIncludeExample.Attendance,System.Boolean]' cannot be used for parameter of type 'System.Linq.Expressions.Expression`1[System.Func`2[FuncInEFIncludeExample.Attendance,System.Boolean]]' of method 'System.Linq.IQueryable`1[FuncInEFIncludeExample.Attendance] Where[Attendance](System.Linq.IQueryable`1[FuncInEFIncludeExample.Attendance], System.Linq.Expressions.Expression`1[System.Func`2[FuncInEFIncludeExample.Attendance,System.Boolean]])' (Parameter 'arg1')
   at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
   at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, Expression arg0, Expression arg1)
   at System.Linq.Expressions.Expression.Call(Expression instance, MethodInfo method, IEnumerable`1 arguments)
   at System.Linq.Expressions.Expression.Call(MethodInfo method, IEnumerable`1 arguments)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.TryConvertEnumerableToQueryable(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression`1 node)
   at System.Linq.Expressions.Expression`1.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)
   at System.Linq.Expressions.UnaryExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at System.Dynamic.Utils.ExpressionVisitorUtils.VisitArguments(ExpressionVisitor visitor, IArgumentProvider nodes)
   at System.Linq.Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryableMethodNormalizingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.NormalizeQueryableMethod(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPreprocessor.NormalizeQueryableMethod(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.IncludableQueryable`2.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at FuncInEFIncludeExample.Program.Main() in W:\Projects\FuncInEFInclude\FuncInEFIncludeExample\Program.cs:line 81

//** fails with

System.InvalidOperationException: The LINQ expression 'DbSet<Attendance>()
    .Where(a => EF.Property<Guid?>(EntityShaperExpression: 
        FuncInEFIncludeExample.Student
        ValueBufferExpression: 
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    , "Id") != null && object.Equals(
        objA: (object)EF.Property<Guid?>(EntityShaperExpression: 
            FuncInEFIncludeExample.Student
            ValueBufferExpression: 
                ProjectionBindingExpression: EmptyProjectionMember
            IsNullable: False
        , "Id"), 
        objB: (object)EF.Property<Guid?>(a, "StudentId")))
    .Where(a => Invoke(__func_x_0, a)
    )' 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.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.IncludeExpression.VisitChildren(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.VisitExtension(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.IncludableQueryable`2.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at FuncInEFIncludeExample.Program.Main() in W:\Projects\FuncInEFInclude\FuncInEFIncludeExample\Program.cs:line 91

The rest are failing as //*, and the last one, //*****, as expected, does not even compile.

I tried further to play around with nested collections, making them IQueryable<>. Could not achieve what I want. I am especially confused by

System.ArgumentException: Expression of type 'System.Func`2[FuncInEFIncludeExample.Attendance,System.Boolean]' cannot be used for parameter of type 'System.Linq.Expressions.Expression`1[System.Func`2[FuncInEFIncludeExample.Attendance,System.Boolean]]' of method 'System.Linq.IQueryable`1[FuncInEFIncludeExample.Attendance]

Is there an invalid cast in the middle of Visitor pattern implementation (looking at stacktrace)?

Hence is my question. Has anybody tried to achieve the same in EF Core? May be any suggestions?

Thanks!

CodePudding user response：

If you want query translation, you have to deal only with Expression, just Func is not tranaslateble to the SQL. Good news, that you do not need Z.EntityFramework, bad news that EF Core still will not translate your query.

You neeed ligthweigh library LINQKit. It needs just configuring DbContextOptions:

builder
    .UseSqlServer(connectionString)
    .WithExpressionExpanding(); // enabling LINQKit extension

Then you can use your expression via Invoke extension:

Expression<Func<Attendance, bool>> func_x = attendance => true;
Expression<Func<Attendance, bool>> func_y = attendance => attendance.Rank < 3.0m;
Expression<Func<Attendance, bool>> expr = att => att.Rank < 3.0m; 

using (var db = new UniversityDbContext(connString)) 
{
    var students_x = db
        .Students
        .Include(student => student.Attendances.Where(x => func_x.Invoke(x)))
        .ToList();
}

CodePudding user response：

As suggested, LINQKit is the answer so far. Thanks.