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 ?

LINQ isn't SQL, or a replacement for SQL. You shouldn't have to write explicit `join`s like this. If you use Entity Framework the JOIN clauses will be generated by the ORM based on the relations between entities. — Panagiotis Kanavos, May 18 '22 at 11:29
@PanagiotisKanavos OK. So how do I solve my problem with EF, without explicit join in LINQ ? — Resadan, May 18 '22 at 11:47
Are you trying to find agents whose last contract was before a specific date and their contract counts? To do that you'd have to include the latest contact date in `SELECT`, eg `MAX(Co.End) as LatestContract` — Panagiotis Kanavos, May 18 '22 at 11:56
Yes, exactly, I'd like to find agents whose last contract was before a specific date and their contracts count! Where could I put this select in my code ? The Id key is 'AgtNum'. You can find theses columns in Agts table (primary key) and Contracts table (not primary key). There is no foreign keys between them — Resadan, May 18 '22 at 12:08
Why not? That's sign of design problems. In any case, if there was no relation you wouldn't be able to JOIN them. A Foreign Key is actually a Foreign Key *Constraint*, not a relation. If the entity names follow EF's naming conventions, EF will infer the relations. In most databases the primary key is a field named `Id` or `TableNameId`. EF follows the same convention — Panagiotis Kanavos, May 18 '22 at 12:15
No matter what the tables look like, EF loads *classes*, not tables. In classes, related object appear in properties. If you were creating a standalone `Agent` class, wouldn't you add a `List Contracts` property? EF's job is to fill both `Agent` and `Contracts`. Behind the scenes it would create a `JOIN` to load the classes, or just the necessary fields — Panagiotis Kanavos, May 18 '22 at 12:19
So I can't join them because their primary keys aren't "Id" and I can't change it. How could I do ? — Resadan, May 18 '22 at 12:21
You may be able to write `db.Agents..GroupBy(ag=>ag).Select(ag=>new AgentToPurge{ag.Id,ag.FirstName,ag.LastName,Count=ag.Contracts.Count(),LastDate=ag.Contracts.Max(c=>c.End)}).Where(ag=>ag.LastDate<=datePurge).ToList();`. I haven't tried this yet, but I think EF can infer it needs to generate a `GROUP BY`. If that doesn't work, try `.GroupBy(ag=>ag).` [This question asks the same thing you do](https://stackoverflow.com/questions/29399430/entity-framework-group-by-with-max-date-and-count) except the `Where` afterwards — Panagiotis Kanavos, May 18 '22 at 12:28
`I can't join them because their primary keys aren't "Id"` quite the opposite. You can specify the relations in the DbContext's `OnModelCreating` method or through attributes. This is described in the docs, eg in the [Relations](https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key). This is such a fundamental concept it's covered in *all* courses and tutorials — Panagiotis Kanavos, May 18 '22 at 12:29
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, May 18 '22 at 20:33

score 0 · Answer 1 · answered May 18 '22 at 14:54

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.

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

1 Answers1