如何使用LINQ中的Max翻译我的SQL查询？

Question

我想用EF在LINQ中翻译此SQL查询，

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

我尝试了：

    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();
    }

但是行

where g.CoFin <= datePurge

不起作用。

我认为我的“选择新”也不正确。

您能帮我解决这个问题吗？

Answer 1

尝试以下查询：

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();
}

请注意，LINQ查询是从类和导航属性构建的，如果您具有正确定义的模型，则可能不需要加入。