如何将多分组SQL查询转换为EF Core 6等效实现？

问题场景 #

你有一个PostgreSQL数据库，包含student主表、amount附加表及3个字典表（region、district、department），需要将已验证的SQL分组查询转换为EF Core 6等效实现，同时解决字典表名称获取、空值检查及性能优化问题。

数据库结构 #

create table region (id serial primary key, name varchar);
create table district (id serial primary key, name varchar);
create table department (id serial primary key, name varchar);

create table student (
    id serial primary key,
    name varchar,
    region_id bigint references region,        
    district_id bigint references district,
    department_id bigint references department
);

create table amount (
    id serial primary key,
    student_id bigint references student on delete cascade,
    value numeric,
    year int
);

原SQL查询 #

select
    t.region_id,
    region."name" region_name,
    t.district_id,
    district."name" district_name,
    t.department_id,
    department."name" department_name,
    t.cnt,
    t.value
from (
    select
        region_id,
        district_id,
        department_id,
        count(distinct s.id) cnt,
        sum(a.value) "value"
    from student s 
    join amount a on s.id = a.student_id 
    where a.year = 2020
    group by region_id, district_id, department_id
) t
join region on t.region_id = region.id
join district on t.district_id = district.id 
join department on t.department_id = department.id 

现有实体类与部分EF查询 #

Student实体类

[Table("student")]
public class Student
{
    [Key]
    [Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public string? Name { get; set; }

    [Column("region_id")]
    public int? RegionId { get; set; }

    [Column("district_id")]
    public int? DistrictId { get; set; }

    [Column("department_id")]
    public int? DepartmentId { get; set; }

    [ForeignKey(nameof(RegionId))]
    public virtual Region? Region { get; set; }

    [ForeignKey(nameof(DistrictId))]
    public virtual District? District { get; set; }

    [ForeignKey(nameof(DepartmentId))]
    public virtual Department? Department { get; set; }

    public ICollection<Amount>? Amounts { get; set; }
}

部分EF查询

var result = await db.Student
    .GroupBy(x => new { x.RegionId, x.DistrictId, x.DepartmentId })
    .Select(x => new 
    {
        x.Key.RegionId,
        x.Key.DistrictId,            
        x.Key.DepartmentId,
        Cnt = x.Count(),
        Value = x.Sum(c => c.Amounts.Where(v => v.Year == 2020).Sum(v => v.Value))
    })
    .ToListAsync();

你尝试通过x.First().Region.Name获取字典表名称，但不确定方案是否最优，且需要添加空值检查。

解决方案 #

1. 原方案的问题分析 #

• 空值风险：如果RegionId/DistrictId/DepartmentId为null，对应的导航属性Region/District/Department会是null，直接访问.Name会抛出NullReferenceException。
• 性能问题：x.First()会让EF Core生成额外的子查询，相比数据库层面直接关联字典表，效率更低。
• 求和逻辑错误：原SQL是对amount.value直接求和，而你写的嵌套Sum会导致重复计算（先对每个Student的Amount求和，再对分组内的结果求和），与原SQL逻辑不符。

2. 最优实现（贴近原SQL逻辑） #

推荐使用LINQ查询语法，让EF Core生成与原SQL几乎一致的数据库查询，在数据库层面完成分组、关联和统计，性能最优，同时自然处理空值。

var result = await (from s in db.Student
                    // 关联2020年的amount数据，与原SQL的join逻辑一致
                    join a in db.Amount on s.Id equals a.StudentId
                    where a.Year == 2020
                    // 按三个字典ID分组
                    group new { s, a } by new { s.RegionId, s.DistrictId, s.DepartmentId } into g
                    // 关联字典表，使用内连接（与原SQL一致，过滤掉ID为null的分组）
                    join r in db.Region on g.Key.RegionId equals r.Id
                    join d in db.District on g.Key.DistrictId equals d.Id
                    join dp in db.Department on g.Key.DepartmentId equals dp.Id
                    select new
                    {
                        g.Key.RegionId,
                        RegionName = r.Name,
                        g.Key.DistrictId,
                        DistrictName = d.Name,
                        g.Key.DepartmentId,
                        DepartmentName = dp.Name,
                        // 对应原SQL的count(distinct s.id)
                        Cnt = g.Select(x => x.s.Id).Distinct().Count(),
                        // 对应原SQL的sum(a.value)
                        Value = g.Sum(x => x.a.Value)
                    })
                    .ToListAsync();

3. 支持空ID的版本（左连接） #

如果需要保留RegionId/DistrictId/DepartmentId为null的分组，将内连接改为左连接（使用into+DefaultIfEmpty()），并添加空值检查：

var result = await (from s in db.Student
                    join a in db.Amount on s.Id equals a.StudentId
                    where a.Year == 2020
                    group new { s, a } by new { s.RegionId, s.DistrictId, s.DepartmentId } into g
                    // 左连接字典表，保留ID为null的分组
                    join r in db.Region on g.Key.RegionId equals r.Id into regionGroup
                    from region in regionGroup.DefaultIfEmpty()
                    join d in db.District on g.Key.DistrictId equals d.Id into districtGroup
                    from district in districtGroup.DefaultIfEmpty()
                    join dp in db.Department on g.Key.DepartmentId equals dp.Id into deptGroup
                    from department in deptGroup.DefaultIfEmpty()
                    select new
                    {
                        g.Key.RegionId,
                        // 空值检查：如果region为null，返回默认文本（可改为""或其他）
                        RegionName = region?.Name ?? "未指定",
                        g.Key.DistrictId,
                        DistrictName = district?.Name ?? "未指定",
                        g.Key.DepartmentId,
                        DepartmentName = department?.Name ?? "未指定",
                        Cnt = g.Select(x => x.s.Id).Distinct().Count(),
                        Value = g.Sum(x => x.a.Value)
                    })
                    .ToListAsync();

4. 方法语法等效实现 #

如果你偏好方法语法，也可以写出等效代码：

var result = await db.Student
    .Join(db.Amount, s => s.Id, a => a.StudentId, (s, a) => new { s, a })
    .Where(x => x.a.Year == 2020)
    .GroupBy(x => new { x.s.RegionId, x.s.DistrictId, x.s.DepartmentId })
    .Join(db.Region, g => g.Key.RegionId, r => r.Id, (g, r) => new { g, r })
    .Join(db.District, gr => gr.g.Key.DistrictId, d => d.Id, (gr, d) => new { gr.g, gr.r, d })
    .Join(db.Department, grd => grd.g.Key.DepartmentId, dp => dp.Id, (grd, dp) => new
    {
        grd.g.Key.RegionId,
        RegionName = grd.r.Name,
        grd.g.Key.DistrictId,
        DistrictName = grd.d.Name,
        grd.g.Key.DepartmentId,
        DepartmentName = dp.Name,
        Cnt = grd.g.Select(x => x.s.Id).Distinct().Count(),
        Value = grd.g.Sum(x => x.a.Value)
    })
    .ToListAsync();

关键说明 #

• 求和逻辑修正：使用g.Sum(x => x.a.Value)直接对分组内的amount.value求和，与原SQL的sum(a.value)完全一致，避免嵌套求和的错误。
• 去重计数：g.Select(x => x.s.Id).Distinct().Count()对应原SQL的count(distinct s.id)，确保统计的是唯一Student数量。
• 空值处理：左连接+?.+??的组合，既保留了ID为null的分组，又避免了空引用异常。
• 性能优化：所有逻辑在数据库层面完成，EF Core生成的SQL与原SQL结构一致，避免客户端额外计算，性能最优。

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