Condense event list with LINQ

Question

I have a list of log entries in Audit class

public class Audit
{
    public DateTime TimeStamp { get; set; }
    public string User { get; set; }
    public string AuditType { get; set; }
}

so a list might look like this;

20140206 11:29:20   Owen   Open   
20140206 11:29:21   Owen   Close
20140206 11:31:20   Owen   Open
20140206 11:32:20   Owen   Close
20140206 11:42:20   Owen   Open
20140206 11:50:00   Owen   Acknowledge

This gives us gaps of 1 second, 1 minute, and 40 seconds. So the longest time it was open was the middle pair for 1 minute, then it was acknowledged at 11:50. I'm looking for the date pair where it was open longes, in this case 1 min.

I know I can process the list in sequentially and find the biggest gap using a TimeSpan but I figure there is a neat LINQ way to do it maybe with groups?

UPDATE It's not pretty, but this is the logic in really expanded walk

var audits = notice.AuditEntries.Where(a => a.User == user);

DateTime? currentOpen = null;
DateTime? bestOpen = null;
DateTime? bestClose = null;
foreach (var audit in audits)
{
    if (audit.AuditType == "Open")
    {
       if (currentOpen.HasValue) continue;
       currentOpen = audit.TimeStamp;
    }
    if (audit.AuditType == "Close" || audit.AuditType == "Acknowledge")
    {
       if (currentOpen.HasValue)
       {
          DateTime? currentClose = audit.TimeStamp;
          if (!bestOpen.HasValue)
          {
             bestOpen = currentOpen;
             bestClose = currentClose;
          }
          else
          {
             if (bestClose.Value.Subtract(bestOpen.Value) >                                               currentClose.Value.Subtract(currentOpen.Value))
             {
                bestOpen = currentOpen;
                bestClose = currentClose;
             }
           }
         currentOpen = null;
         }
      }
  }

Answer 1

Not tested but should work:

var auditGaps = audits
    .GroupBy(a => a.User)
    .Select(g => new
    {
        User = g.Key,
        MinOpen = g.Where(a => a.AuditType == "Open").Select(a=> a.TimeStamp).Min(),
        MaxClosed = g.Where(a => a.AuditType == "Close").Select(a=> a.TimeStamp).Max(),
        MaxAcknowledge = g.Where(a => a.AuditType == "Acknowledge").Select(a=> a.TimeStamp).Max()
    })
    .Select(x => new
    {
        x.User,
        LargestOpenCloseGap = x.MaxClosed - x.MinOpen,
        LargestOpenAcknowledgeGap = x.MaxAcknowledge - x.MinOpen
    });

Answer 2

I think this will do the trick:

IEnumerable<Audit> audits = ...

var longestAuditsByUser = audits.OrderBy(a => a.Timestamp)
    // group by user, since presumably we don't want to match an open from one user with a close from another
    .GroupBy(a => a.User)
    .Select(userAudits => 
    {
        // first, align each audit entry with it's index within the entries for the user
        var indexedAudits = userAudits.Select((audit, index) => new { audit, index });
        // create separate sequences for open and close/ack entries
        var starts = indexedAudits.Where(t => t.audit.AuditType == "Open");
        var ends = indexedAudits.Where(t => t.audit.AuditType == "Close" || t.audit.AuditType == "Acknowledge");

        // find the "transactions" by joining starts to ends where start.index = end.index - 1
        var pairings = starts.Join(ends, s => s.index, e => e.index - 1, (start, end) => new { start, end });

        // find the longest such pairing with Max(). This will throw if no pairings were
        // found. If that can happen, consider changing this Select() to SelectMany()
        // and returning pairings.OrderByDescending(time).Take(1)
        var longestPairingTime = pairings.Max(t => t.end.Timestamp - t.start.Timestamp);
        return new { user = userAudits.Key, time = longestPairingTime };
    });

// now that we've found the longest time for each user, we can easily find the longest
// overall time as well
var longestOverall = longestAuditsByUser.Max(t => t.time);