EF/Linq - Sort collection before grouping

Question

I have a one-to-many relationship between a project table and an audit table. I'm trying to pick out from the audit table the latest entry for each project entity. From what I understand to do this I should be able to sort my audit collection by date before grouping by project id, so that I can select the first entry for each group (project id) to get the latest entry. But when I run my ef/linq query, the results are not correct and the order-by seems to be ignored - even the generated sql doesn't include the order by statement. Heres the simple example I've tried. ``` using (var ctx = new MyDbContext()) { var audit = from a in ctx.ProjectAudits orderby a.CreatedDate descending group a by a.ProjectId into grp select grp.FirstOrDefault(); var resultsList = audit.ToList(); } ``` The results always return with the earliest audit entry for each project id and not the latest. Is there something wrong with this query; am I missing something obvious?

Accepted Answer

UPDATE

Okay, how about this?

```
ctx.ProjectAudits
    .GroupBy(p => p.ProjectId)
    .Select(p => p.OrderByDescending(j => j.CreatedDate).FirstOrDefault())
    .ToList();
```

Don't have VS with me here, but it should theoretically group your records, order them within the group by their creation date, and select the first record from each group.

EF/Linq - Sort collection before grouping

Solution

Problem

Related problems