How do I translate my SQL Query with Having MAX in LINQ?

Question

I'd like to translate this SQL Query in LINQ with EF

SELECT Agts.AgtNum, Agts.AgtLastname, Agts.AgtFirstname, COUNT(Co.CoEnd) FROM [dbo].Agts AS Agts
INNER JOIN [dbo].[Contracts] AS Co ON Agts.AgtNum = Co.AgtNum
GROUP BY Agts.AgtNum, Agts.AgtLastname, Agts.Firstname
HAVING MAX(Co.CoEnd) <= '2020-05-17'
ORDER BY AgtNum asc

I tried that :

    public List<AgentToPurge> AgentsToPurge(DateTime datePurge)
    {
        return  (from agent in this.Entities.Agts
                join contract in this.Entities.Contracts on agent.AgtNum equals contract.AgtNum
                group agent by agent.AgtNum into g
                where g.CoEnd <= datePurge

                select new AgentToPurge
                {
                    Id = g.Key,
                    Lastname = g.Key.AgtLastname,
                    Firstname = g.Key.AgtFirstname,
                    Contract_Deleted = g.Key.CoEnd.Count()
                }).ToList();
    }

But the line

where g.CoFin <= datePurge

doesn't work.

I think my "select new" isn't correct either.

Could you help me to solve this ?

Answer 1

Try the following query:

public List<AgentToPurge> AgentsToPurge(DateTime datePurge)
{
    return  (from agent in this.Entities.Agts
            join contract in this.Entities.Contracts on agent.AgtNum equals contract.AgtNum
            group contract by new { agent.AgtNum, agent.AgtLastname, agent.AgtFirstname } into g
            where g.Max(x => x.CoEnd) <= datePurge
            select new AgentToPurge
            {
                Id = g.Key.AgtNum,
                Lastname = g.Key.AgtLastname,
                Firstname = g.Key.AgtFirstname,
                Contract_Deleted = g.Sum(x => x.CoEnd != null ? 1 : 0)
            }).ToList();
}

Note that LINQ query is built from classes and navigation properties and probably you will not need JOIN, if you have properly defined Model.