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?

Answer 1

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.

Answer 2

I think you need to look at a different approach. Instead of ordering, why don't you group and then select the audit with the maximum CreatedDate. I've not tested the following and am just throwing it out there:

var audit = from a in ctx.ProjectAudits
            group a by a.ProjectId into grp
            select new {
              // whatever your other properties are
              CreatedDate = grp.Max(i => i.CreatedDate)
            };

Or, as most people prefer the method syntax:

var audit = ctx.ProjectAudits
               .Where(i => i.CreatedDate == ctx.ProjectAudits
                                               .Max(x => x.CreatedDate));

EDIT - made some changes, and tested with a test class and a List<TestClass> and the above works with that.