为GraphQL查询添加UseProjection时出现LINQ执行异常

技术栈 #

• HotChocolate
• EF Core 8
• .NET 8

实体类定义 #

public class Batch
{
    public Batch()
    {
    }

    public Batch(string batchId, string tenantId, int paymentsCount, decimal totalAmount, string status,
        string statusDescription)
    {
        BatchId = batchId;
        TenantId = tenantId;
        PaymentsCount = paymentsCount;
        TotalAmount = totalAmount;
        Status = status;
        StatusDescription = statusDescription;
    }

    public string BatchId { get; set; }
    public string TenantId { get; set; }
    public int PaymentsCount { get; set; }
    public decimal TotalAmount { get; set; }
    public string Status { get; set; }
    public string StatusDescription { get; set; }
    [UseFiltering]
    [UseSorting]
    public ICollection<Payment> Payments { get; set; }
}

public class Payment
{
    public Payment()
    {
    }

    public string Id { get; set; }
    public string PayeeName { get; set; }
    public string CheckNumber { get; set; }
    //public List<string> InvoiceNumbers { get; set; }
    public DateTime PaymentDate { get; set; }
    public string Status { get; set; }
    public string StatusDescription { get; set; }
    public decimal Amount { get; set; }
}

GraphQL查询解析器 #

public class BatchesQuery
{
    // Resolver
    [UsePaging(IncludeTotalCount = true)]
    [UseProjection]
    [UseFiltering]
    [UseSorting]
    public IQueryable<Batch> Batches([Service] PaymentDbContext paymentDbContext, string tenantId, IResolverContext context)
    {
        var paymentBatchesQueryable = paymentDbContext.PaymentBatches
            .Where(pb => pb.TenantId == tenantId)
            .OrderByDescending(pb => pb.CreatedTimestamp)
            .AsNoTracking();
        var paymentRequests = paymentBatchesQueryable.SelectMany(pb => pb.PaymentRequests);
        var payments = paymentRequests.Select(pr => new Payment()
        {
            Id = pr.PaymentRequestExternalId,
            PayeeName = pr.Payee.Name,
            CheckNumber = pr.Number,
            //InvoiceNumbers = pr.RemittanceDetails.Select(rd => rd.Number).ToList(),
            PaymentDate = pr.RequestDate,
            Status = pr.PaymentRequestStatusType.ToString(),
            StatusDescription = pr.PaymentRequestStatusType.ToString(),
            Amount = pr.RemittanceDetails.Sum(rd => rd.Net)
        });

        var paymentBatches = paymentBatchesQueryable
            .Select(pb => new Batch(pb.PaymentBatchExternalId,
                pb.TenantId,
                pb.PaymentRequests.Count,
                pb.PaymentRequests
                    .SelectMany(pr => pr.RemittanceDetails)
                    .Sum(rd => rd.Net),
                pb.PaymentBatchStatusType.ToString(),
                pb.PaymentBatchStatusType.ToString())
            {
                Payments = payments.ToList()
      
            });
       

        return paymentBatches;
    }
}

Program.cs配置 #

builder.Services.AddGraphQLServer()
    .AddQueryType<BatchesQuery>()
    .AddProjections()
    .AddFiltering()
    .AddSorting()
    .RegisterDbContext<PaymentDbContext>();

执行的GraphQL查询 #

query{
  batches(tenantId: "yKYrJLSUrE6XDWkrQC0e"){
    nodes{
      batchId
      payments{
        id
      }
    }
  }
}

错误响应 #

{
  "errors": [
    {
      "message": "Unexpected Execution Error",
      "locations": [
        {
          "line": 2,
          "column": 3
        }
      ],
      "path": [
        "batches"
      ],
      "extensions": {
        "message": "The LINQ expression 'p1 => new Payment{ Id = p1.Id }\r\n' 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.",
        "stackTrace": "   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitLambda[T](Expression`1 lambdaExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression, Boolean applyDefaultTypeMapping)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateProjection(Expression expression, Boolean applyDefaultTypeMapping)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberAssignment(MemberAssignment memberAssignment)\r\n   at System.Linq.Expressions.ExpressionVisitor.VisitMemberBinding(MemberBinding node)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitMemberInit(MemberInitExpression memberInitExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)\r\n   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)\r\n   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()\r\n   at HotChocolate.Types.Pagination.QueryableCursorPagination`1.ExecuteAsync(IQueryable`1 query, Int32 offset, CancellationToken cancellationToken)\r\n   at HotChocolate.Types.Pagination.CursorPaginationAlgorithm`2.ApplyPaginationAsync(TQuery query, CursorPagingArguments arguments, Nullable`1 totalCount, CancellationToken cancellationToken)\r\n   at HotChocolate.Types.Pagination.QueryableCursorPagingHandler`1.ResolveAsync(IResolverContext context, IQueryable`1 source, CursorPagingArguments arguments, CancellationToken cancellationToken)\r\n   at HotChocolate.Types.Pagination.CursorPagingHandler.HotChocolate.Types.Pagination.IPagingHandler.SliceAsync(IResolverContext context, Object source)\r\n   at HotChocolate.Types.Pagination.PagingMiddleware.InvokeAsync(IMiddlewareContext context)\r\n   at HotChocolate.Utilities.MiddlewareCompiler`1.ExpressionHelper.AwaitTaskHelper(Task task)\r\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)\r\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)"
      }
    }
  ],
  "data": {
    "batches": null
  }
}

问题根源 #

你的解析器代码存在两个核心问题，导致EF Core无法配合HotChocolate的UseProjection生成可翻译的SQL：

1. 跨查询集合引用错误：你先定义了独立的payments查询，随后在Batch的投影中直接引用这个外部查询的ToList()结果。EF Core无法将这种跨查询的关联逻辑翻译成SQL，加上UseProjection会触发字段裁剪，进一步打乱了EF的查询翻译流程。

2. 嵌套集合投影方式错误：嵌套的Payments集合必须在当前Batch的Select表达式内部直接投影，不能引用外部预定义的查询。

修复后的解析器代码 #

public class BatchesQuery
{
    [UsePaging(IncludeTotalCount = true)]
    [UseProjection]
    [UseFiltering]
    [UseSorting]
    public IQueryable<Batch> Batches([Service] PaymentDbContext paymentDbContext, string tenantId)
    {
        return paymentDbContext.PaymentBatches
            .Where(pb => pb.TenantId == tenantId)
            .OrderByDescending(pb => pb.CreatedTimestamp)
            .AsNoTracking()
            .Select(pb => new Batch(
                pb.PaymentBatchExternalId,
                pb.TenantId,
                pb.PaymentRequests.Count,
                pb.PaymentRequests.SelectMany(pr => pr.RemittanceDetails).Sum(rd => rd.Net),
                pb.PaymentBatchStatusType.ToString(),
                pb.PaymentBatchStatusType.ToString())
            {
                Payments = pb.PaymentRequests.Select(pr => new Payment
                {
                    Id = pr.PaymentRequestExternalId,
                    PayeeName = pr.Payee.Name,
                    CheckNumber = pr.Number,
                    PaymentDate = pr.RequestDate,
                    Status = pr.PaymentRequestStatusType.ToString(),
                    StatusDescription = pr.PaymentRequestStatusType.ToString(),
                    Amount = pr.RemittanceDetails.Sum(rd => rd.Net)
                }).ToList()
            });
    }
}

为什么移除UseProjection会生效？ #

移除UseProjection后，HotChocolate不会对返回的IQueryable进行字段裁剪，EF Core会直接加载完整的实体数据到内存中，再进行客户端处理，因此不会触发SQL翻译错误。但这种方式会加载大量不必要的数据，性能极差，不建议使用。

额外优化建议 #

• 解析器无需注入IResolverContext，除非需要用到上下文的其他功能
• 可为枚举类型创建扩展方法，避免重复调用ToString()
• 保持Payments上的[UseFiltering]和[UseSorting]特性，确保嵌套集合的过滤排序功能正常

内容的提问来源于stack exchange，提问作者vamsi krishna