如何在C#中编写包含MAX()的关联子查询？

我能用C#编写查询，但不知道怎么写包含MAX()的子查询，对应的SQL语句如下：

SELECT * 
FROM Stores.PerformanceRealization PR
LEFT JOIN Stores.GroupRealization ON Stores.GroupRealization.Id = PR.GroupRealizationId
WHERE PR.Deadline = (SELECT MAX(Deadline) 
                     FROM Stores.PerformanceRealization PR2 
                     WHERE PR.GroupRealizationId = PR2.GroupRealizationId)

我尝试的代码如下：

var result = from aa in _context.PerformanceRealization
             join bb in _context.GroupRealization on bb.Id equals aa.GroupRealizationId
             where aa.Deadline = (from cc in _context.PerformanceRealization 
                                  where aa.GroupRealizationId = cc.GroupRealizationId 
                                  select max(cc.Deadline))   
             select aa;    

你的代码存在几个编译逻辑问题：

• C#中判断相等要用==，而非赋值运算符=，你在where条件里误用了赋值符号
• LINQ中取最大值需调用Max()方法，不是小写的max
• 原SQL是LEFT JOIN，但你的代码用的是内连接，需要调整写法实现左连接

下面是两种正确的实现方式：

方式1：LINQ查询语法 #

var result = from pr in _context.PerformanceRealization
             join gr in _context.GroupRealization 
             on pr.GroupRealizationId equals gr.Id into grGroup
             from gr in grGroup.DefaultIfEmpty() // 实现LEFT JOIN
             where pr.Deadline == (
                 from pr2 in _context.PerformanceRealization
                 where pr.GroupRealizationId == pr2.GroupRealizationId
                 select pr2.Deadline
             ).Max()
             select new { pr, gr }; // 对应原SQL的SELECT *，按需返回实体组合

方式2：LINQ方法语法 #

如果更习惯链式调用的写法，可使用如下代码：

var result = _context.PerformanceRealization
    .GroupJoin(
        _context.GroupRealization,
        pr => pr.GroupRealizationId,
        gr => gr.Id,
        (pr, grs) => new { pr, gr = grs.DefaultIfEmpty() }
    )
    .SelectMany(x => x.gr, (x, gr) => new { x.pr, gr })
    .Where(x => x.pr.Deadline == _context.PerformanceRealization
        .Where(pr2 => pr2.GroupRealizationId == x.pr.GroupRealizationId)
        .Max(pr2 => pr2.Deadline))
    .Select(x => new { x.pr, x.gr });

补充说明 #

• 若仅需返回PerformanceRealization实体，将select部分改为select pr即可，和你原代码的select aa对应
• 确保Deadline字段是可比较类型（如DateTime、DateTimeOffset），否则Max()方法会抛出异常

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