Entity Framework: organize and then group

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;} } 

CorrelationId is not unique. Typically, a table will have multiple entries with the same CorrelationId, and this field is used to track which log entries match the query.

Then I have another object that allows me to group these log entries using CorrelationId. This object is not mapped to any tables in the database.

 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 trick is that I want them to be sorted so that the new Audit Trail entries are on top. I also do swap, so I need order in order to happen before the group in order to return the correct entries. that is, I do not want to get the results, and then sort them. The variety must occur before the data is returned.

I tried several queries and got the following:

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

This gives me the IQueryable<IOrderedEnumerable<LogEntry>> back, which I repeat to create AuditTrail objects. The problem is that entries are sorted only within groups. For example, I will return to AuditTrail yesterday, followed by one of the weeks ago, and then one of them, but in the LogEntries list all these entries are sorted. I want AuditTrails to be returned in descending order based on the TimeStamp column so that new AuditTrails appear at the top of my table in the user interface.

I also tried this query (according to Entity Framework, skip group capture on ):

 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 identifier when I want all of them to be grouped by correlation identifier.

Answer 1

I think you are looking for 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)