如何将含LEFT OUTER JOIN的高级SQL语句转换为LINQ-to-SQL？

刚好我之前处理过类似的场景，咱们一步步来把这个SQL转成LINQ-to-SQL，先理清楚原SQL的逻辑，再对应到LINQ的写法上～

先回顾下原需求对应的SQL：它通过自左连接，筛选出每个id下不存在更大rev的行，也就是每个id对应最大rev的记录，SQL代码如下：

SELECT d1.* FROM docs AS d1 LEFT OUTER JOIN docs AS d2 ON (d1.id = d2.id AND d1.rev < d2.rev) WHERE d2.id is null ORDER BY id;

你提到的难点确实在ON子句里的复合条件（id相等+d1.rev < d2.rev），下面给你几种可行的LINQ-to-SQL写法：

写法一：直接模拟原SQL的左连接逻辑 #

这种写法和原SQL的结构最贴近，容易对应理解：

var query = from d1 in db.Docs
            // 先按Id关联，再筛选出d2的rev比d1大的记录
            join d2 in db.Docs on d1.Id equals d2.Id into tempDocs
            from d2 in tempDocs.Where(d => d.Rev > d1.Rev).DefaultIfEmpty()
            // 没有找到符合条件的d2，说明当前d1的rev是该Id下最大的
            where d2 == null
            orderby d1.Id
            select d1;

这里的join ... into对应SQL的左连接，Where(d => d.Rev > d1.Rev)就是模拟原SQL里d1.rev < d2.rev的条件，DefaultIfEmpty()保证即使没有匹配的d2，也会保留d1的记录，最后筛选出d2 == null的行就是我们要的结果。

写法二：用方法语法实现相同逻辑 #

如果你更习惯链式调用的方法语法，对应的代码是这样的：

var query = db.Docs
    // 按Id分组关联原表
    .GroupJoin(db.Docs,
        d1 => d1.Id,
        d2 => d2.Id,
        (d1, relatedDocs) => new { Doc = d1, RelatedDocs = relatedDocs })
    // 筛选出关联文档中rev更大的，没有则返回空
    .SelectMany(x => x.RelatedDocs.Where(d => d.Rev > x.Doc.Rev).DefaultIfEmpty(),
        (x, d2) => new { x.Doc, d2 })
    // 保留没有更大rev的记录
    .Where(result => result.d2 == null)
    .OrderBy(result => result.Doc.Id)
    .Select(result => result.Doc);

写法三：分组找最大值再关联（更直观的替代方案） #

如果你觉得模拟左连接有点绕，还可以换个思路：先找出每个Id对应的最大Rev值，再和原表关联获取完整记录，逻辑更清晰：

// 先获取每个Id对应的最大Rev
var maxRevPerId = db.Docs
    .GroupBy(doc => doc.Id)
    .Select(group => new { Id = group.Key, MaxRev = group.Max(doc => doc.Rev) });

// 关联原表，获取对应最大Rev的完整记录
var query = from doc in db.Docs
            join maxRev in maxRevPerId 
            on new { doc.Id, doc.Rev } equals new { maxRev.Id, maxRev.MaxRev }
            orderby doc.Id
            select doc;

这种写法的业务逻辑更直白，性能上和原SQL也差不多，适合更喜欢直观逻辑的场景。

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