请求指导将含子查询、连接等操作的复杂SQL转换为LINQ

先拆解原SQL核心逻辑：

• 统计每个BookingID的总记录数
• 统计每个BookingID中InvoiceNumber不为空的记录数
• 筛选满足以下任一条件的记录：
  1. 对应BookingID的总记录数等于非空发票记录数（即该预订下所有记录都有发票）
  2. 当前记录的InvoiceNumber为空
• 最后按指定字段分组聚合总价，并排序

下面分别给出查询语法（更贴近SQL结构，易读）和方法语法（链式调用，简洁灵活）的实现：

查询语法实现 #

var result = from r in db.Reservations
             // 子查询1：统计每个BookingID的总记录数，对应SQL的all_group
             join allGroup in (
                 from rSub in db.Reservations
                 group rSub by rSub.BookingID into g
                 select new { BookingID = g.Key, Cnt = g.Count() }
             ) on r.BookingID equals allGroup.BookingID
             // 子查询2：统计每个BookingID中InvoiceNumber非空的记录数，对应SQL的not_null_group
             join notNullGroup in (
                 from rSub in db.Reservations
                 where rSub.InvoiceNumber != null
                 group rSub by rSub.BookingID into g
                 select new { BookingID = g.Key, Cnt = g.Count() }
             ) on r.BookingID equals notNullGroup.BookingID into leftJoinGroup
             from notNullGroup in leftJoinGroup.DefaultIfEmpty()
             // 应用WHERE筛选条件
             where allGroup.Cnt == notNullGroup?.Cnt || r.InvoiceNumber == null
             // 按指定字段分组，对应SQL的GROUP BY
             group r by new {
                 r.BookingID,
                 r.FlightDate,
                 r.CarrierCode,
                 r.FlightNo,
                 r.InvoiceNumber
             } into g
             // 聚合计算总价，对应SQL的SUM(r.Price)
             select new {
                 g.Key.BookingID,
                 g.Key.FlightDate,
                 g.Key.CarrierCode,
                 g.Key.FlightNo,
                 g.Key.InvoiceNumber,
                 TotalPrice = g.Sum(x => x.Price)
             }
             // 排序，对应SQL的ORDER BY
             orderby g.Key.FlightDate, g.Key.CarrierCode, g.Key.FlightNo, g.Key.InvoiceNumber;

方法语法实现 #

// 先定义两个子查询，对应SQL中的all_group和not_null_group
var allGroupQuery = db.Reservations
    .GroupBy(r => r.BookingID)
    .Select(g => new { BookingID = g.Key, Cnt = g.Count() });

var notNullGroupQuery = db.Reservations
    .Where(r => r.InvoiceNumber != null)
    .GroupBy(r => r.BookingID)
    .Select(g => new { BookingID = g.Key, Cnt = g.Count() });

// 主查询链式调用
var result = db.Reservations
    // 对应INNER JOIN all_group
    .Join(allGroupQuery, r => r.BookingID, ag => ag.BookingID, (r, ag) => new { r, ag })
    // 对应LEFT JOIN not_null_group
    .GroupJoin(notNullGroupQuery, 
        ra => ra.r.BookingID, 
        nng => nng.BookingID, 
        (ra, nngGroup) => new { ra.r, ra.ag, nngGroup })
    .SelectMany(x => x.nngGroup.DefaultIfEmpty(), 
        (x, nng) => new { x.r, x.ag, nng })
    // 应用WHERE筛选条件
    .Where(x => x.ag.Cnt == x.nng?.Cnt || x.r.InvoiceNumber == null)
    // 分组，对应GROUP BY
    .GroupBy(x => new {
        x.r.BookingID,
        x.r.FlightDate,
        x.r.CarrierCode,
        x.r.FlightNo,
        x.r.InvoiceNumber
    })
    // 聚合计算总价
    .Select(g => new {
        g.Key.BookingID,
        g.Key.FlightDate,
        g.Key.CarrierCode,
        g.Key.FlightNo,
        g.Key.InvoiceNumber,
        TotalPrice = g.Sum(item => item.r.Price)
    })
    // 排序，对应ORDER BY
    .OrderBy(g => g.FlightDate)
    .ThenBy(g => g.CarrierCode)
    .ThenBy(g => g.FlightNo)
    .ThenBy(g => g.InvoiceNumber);

关键对应说明 #

• SQL的INNER JOIN对应LINQ的Join方法（或查询语法的join）
• SQL的LEFT JOIN对应LINQ的GroupJoin+SelectMany+DefaultIfEmpty组合（查询语法中用into+from ... DefaultIfEmpty()）
• 空值判断用?.处理，对应SQL中LEFT JOIN后的空值场景
• GROUP BY和聚合逻辑直接映射为GroupBy+Select中的Sum方法
• 多字段排序用OrderBy+ThenBy链式调用，对应SQL的多字段ORDER BY

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