如何将两类关联表聚合SQL查询转换为EF Core 7等效查询？

场景1：初始实体类对应SQL的EF Core查询 #

实体类定义 #

public class Product
{
    public int ProductId { get; set; }
    public int Score { get; set; }
    public int MarkId { get; set; }

    [ForeignKey("MarkId")]
    public Mark Mark { get; set; } = null!;
}

public class Mark
{
    public int MarkId { get; set; }
    public decimal? Value { get; set; }
    public int SuperMarkId { get; set; }

    [ForeignKey("SuperMarkId")]
    public SuperMark SuperMark { get; set; } = null!;
}

目标SQL查询 #

SELECT
    AVG(m.Value), MAX(p.Score)   
FROM
    Product p    
JOIN
    Mark m ON m.MarkId = p.MarkId

EF Core 7等效查询（两种写法） #

写法1：利用实体导航属性（更简洁）

var result = await context.Products
    .GroupBy(_ => 1) // 全局聚合使用固定分组键
    .Select(g => new
    {
        AvgValue = g.Average(p => p.Mark.Value),
        MaxScore = g.Max(p => p.Score)
    })
    .FirstOrDefaultAsync();

注：由于实体中Mark导航属性标记为非空（null!），EF Core会自动生成INNER JOIN，与原SQL逻辑一致。

写法2：显式Join方式

var result = await context.Products
    .Join(context.Marks,
          product => product.MarkId,
          mark => mark.MarkId,
          (product, mark) => new { product.Score, mark.Value })
    .GroupBy(_ => 1)
    .Select(g => new
    {
        AvgValue = g.Average(item => item.Value),
        MaxScore = g.Max(item => item.Score)
    })
    .FirstOrDefaultAsync();

场景2：新增SuperMark实体后的EF Core查询 #

新增实体类定义 #

public class SuperMark
{
    public int SuperMarkId { get; set; }
    public decimal DefaultValue { get; set; }
}

目标SQL查询 #

SELECT
    AVG(ISNULL(m.Value, sm.DefaultValue)), MAX(p.Score)
FROM
    Product p
JOIN
    Mark m ON m.MarkId = p.MarkId
JOIN 
    SuperMark sm ON sm.SuperMarkId = m.SuperMarkId

EF Core 7等效查询（两种写法） #

写法1：利用实体导航属性（更简洁）

var result = await context.Products
    .GroupBy(_ => 1)
    .Select(g => new
    {
        AvgValue = g.Average(p => p.Mark.Value ?? p.Mark.SuperMark.DefaultValue),
        MaxScore = g.Max(p => p.Score)
    })
    .FirstOrDefaultAsync();

注：EF Core会将C#的??空合并运算符自动翻译成SQL中的ISNULL（针对SQL Server），同时通过导航属性自动生成两层INNER JOIN，与原SQL逻辑完全匹配。

写法2：显式Join方式

var result = await context.Products
    .Join(context.Marks,
          product => product.MarkId,
          mark => mark.MarkId,
          (product, mark) => new { product.Score, mark.Value, mark.SuperMarkId })
    .Join(context.SuperMarks,
          productMark => productMark.SuperMarkId,
          superMark => superMark.SuperMarkId,
          (productMark, superMark) => new 
          { 
              productMark.Score, 
              CalculatedValue = productMark.Value ?? superMark.DefaultValue 
          })
    .GroupBy(_ => 1)
    .Select(g => new
    {
        AvgValue = g.Average(item => item.CalculatedValue),
        MaxScore = g.Max(item => item.Score)
    })
    .FirstOrDefaultAsync();

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