.NET 8 EF Core查询报Sequence contains more than one element原因咨询

场景概述 #

在.NET 8环境下使用Entity Framework Core编写LINQ查询时，抛出System.InvalidOperationException，提示Sequence contains more than one element。

原始LINQ查询 #

var previousAudit = DbContext.AuditInspections
    .AsNoTracking()
    .Where(i => i.CreationDateTime >= financialYearStart.AddYears(-1) &&
                i.CreationDateTime <= financialYearStart.AddDays(-1) &&
                i.SiteModelId == inspectionData.SiteId &&
                i.FlowStateModel.Name == StateEnums.AuditReportReleased)
    .OrderByDescending(i => i.CreationDateTime)
    .Select(i => new
    {
        i.Id,
        i.AuditDateTime,
        i.DeadlineDateTime,
        Score = i.AuditInspectionQuestionModels
            .GroupBy(q => new
            {
                q.AuditQuestionPriority,
                q.AuditChoice
            })
            .Select(g => new
            {
                g.Key.AuditQuestionPriority,
                g.Key.AuditChoice,
                Count = g.Count()
            })
            .ToList()
    })
    .FirstOrDefault();

生成的SQL语句 #

SELECT [t].[Id], [t].[AuditDateTime], [t].[DeadlineDateTime], [t].[Id0], [t0].[AuditQuestionPriority], [t0].[AuditChoice], [t0].[Count]
FROM (
    SELECT TOP(1) [a].[Id], [a].[AuditDateTime], [a].[DeadlineDateTime], [f].[Id] AS [Id0], [a].[CreationDateTime]
    FROM [AuditInspections] AS [a]
    INNER JOIN [FlowStates] AS [f] ON [a].[FlowStateModelId] = [f].[Id]
    WHERE [a].[CreationDateTime] >= @__AddYears_0 AND [a].[CreationDateTime] <= @__AddDays_1 AND [a].[SiteModelId] = @__inspectionData_SiteId_2 AND [f].[Name] = N'Audit Report Released'
    ORDER BY [a].[CreationDateTime] DESC
) AS [t]
OUTER APPLY (
    SELECT [a0].[AuditQuestionPriority], [a0].[AuditChoice], COUNT(*) AS [Count]
    FROM [AuditInspectionQuestions] AS [a0]
    WHERE [t].[Id] = [a0].[AuditInspectionModelId]
    GROUP BY [a0].[AuditQuestionPriority], [a0].[AuditChoice]
) AS [t0]
ORDER BY [t].[CreationDateTime] DESC, [t].[Id], [t].[Id0], [t0].[AuditQuestionPriority]

错误详情 #

An unhandled exception has occurred while executing the request.
System.InvalidOperationException: Sequence contains more than one element.
at async Task Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync(IAsyncEnumerable asyncEnumerable, CancellationToken cancellationToken) x 2
at async Task ios_api.Controllers.Features.AuditModule.Inspection.AuditInspectionController.ShareAuditInspectionReports(int inspectionId, ReportRecipients reportRecipients) in C:/Usera/Project/msTest/ios_api/Controllers/Features/AuditModule/Inspection/AuditInspectionController.cs:line 2986

已验证的解决方案 #

• 转为内存查询：先调用ToList()将主表数据加载到内存，再执行嵌套分组的Select操作（转为IEnumerable<>处理），代码如下：

var previousAudit = DbContext.AuditInspections
    .AsNoTracking()
    .Where(i => i.CreationDateTime >= financialYearStart.AddYears(-1) &&
                i.CreationDateTime <= financialYearStart.AddDays(-1) &&
                i.SiteModelId == inspectionData.SiteId &&
                i.FlowStateModel.Name == StateEnums.AuditReportReleased)
    .OrderByDescending(i => i.CreationDateTime)
    .Include(auditInspectionModel => auditInspectionModel.AuditInspectionQuestionModels)
    .ToList()
    .Select(i => new
    {
        i.Id,
        i.AuditDateTime,
        i.DeadlineDateTime,
        Score = i.AuditInspectionQuestionModels
            .GroupBy(q => new
            {
                q.AuditQuestionPriority,
                q.AuditChoice
            })
            .Select(g => new
            {
                g.Key.AuditQuestionPriority,
                g.Key.AuditChoice,
                Count = g.Count()
            })
            .ToList()
    })
    .FirstOrDefault();

• 移除站点ID过滤条件：删除i.SiteModelId == inspectionData.SiteId条件后，查询可正常执行（inspectionData.SiteId为int类型，非集合）

核心原因 #

这是EF Core 8的查询翻译逻辑bug，具体触发点如下：

1. SQL结果与对象映射的逻辑冲突：原始查询中，EF Core将FirstOrDefault()获取的单条主记录，与OUTER APPLY返回的多条分组数据组合，尝试映射到包含嵌套列表的匿名对象。但添加i.SiteModelId == inspectionData.SiteId过滤条件后，EF Core的翻译器错误地将嵌套集合的映射逻辑替换为单值查询逻辑（内部调用了SingleOrDefaultAsync），当嵌套分组有多个结果时，直接触发"Sequence contains more than one element"异常。
2. 内存查询与数据库查询的处理差异：转为内存IEnumerable查询时，.NET的LINQ to Objects会正确识别嵌套集合并处理分组，不会出现逻辑误判；而数据库层面的查询翻译分支存在缺陷，在特定过滤条件下触发了错误的映射逻辑。
3. 过滤条件改变翻译路径：移除站点ID条件后，EF Core切换到了正确的集合映射翻译分支，因此异常消失。

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