如何在Entity Framework/LINQ查询中使用MAX聚合函数？

我有一个使用Entity Framework Core 7对接MSSQL数据库的项目，在将SQL中的MAX聚合函数整合到LINQ语句时遇到问题。

数据结构 #

Create Table #Member (
    MemberID int identity(1,1) primary key,
    FirstName VarChar(20)
)

Create Table #QuestLog (
    QuestID int identity(1,1) primary key,
    MemberID int,
    Quest VarChar(20),
    GoldCollected Int,
    MonstersDispatched Int
)

测试数据 #

Insert #Member(FirstName) Values('Wealda')
Insert #Member(FirstName) Values('Tunbin')
Insert #Member(FirstName) Values('Ealhart')
Insert #Member(FirstName) Values('Rancent')

Insert #QuestLog(MemberID, Quest, GoldCollected, MonstersDispatched) Values(1, '1000 Doors', 100, 6)
Insert #QuestLog(MemberID, Quest, GoldCollected, MonstersDispatched) Values(1, '1000 Doors', 50, 100)
Insert #QuestLog(MemberID, Quest, GoldCollected, MonstersDispatched) Values(1, '1000 Doors', 75, 2)
Insert #QuestLog(MemberID, Quest, GoldCollected, MonstersDispatched) Values(1, 'Rats!', 555, 1500)
Insert #QuestLog(MemberID, Quest, GoldCollected, MonstersDispatched) Values(1, 'Kill Dr. Lucky', 50, 0)
Insert #QuestLog(MemberID, Quest, GoldCollected, MonstersDispatched) Values(1, 'Kill Dr. Lucky', 1000, 1)

期望的SQL查询及结果 #

目标SQL如下，能返回指定数据集：

Select M.FirstName, Q.Quest, Max(Q.GoldCollected) MaxGold
From #Member M
    Inner Join #QuestLog Q
        On M.MemberID = Q.MemberID
Group By M.FirstName, Q.Quest

返回结果：

现有LINQ查询（存在问题） #

var query = from m in dbContext.Member
                join q in dbContext.Quest on m.MemberID equals q.MemberID
            group new { m, q} by new { m.FirstName, q.Quest } into g 
            select new { FirstName = m.FirstName, Quest = q.Quest, MaxGold = ??? };

解决方案 #

你需要修改LINQ查询的两个关键点：

1. 分组后不能直接引用原始的m或q，要通过分组的Key属性获取分组依据的字段
2. 使用Max方法对分组内的GoldCollected字段计算最大值

正确的LINQ查询如下：

var query = from m in dbContext.Member
            join q in dbContext.QuestLog on m.MemberID equals q.MemberID
            group new { m, q } by new { m.FirstName, q.Quest } into g
            select new 
            {
                FirstName = g.Key.FirstName,
                Quest = g.Key.Quest,
                MaxGold = g.Max(item => item.q.GoldCollected)
            };

说明 #

• g.Key对应你分组时定义的匿名类型，包含FirstName和Quest两个字段，对应SQL中GROUP BY的分组字段
• g.Max(item => item.q.GoldCollected)会被EF Core转换为SQL中的MAX(Q.GoldCollected)，实现相同的聚合逻辑

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