如何左连接两个已内连接的表？及如何将指定SQL转Linq to SQL？

嘿，我来帮你搞定这两个问题！

其实逻辑很简单——你可以把已经完成内连接的结果当成一个临时数据集，再用它和目标表做左连接就好，不管是原生SQL还是Linq to SQL都能这么操作：

• 原生SQL写法示例：
  假设你已经有了A和B的内连接，现在要左连表C，可以把内连接包成子查询：

  SELECT *
  FROM (
      -- 先做A和B的内连接
      SELECT A.*, B.some_column
      FROM A INNER JOIN B ON A.id = B.a_id
  ) AS AB_Temp
  -- 用临时结果左连C
  LEFT JOIN C ON AB_Temp.id = C.ab_id
  

• Linq to SQL写法示例：
  先把内连接的结果存成一个临时集合，再执行左连接（注意Linq里左连需要用into搭配DefaultIfEmpty()来实现）：

  // 先完成A和B的内连接
  var innerJoinedData = from a in dbContext.A
                        join b in dbContext.B on a.Id equals b.AId
                        select new { A = a, BColumn = b.SomeColumn };
  
  // 用内连接结果左连表C
  var finalLeftJoin = from ab in innerJoinedData
                      join c in dbContext.C on ab.A.Id equals c.AbId into cGroup
                      from c in cGroup.DefaultIfEmpty() // 这一步对应SQL的LEFT JOIN
                      select new { ab.A, ab.BColumn, C = c };
  

先拆解下你的原生SQL逻辑：左连接tdHealthModules和HealthEventPos与HealthEvents的内连接结果，筛选出日期为2018-04-10的记录，再按模块信息分组，只保留预订数小于RedLight的模块。

对应的Linq to SQL代码可以这么写：

// 先定义目标日期，提前转成Date类型，方便EF解析
var targetDate = new DateTime(2018, 4, 10).Date;

var linqQuery = from m in dbContext.tdHealthModules
                // 先处理HealthEventPos和tdHealthEvents的内连接，筛选指定日期
                join pos in (
                    from p in dbContext.HealthEventPos
                    join e in dbContext.tdHealthEvents on p.HealthEventId equals e.Id
                    where e.Starttime.Date == targetDate
                    select p
                ) on m.Id equals pos.HealthModuleId into posGroup
                from pos in posGroup.DefaultIfEmpty() // 实现左连接
                // 按模块的所有非聚合字段分组，和原生SQL的group by保持一致
                group pos by new { m.Id, m.Name, m.RedLight, m.OrangeLight, m.GreenLight } into moduleGroup
                // 计算每个模块的预订数，左连接无匹配时pos为null，所以要排除null
                let bookingCount = moduleGroup.Count(p => p != null)
                // 对应原生SQL的having条件
                where bookingCount < moduleGroup.Key.RedLight
                // 选择最终需要返回的字段
                select new {
                    moduleGroup.Key.Id,
                    moduleGroup.Key.Name,
                    moduleGroup.Key.RedLight,
                    moduleGroup.Key.OrangeLight,
                    moduleGroup.Key.GreenLight,
                    NumberOfBookingsPerDate = bookingCount
                };

几个关键细节： #

• 把日期提前实例化，避免在Linq表达式里写字符串转换，让Entity Framework能更好地把Linq翻译成高效的SQL；
• Linq里没有直接的LEFT JOIN关键字，需要用into把匹配的结果分组，再用DefaultIfEmpty()来包含左表中没有匹配的记录；
• 分组时必须把所有要保留的非聚合字段都放进分组键里，否则Linq会报错，这和原生SQL的group by规则一致；
• Count(p => p != null)和原生SQL里的count(pos.HealthModuleId)效果完全一致——因为左连接无匹配时pos为null，count不会统计这些null值。

内容的提问来源于stack exchange，提问作者Björn Sturzrehm