使用linq2db按嵌套集合过滤时抛出异常的问题求助

环境 #

• .NET Core 3.1
• Microsoft.EntityFrameworkCore 3.1.9
• Npgsql 4.1.9
• linq2db.EntityFrameworkCore 3.7.0

实体类 #

[Table("testrunconfig", Schema = "fit")]
public partial class Testrunconfig
{
    public Testrunconfig()
    {
        Testruntestgrp = new HashSet<Testruntestgrp>();
    }
    
    [Key]
    [Column("id")]
    public int Id { get; set; }
    
    [InverseProperty("Testrunconfig")]
    public virtual ICollection<Testruntestgrp> Testruntestgrp { get; set; }
}

[Table("testruntestgrp", Schema = "fit")]
public partial class Testruntestgrp
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [ForeignKey(nameof(TestrunconfigId))]
    [InverseProperty("Testruntestgrp")]
    public virtual Testrunconfig Testrunconfig { get; set; }
}

查询代码 #

var testName = "MyTestName"
var test = _fitDbContext.Testrunconfig
    .Include(x => x.Testruntestgrp)
    .Where(x => x.Testruntestgrp.Select(y => y.Testname).Contains("MyTestName"))
    .Take(10)
    .ToListAsyncLinqToDB()
    .Result;

异常信息 #

Exception thrown: 'LinqToDB.Linq.LinqException' in linq2db.dll
Exception thrown: 'LinqToDB.Linq.LinqException' in linq2db.dll
Exception thrown: 'System.AggregateException' in System.Private.CoreLib.dll
...（省略重复异常抛出信息）
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware: Error: An unhandled exception has occurred while executing the request.

System.AggregateException: One or more errors occurred. (One or more errors occurred. ('x.Testruntestgrp.Select(y => y.Testname).Contains(value(MyProject.Service.TestExecutionsService+<>c__DisplayClass3_0).testName)' cannot be converted to SQL.))
 ---> System.AggregateException: One or more errors occurred. ('x.Testruntestgrp.Select(y => y.Testname).Contains(value(MyProject.Service.TestExecutionsService+<>c__DisplayClass3_0).testName)' cannot be converted to SQL.)
 ---> LinqToDB.Linq.LinqException: 'x.Testruntestgrp.Select(y => y.Testname).Contains(value(MyProject.Service.TestExecutionsService+<>c__DisplayClass3_0).testName)' cannot be converted to SQL.
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertInPredicate(IBuildContext context, MethodCallExpression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertPredicate(IBuildContext context, Expression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions, Boolean isNotExpression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildWhere(IBuildContext parent, IBuildContext sequence, LambdaExpression condition, Boolean checkForSubQuery, Boolean enforceHaving)
   at LinqToDB.Linq.Builder.WhereBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.TakeSkipBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]()
   at LinqToDB.Linq.Query`1.CreateQuery(IDataContext dataContext, Expression expr)
   at LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext, Expression& expr)
   at LinqToDB.Linq.ExpressionQuery`1.GetQuery(Expression& expression, Boolean cache)
   at LinqToDB.Linq.ExpressionQuery`1.LinqToDB.Async.IQueryProviderAsync.ExecuteAsyncEnumerable[TResult](Expression expression, CancellationToken cancellationToken)
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
   at System.Threading.Tasks.Task`1.get_Result()
   at LinqToDB.EntityFrameworkCore.Internal.LinqToDBForEFQueryProvider`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at LinqToDB.AsyncExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken token)
   --- End of inner exception stack trace ---
   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
   at System.Threading.Tasks.Task`1.get_Result()
   at MyProject.Service.TestExecutionsService.GetTestBisectedExecutions(String configuration, Int32 maxTransitions, String testName, String svnRevision) in E:\MyDevCheckouts\DevFolder\MyProject_trunk\MyProject.Service\TestExecutionsService.cs:line 56
   at MyProject.Web.Controllers.TestExecutions.TestExecutionsController.IndexAlternate(BisectExecutionsGuiModel model) in E:\MyDevCheckouts\DevFolder\MyProject_trunk\MyProject.web\Controllers\TestExecutions\TestExecutionsController.cs:line 98
   ...（省略后续堆栈信息）
Exception thrown: 'System.NotImplementedException' in System.Private.CoreLib.dll
Exception thrown: 'System.NotImplementedException' in System.Private.CoreLib.dll

该查询在EF Core 3.1中可正常运行，但使用linq2db的ToListAsyncLinqToDB()时抛出上述异常，请问哪里操作出错了？

linq2db对EF Core查询表达式的支持和原生EF存在差异，你使用的x.Testruntestgrp.Select(y => y.Testname).Contains(testName)这种集合导航属性的Select+Contains写法，linq2db的表达式解析器无法将其转换为SQL语句。

可以将查询逻辑改为使用Any方法，这是linq2db能够正确解析的写法，逻辑上和原查询等价：

var testName = "MyTestName";
var test = await _fitDbContext.Testrunconfig
    .Include(x => x.Testruntestgrp)
    .Where(x => x.Testruntestgrp.Any(y => y.Testname == testName))
    .Take(10)
    .ToListAsyncLinqToDB();

另外注意：避免直接使用.Result获取异步任务结果，这可能导致死锁，改用await来异步等待结果。

Any方法会被linq2db转换为SQL中的EXISTS子查询，和原生EF处理原查询的逻辑一致，但linq2db对这种写法的支持更完善。

内容的提问来源于stack exchange，提问作者Aleks Vujic