EF Core中GroupBy/GroupJoin搭配Count()是否不被支持？

我们有多个基于OData/SCIM实现过滤和分页的API端点，之前用EF6时的流程是：将ModelType投影到ResultType得到IQueryable，对结果类型应用过滤，接着在查询末尾添加Count()获取总记录数，最后对不含Count()的初始查询用Skip()和Take()处理分页并返回结果。这套流程在EF6中运行正常，但切换到EF Core 6/7（PostgreSQL实现）后，当返回对象包含集合时会报错。

以下是基于ASP.Net Identity数据库的LinqPad示例（Users对应aspnetusers表，UserLogins对应aspnetuserlogins表）：

可正常运行的代码 #

Users
.Select(u => new { Id = u.Id, UserName = u.UserName, IsEmailConfirmed = u.EmailConfirmed, LockoutEnd = u.LockoutEnd })
.GroupJoin(UserLogins.Select(x => new { x.UserId, x.LoginProvider }), u => u.Id, ul => ul.UserId, (u, uls) => new { u.Id, u.UserName, u.IsEmailConfirmed, u.LockoutEnd, LoginProviders = uls })
.SelectMany(x => x.LoginProviders.DefaultIfEmpty(), (x, l) => new { x.Id, x.UserName, x.IsEmailConfirmed, x.LockoutEnd, LoginProvider = l.LoginProvider })
.GroupBy(x => new {x.Id, x.UserName, x.IsEmailConfirmed, x.LockoutEnd}, x => x.LoginProvider)
.Select(g => new {g.Key.Id, g.Key.UserName, g.Key.IsEmailConfirmed, g.Key.LockoutEnd, LoginProviders = g.Select(x => x)})
.ToList();

无法运行的代码（仅末尾添加Count()） #

Users
.Select(u => new { Id = u.Id, UserName = u.UserName, IsEmailConfirmed = u.EmailConfirmed, LockoutEnd = u.LockoutEnd })
.GroupJoin(UserLogins.Select(x => new { x.UserId, x.LoginProvider }), u => u.Id, ul => ul.UserId, (u, uls) => new { u.Id, u.UserName, u.IsEmailConfirmed, u.LockoutEnd, LoginProviders = uls })
.SelectMany(x => x.LoginProviders.DefaultIfEmpty(), (x, l) => new { x.Id, x.UserName, x.IsEmailConfirmed, x.LockoutEnd, LoginProvider = l.LoginProvider })
.GroupBy(x => new {x.Id, x.UserName, x.IsEmailConfirmed, x.LockoutEnd}, x => x.LoginProvider)
.Select(g => new {g.Key.Id, g.Key.UserName, g.Key.IsEmailConfirmed, g.Key.LockoutEnd, LoginProviders = g.Select(x => x)})
.Count();

抛出的异常信息 #

System.InvalidOperationException: 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 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 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__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 UserQuery.Main() in C:\Users\brian.adams\AppData\Local\Temp\LINQPad7\_yrgghvyx\mhemrm\LINQPadQuery:line 5

问题根源 #

这是EF Core在处理包含集合投影的查询时，调用Count()的翻译bug：EF Core尝试移除客户端投影（如集合属性）来优化Count查询，但处理Nullable类型时触发了空引用错误。

解决办法 #

1. 拆分查询，直接统计主实体数量
   你的查询最终是按用户分组，统计的是用户总数，因此可以直接对过滤后的Users集合调用Count()，无需走完整的投影+分组流程，既避免bug又提升性能：

   // 先获取过滤后的用户总数（若有过滤条件，需先应用到Users上）
   var totalCount = Users.Count();
   
   // 再执行完整的投影+分页查询
   var paginatedResult = Users
       .Select(u => new { Id = u.Id, UserName = u.UserName, IsEmailConfirmed = u.EmailConfirmed, LockoutEnd = u.LockoutEnd })
       .GroupJoin(UserLogins.Select(x => new { x.UserId, x.LoginProvider }), u => u.Id, ul => ul.UserId, (u, uls) => new { u.Id, u.UserName, u.IsEmailConfirmed, u.LockoutEnd, LoginProviders = uls })
       .SelectMany(x => x.LoginProviders.DefaultIfEmpty(), (x, l) => new { x.Id, x.UserName, x.IsEmailConfirmed, x.LockoutEnd, LoginProvider = l.LoginProvider })
       .GroupBy(x => new {x.Id, x.UserName, x.IsEmailConfirmed, x.LockoutEnd}, x => x.LoginProvider)
       .Select(g => new {g.Key.Id, g.Key.UserName, g.Key.IsEmailConfirmed, g.Key.LockoutEnd, LoginProviders = g.Select(x => x)})
       .Skip(skipNumber)
       .Take(pageSize)
       .ToList();
   

   注意：如果有过滤条件，必须确保Count()和分页查询使用同一个过滤后的IQueryable<Users>，避免数据不一致。

2. 临时方案：内存中统计（仅小数据量）
   若无法修改查询结构，可先将查询转换为IEnumerable再调用Count()，但这会将所有数据拉到内存，仅适合小数据量场景：

   var count = query.AsEnumerable().Count();
   

3. 升级到EF Core 8
   该bug已在EF Core 8中被官方修复，若项目允许升级版本，可直接解决问题。

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