Using a MAX aggregate in an Entity Framework/LINQ query

Question

I have a project where I need to use Entity Framework Core 7 against a MSSQL database but I'm stuck in bringing the MAX aggregate in SQL as a part of the LINQ statement.

Consider the following data:

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
)

This is the data for those tables:

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)

Here is the SQL query that brings me the data I want.

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

Which gives me this dataset:

FirstName	Quest	MaxGold
Wealda	1000 Doors	100
Wealda	Kill Dr. Lucky	1000
Wealda	Rats!	555

This is what I have so far in the LINQ query.

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 = ??? };

This is where I'm stuck. How do I translate the aggregate Max into a LINQ query and get the dame results?

Answer 1

You were close, it is trivial grouping query:

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