Entity Framework: Order by and then group by

Question

I am using Entity Framework Code First and have the following POCO that represents a table in my database.

public class LogEntry
{
    public int Id {get; set;}
    public DateTimeOffset TimeStamp {get;set;}
    public string Message {get; set;}
    public string CorrelationId {get; set;}
}

The CorrelationId is not unique. There will typically be multiple records in the table with the same CorrelationId and this field is used to track what log entries correspond to what request.

I then have another object that lets me group these log entries by CorrelationId. This object does not map back to any tables in the DB.

public class AuditTrail
{
    public string CorrelationId {get; set;}
    public DateTimeOffset FirstEvent {get; set;}
    public List<LogEntry> LogEntries {get; set;}
}

I want to be able to populate a list of AuditTrail objects. The catch is that I want them to be sorted so that the newest Audit Trail records are at the top. I am also doing paging so I need the order by to happen before the group by so that the correct records get returned. i.e. I don't want to get the results and then sort through them. The sort needs to happen before the data is returned.

I have tried some queries and have ended up with this:

var audits = from a in context.LogEntries
                             group a by a.CorrelationId into grp
                             select grp.OrderByDescending(g => g.TimeStamp);

This gives me an IQueryable<IOrderedEnumerable<LogEntry>> back that I iterate through to build my AuditTrail objects. The problem is that the records are only sorted within the groups. For example I will get back an AuditTrail for yesterday followed by one from a week ago followed by one from today but within the LogEntries List all those entries are sorted. What I want is for the AuditTrails to come back in descending order based on the TimeStamp column so that new AuditTrails are displayed at the top of my table on the UI.

I have also tried this query (as per Entity Framework skip take by group by):

var audits = context.LogEntries.GroupBy(i => i.CorrelationId)
                               .Select(g => g.FirstOrDefault())
                               .OrderBy(i => i.TimeStamp)
                               .ToList();

This only returns the first LogEntry for each Correlation Id when I want them all back grouped by Correlation Id.

Answer 1

I think what you are looking for is something like this:

var audits = (from a in context.LogEntries
             group a by a.CorrelationId into grp
             let logentries = grp.OrderByDescending( g => g.TimeStamp)
             select   new AuditTrail
                      {
                          CorrelationId = grp.Key,
                          FirstEvent = logentries.First().TimeStamp,
                          LogEntries = logentries.ToList()
                      }).OrderByDescending( at => at.FirstEvent);

Answer 2

var audits = (from a in context.LogEntries
             group a by a.CorrelationId into grp
             select new AuditTrail
             {
               CorrelationId = grp.Key,
               FirstEvent = grp.OrderBy(g=>g.TimeStamp).First().TimeStamp,
               LogEntries = grp 
             }).OrderByDescending(a=>a.FirstEvent)