Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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)  

Custs Layout

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

like image 707
p.campbell Avatar asked Nov 10 '09 00:11

p.campbell


People also ask

How do I get the latest date in C#?

MaxDate(r=>r. ExpirationDate. Value); c#

How do I find the next record in LINQ?

First(); var next = items . OrderBy(item => item.Id) . First(item => item.Id > currentId); var next = items .


1 Answers

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

like image 139
Marc Gravell Avatar answered Oct 07 '22 16:10

Marc Gravell