EFCore：嵌套集合(Func<>)作為變數的過濾條件-有解無憂

tl;博士; 我 .Net6 我想通過一個變數 Func<ElementType, bool> 傳遞給 dbcontext.Entities.Include(host => host.nestedeCollection.Where(element => )) func element =>。它適用于 Z.Entityframework.Extensions，但無法將其轉換為 EF Core。

很長的故事。考慮以下模型：

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 Core 進行了相同的嘗試。所有嘗試都失敗：

        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

其余的都失敗了，因為 //*，最后一個，//*****，正如預期的那樣，甚至無法編譯。

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!

uj5u.com熱心網友回復：

如果你想要查詢翻譯，你只需要處理Expression，只是Func不能翻譯到 SQL。好訊息是您不需要Z.EntityFramework，壞訊息是 EF Core 仍然不會翻譯您的查詢。

您需要 ligthweigh 庫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();
}

uj5u.com熱心網友回復：

正如所建議的，到目前為止，LINQKit 是答案。謝謝。

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/383962.html

標籤：.net entity-framework linq .net-core entity-framework-core

上一篇：如何將在服務層的事務中分兩步執行的兩個資料庫操作包裝起來？

下一篇：你能插入一個表，其中一個值是mysql中的宣告變數嗎？[復制]