EF Core按属性分组统计关联/无关联实体数量的实现方案

需求说明 #

• 按MappingTarget的Type属性分组
• 统计每组三个指标：总数、关联PrinterMapping的MappingTarget数量、无关联的数量
• 当前已通过客户端查询全量数据后统计实现，需迁移至数据库端执行以减少数据传输

现有客户端可行代码 #

var groupedMappings = await _spContext.MappingTargets.AsNoTracking()
        .Include(x => x.PrinterMappings)
        .GroupBy(target => target.Type)
        .Select(group => new
        {
            Type = group.Key,
            Targets = group.ToList()
        })
        .ToListAsync(ct);

var counts = groupedMappings.Select(group => 
          new DashboardMappingTargetDto(
              group.Type,
              group.Targets.Count,
              group.Targets.Count(target => target.PrinterMappings.Any()),
              group.Targets.Count(target => !target.PrinterMappings.Any())
          )).ToList();

结果模型 #

public record DashboardMappingTargetDto(int Type, int TotalCount, int CountWithMapping, int CountWithoutMapping);

尝试EF数据库端查询的报错 #

报错代码 #

var counts2 = await _spContext.MappingTargets.AsNoTracking()
       .GroupBy(target => target.Type)
       .Select(group => new DashboardMappingTargetDto(
           group.Key,
           group.Count(),
           group.Sum(target => target.PrinterMappings.Any() ? 1 : 0),
           group.Sum(target => !target.PrinterMappings.Any() ? 1 : 0)
       ))
       .ToListAsync(ct);

错误信息 #

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

解决方案 #

方案一：修正EF查询语句 #

问题根源是Sum中嵌套了Any()（会被EF转为子查询），导致SQL聚合函数嵌套。可以先投影出每个MappingTarget的关联状态，再分组统计：

var counts = await _spContext.MappingTargets.AsNoTracking()
    .Select(target => new 
    {
        target.Type,
        HasMapping = target.PrinterMappings.Any()
    })
    .GroupBy(x => x.Type)
    .Select(group => new DashboardMappingTargetDto(
        group.Key,
        group.Count(),
        group.Count(x => x.HasMapping),
        group.Count(x => !x.HasMapping)
    ))
    .ToListAsync(ct);

此写法会被EF正确转换为数据库端执行的分组统计SQL，避免全量数据传输。

方案二：原生SQL实现 #

若EF仍无法满足需求，可直接使用原生SQL查询，以下提供两种写法：

写法1：LEFT JOIN统计

SELECT 
    mt.Type,
    COUNT(mt.Id) AS TotalCount,
    SUM(CASE WHEN pm.MappingTargetId IS NOT NULL THEN 1 ELSE 0 END) AS CountWithMapping,
    SUM(CASE WHEN pm.MappingTargetId IS NULL THEN 1 ELSE 0 END) AS CountWithoutMapping
FROM MappingTargets mt
LEFT JOIN PrinterMappings pm ON mt.Id = pm.MappingTargetId
GROUP BY mt.Type

写法2：EXISTS子查询统计（性能更优）

SELECT 
    mt.Type,
    COUNT(mt.Id) AS TotalCount,
    SUM(CASE WHEN EXISTS (SELECT 1 FROM PrinterMappings pm WHERE pm.MappingTargetId = mt.Id) THEN 1 ELSE 0 END) AS CountWithMapping,
    SUM(CASE WHEN NOT EXISTS (SELECT 1 FROM PrinterMappings pm WHERE pm.MappingTargetId = mt.Id) THEN 1 ELSE 0 END) AS CountWithoutMapping
FROM MappingTargets mt
GROUP BY mt.Type

控制台测试代码（EF Core）

using var spContext = new YourDbContext();
var cancellationToken = CancellationToken.None;

// 定义SQL语句
var sqlQuery = @"
SELECT 
    mt.Type,
    COUNT(mt.Id) AS TotalCount,
    SUM(CASE WHEN EXISTS (SELECT 1 FROM PrinterMappings pm WHERE pm.MappingTargetId = mt.Id) THEN 1 ELSE 0 END) AS CountWithMapping,
    SUM(CASE WHEN NOT EXISTS (SELECT 1 FROM PrinterMappings pm WHERE pm.MappingTargetId = mt.Id) THEN 1 ELSE 0 END) AS CountWithoutMapping
FROM MappingTargets mt
GROUP BY mt.Type
";

// 执行原生SQL并映射到Dto
var rawResults = await spContext.Database
    .SqlQuery<(int Type, int TotalCount, int CountWithMapping, int CountWithoutMapping)>(sqlQuery)
    .ToListAsync(cancellationToken);

var counts = rawResults.Select(result => 
    new DashboardMappingTargetDto(result.Type, result.TotalCount, result.CountWithMapping, result.CountWithoutMapping))
    .ToList();

// 输出测试结果
foreach (var item in counts)
{
    Console.WriteLine($"Type: {item.Type}, Total: {item.TotalCount}, With Mapping: {item.CountWithMapping}, Without Mapping: {item.CountWithoutMapping}");
}

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