EF Core 6 + OData计算总数时触发Nullable object must have a value错误

问题描述 #

从SQL视图通过EF Core分组查询数据，使用自定义OData FilterAsync扩展时，获取数据（ToListAsync）可正常返回10条结果，但执行CountAsync计算符合条件的总行数时，抛出「Nullable object must have a value」异常，无论是否添加过滤器都会触发该错误。

请求地址 #

http://localhost:9081/api/customer

相关代码片段 #

DbContext定义 #

public class MyDbContext : DbContext
{
    public DbSet<DetailedCustomerView> list_detailed_customers { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<DetailedCustomerView>()
            .ToView("list_detailed_customers")
            .HasKey(t => t.CustomerId);
    }
}

视图实体 #

public class DetailedCustomerView
{
    [Column("customer_id")]
    public Int64 CustomerId { get; set; }

    [Column("group_id")]
    public Int64 GroupId { get; set; }
}

查询使用代码 #

dbContext.list_detailed_customers
    .GroupBy(x => x.CustomerId)
    .Select(cusromerGroup => new CustomerDetailedListDto
    {
       CustomerId = cusromerGroup.First().CustomerId,
       GroupIds = cusromerGroup.Select(x => x.GroupId)
    })
    .FilterAsync(request.Options, cancellationToken);

DTO定义 #

public struct CustomerDetailedListDto
{
    public Int64 CustomerId { get; set; }
    public IEnumerable<Int64> GroupIds { get; set; }
}

ODataFilter扩展方法 #

public static async Task<QueryResult<T>> FilterAsync<T>(this IQueryable<T> query, ODataQueryOptions options, CancellationToken cancellationToken)
{
    List<T> items = await query.ApplyTo(options, AllowedQueryOptions.None).ToListAsync(cancellationToken);
    Int32 totalCount = await query.ApplyTo(options, AllowedQueryOptions.Skip | AllowedQueryOptions.Top | AllowedQueryOptions.OrderBy).CountAsync(cancellationToken);

    return new QueryResult<T>(items, totalCount);
}

SQL视图简化定义 #

SELECT 
    1 AS customer_id,    
    1 AS group_id
FROM customer t
UNION
SELECT
    1 AS customer_id,    
    2 AS group_id
FROM customer

获取数据时生成的SQL #

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 6.0.6 initialized 'MyDbContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL:6.0.5+9d79af6e2586d5d28da253ac075706a5575a1743' with options: MaxPoolSize=1024
warn: Microsoft.EntityFrameworkCore.Query[10102]
      The query uses a row limiting operator ('Skip'/'Take') without an 'OrderBy' operator. This may lead to unpredictable results. If the 'Distinct' operator is used after 'OrderBy', then make sure to use the 'OrderBy' operator after 'Distinct' as the ordering would otherwise get erased.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (80ms) [Parameters=[@__TypedProperty_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT t.c, t.customer_id, l1.group_id, l1.customer_id
      FROM (
          SELECT (
              SELECT l0.customer_id
              FROM list_detailed_customers AS l0
              WHERE l.customer_id = l0.customer_id
              LIMIT 1) AS c, l.customer_id
          FROM list_detailed_customers AS l
          GROUP BY l.customer_id
          LIMIT @__TypedProperty_0
      ) AS t
      LEFT JOIN list_detailed_customers AS l1 ON t.customer_id = l1.customer_id
      ORDER BY t.customer_id

错误堆栈信息 #

Critical error occured: Nullable object must have a value.

     at System.Nullable`1.get_Value()
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ClientProjectionRemappingExpressionVisitor.Visit(Expression expression)
   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 Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ClientProjectionRemappingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyProjection(Expression shaperExpression, ResultCardinality resultCardinality, QuerySplittingBehavior querySplittingBehavior)
   at Microsoft.EntityFrameworkCore.Query.Internal.SelectExpressionProjectionApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.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__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at MyProject.Lib.OData.FilterExtension.FilterAsync[T](IQueryable`1 query, ODataQueryOptions options, CancellationToken cancellationToken)
   at MyProject.Core.Mediator.Dispatching.Dispatcher.Dispatch[TResponse](IRequest`1 request, CancellationToken cancellationToken)
   at Web.Controllers.CustomerController.GetList(ODataQueryOptions options)
   at lambda_method2589(Closure , Object )
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at NSwag.AspNetCore.Middlewares.SwaggerUiIndexMiddleware.Invoke(HttpContext context)
   at NSwag.AspNetCore.Middlewares.RedirectToIndexMiddleware.Invoke(HttpContext context)
   at NSwag.AspNetCore.Middlewares.OpenApiDocumentMiddleware.Invoke(HttpContext context)
   at MyProject.Core.Web.Middlewares.HeadersHandlerMiddleware.Invoke(HttpContext httpContext)
   at MyProject.Core.Web.Middlewares.ExceptionHandlerMiddleware.Invoke(HttpContext httpContext)

环境信息 #

• EF Core版本：6.0.6.0
• 数据库提供程序：Npgsql.EntityFrameworkCore.PostgreSQL:6.0.5、Microsoft.AspNetCore.OData 8.0.11
• 目标框架：NET 7.0
• 操作系统：Windows 11

解决方案 #

核心原因 #

EF Core 6在处理包含嵌套集合投影（如GroupIds = cusromerGroup.Select(x => x.GroupId)）的分组查询时，执行CountAsync无法正确翻译SQL，内部处理中出现Nullable类型未赋值的情况。

具体修复步骤 #

1. 优化分组查询的CustomerId获取方式
   分组后直接使用Group.Key获取CustomerId，替代First().CustomerId，减少不必要的子查询，避免潜在的Nullable问题：

   .Select(cusromerGroup => new CustomerDetailedListDto
   {
      CustomerId = cusromerGroup.Key, // 替换First().CustomerId
      GroupIds = cusromerGroup.Select(x => x.GroupId)
   })
   

2. 拆分列表与总数查询逻辑
   将获取数据和计算总数的查询分开，总数查询仅针对分组后的结果计数，不包含嵌套集合的投影，让EF Core能正确生成SQL：

   // 定义基础分组查询
   var groupedQuery = dbContext.list_detailed_customers
       .GroupBy(x => x.CustomerId);
   
   // 处理列表的查询（带DTO投影）
   var listQuery = groupedQuery.Select(g => new CustomerDetailedListDto
   {
       CustomerId = g.Key,
       GroupIds = g.Select(x => x.GroupId)
   });
   
   // 处理总数的查询（仅计数分组数量）
   var countQuery = groupedQuery;
   
   // 应用OData过滤器并执行
   var filteredList = await listQuery.ApplyTo(request.Options, AllowedQueryOptions.None).ToListAsync(cancellationToken);
   var totalCount = await countQuery.ApplyTo(request.Options, AllowedQueryOptions.Skip | AllowedQueryOptions.Top | AllowedQueryOptions.OrderBy).CountAsync(cancellationToken);
   
   return new QueryResult<CustomerDetailedListDto>(filteredList, totalCount);
   

3. 修改DTO为Class（可选）
   将CustomerDetailedListDto从struct改为class，避免值类型在EF投影处理中可能引发的异常：

   public class CustomerDetailedListDto
   {
       public Int64 CustomerId { get; set; }
       public IEnumerable<Int64> GroupIds { get; set; }
   }
   

4. 更新FilterAsync扩展（可选）
   扩展方法支持传入单独的计数查询，避免重复处理复杂投影：

   public static async Task<QueryResult<T>> FilterAsync<T>(this IQueryable<T> listQuery, IQueryable countQuery, ODataQueryOptions options, CancellationToken cancellationToken)
   {
       List<T> items = await listQuery.ApplyTo(options, AllowedQueryOptions.None).ToListAsync(cancellationToken);
       int totalCount = await countQuery.ApplyTo(options, AllowedQueryOptions.Skip | AllowedQueryOptions.Top | AllowedQueryOptions.OrderBy).CountAsync(cancellationToken);
   
       return new QueryResult<T>(items, totalCount);
   }
   

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