Use LINQ to group a sequence by date with no gaps

Posted by Codesleuth on Stack Overflow See other posts from Stack Overflow or by Codesleuth
Published on 2010-06-09T14:15:44Z Indexed on 2010/06/09 16:02 UTC
Read the original article Hit count: 277

Filed under:
|
|

I'm trying to select a subgroup of a list where items have contiguous dates, e.g.

ID  StaffID  Type  Title              ActivityDate
--  -------  ----  -----------------  ------------
 1       41     1  Doctors              07/06/2010
 2       41     0  Meeting with John    08/06/2010
 3       41     0  Meeting Continues    09/06/2010
 4       41     0  Meeting Continues    10/06/2010
 5       41     3  Annual Leave         11/06/2010
 6       41     0  Meeting Continues    14/06/2010

I'm using a pivot point each time, so take the example pivot item as 3, I'd like to get the following resulting contiguous events around the pivot:

ID  StaffID  Type  Title              ActivityDate
--  -------  ----  -----------------  ------------
 2       41     0  Meeting with John    08/06/2010
 3       41     0  Meeting Continues    09/06/2010
 4       41     0  Meeting Continues    10/06/2010

My current implementation is a laborious "walk" into the past, then into the future, to build the list:

var orderedEvents = activities.OrderBy(a => a.ActivityDate).ToArray();

// Walk into the past until a gap is found
var preceedingEvents = orderedEvents.TakeWhile(a => a.ID != activity.ID);
DateTime dayBefore;
var previousEvent = activity;
while (previousEvent != null)
{
    dayBefore = previousEvent.ActivityDate.AddDays(-1).Date;
    previousEvent = preceedingEvents.TakeWhile(a => a.ID != previousEvent.ID).LastOrDefault();
    if (previousEvent != null)
    {
        if (previousEvent.ActivityDate.Date == dayBefore)
            relatedActivities.Insert(0, previousEvent);
        else
            previousEvent = null;
    }
}


// Walk into the future until a gap is found
var followingEvents = orderedEvents.SkipWhile(a => a.ID != activity.ID);
DateTime dayAfter;
var nextEvent = activity;
while (nextEvent != null)
{
    dayAfter = nextEvent.ActivityDate.AddDays(1).Date;
    nextEvent = followingEvents.SkipWhile(a => a.ID != nextEvent.ID).Skip(1).FirstOrDefault();
    if (nextEvent != null)
    {
        if (nextEvent.ActivityDate.Date == dayAfter)
            relatedActivities.Add(nextEvent);
        else
            nextEvent = null;
    }
}

The list relatedActivities should then contain the contiguous events, in order.

Is there a better way (maybe using LINQ) for this?

I had an idea of using .Aggregate() but couldn't think how to get the aggregate to break out when it finds a gap in the sequence.

© Stack Overflow or respective owner

Related posts about c#

Related posts about LINQ