c# - LINQ to SQL: GroupBy() and Max() to get the object with latest date

Question

Consider a SQL Server table that's used to store events for auditing.

The need is to get only that latest entry for each CustID. We want to get the entire object/row. I am assuming that a GroupBy() will be needed in the query. Here's the query so far:

var custsLastAccess = db.CustAccesses   
                        .Where(c.AccessReason.Length>0)
                        .GroupBy(c => c.CustID)
//                      .Select()
                        .ToList();
// (?) where to put the c.Max(cu=>cu.AccessDate) 

Question: How can I create the query to select the latest(the maximum AccessDate) record/object for each CustID?

question from:https://stackoverflow.com/questions/1704821/linq-to-sql-groupby-and-max-to-get-the-object-with-latest-date

Answer 1

I'm wondering if something like:

var custsLastAccess = db.CustAccesses   
                    .Where(c.AccessReason.Length>0)
                    .GroupBy(c => c.CustID)
                    .Select(grp => new {
                      grp.Key,
                      LastAccess = grp
                         .OrderByDescending(x => x.AccessDate)
                         .Select(x => x.AccessDate)
                         .FirstOrDefault()
                    }).ToList();

you could also try OrderBy() and Last()