EF核心:作为变量的嵌套集合(Func<>)的筛选条件

Question

I .Net6我想通过变量Func传递给dbcontext.Entities.Include(主机=> host.nestedeCollection.Where(element => )) func元素=>。它与Z.Entityframework.Extensions一起工作，但不能将其转换为EF。

说来话长。考虑以下模式：

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

我想：

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

目前我正在使用Z.EntityFramework.Extensions .IncludeOptimized。因为Net5有一个EF-本机特性来执行相同的任务，上面的代码对于{{Where(att => att.Rank < 3.0m)}}这样的场景很好。在现实生活中，我对于.Where子句有一个相当复杂的条件，在加载数据时，我必须在多个.Include和.ThenInclude语句中重复它，不幸的是，全局过滤器在这种情况下没有帮助，除非我完全重构了应用程序。我为Z.EntityFramework.Extensions做的是：

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

啊，真灵。

现在，我也尝试在NET6和最新可用的EF核心。所有尝试都失败了：

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

//*失败

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

//**

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

其余的都是失败的//*，而最后一个，//*，正如预期的那样，甚至没有编译。

我进一步尝试使用嵌套集合，使它们成为IQueryable<>。无法实现我想要的。我特别困惑于

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]

访问者模式实现中是否存在无效的强制转换(查看堆栈跟踪)？

因此，我的问题是。有没有人试图在EF核心实现同样的目标？可能有什么建议吗？

谢谢!

Answer 1

如果您想要查询转换，您必须只处理Expression，只有Func不能被转换到SQL。好消息是，您不需要Z.EntityFramework，坏消息是EF核心仍然不会翻译您的查询。

你需要的是LINQKit图书馆。它只需要配置DbContextOptions：

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

然后可以通过Invoke扩展使用表达式：

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

Answer 2

正如所建议的那样，LINQKit是目前为止的答案。谢谢。