Linq Join and/or Filter on DateTime evaluation?

Question

I have a list of Time Clock events, the issue I am having is that I do not know whether the event is a punch in or punch out. My class looks like this.

private class TimeClock
{
    public string EmployeeID { get; set; }
    public DateTime ShiftDate { get; set; }
    public DateTime TimeStamp { get; set; }
}

Given a specific EmployeeID and ShiftDate I need to determine when they punched in and out. This could be done with a simple < evaluation.

var allShifts = (from p1 in punches
                 join p2 in punches 
                    on new { p1.EmployeeID, p1.ShiftDate } equals 
                       new { p2.EmployeeID, p2.ShiftDate }
                 where p1.TimeStamp < p2.TimeStamp
                 select new Shift
                 {
                      EmployeeID = p1.EmployeeID,
                      Hours = p2.TimeStamp.Subtract(p1.TimeStamp).TotalHours
                 }).ToList();

This works well but obviously breaks down if an employee punches in and out more than once in a day. In that event I would like to see two shifts returned, one for the first punch in/out and another for the second.

I appreciate the help. Derrick

Answer 1

If you adjust your Shift model slightly:

public class TimeClock
{
    public int EmployeeId { get; set; } 
    public int TimeClockId { get; set; }
    public DateTime TimeStamp { get; set; }
}

public class Shift
{
    public TimeClock PunchIn { get; set; }
    public TimeClock PunchOut { get; set; }
    public int EmployeeId { get; set; }

    public bool HasShiftEnded 
    { 
        get { return PunchIn != null && PunchOut != null; } 
    }

    public double? DurationInHours
    {
        get
        {
            if (HasShiftEnded)
                return (PunchOut.TimeStamp - PunchIn.TimeStamp).TotalHours;

            return null;
        }
    }
}

You can use the following LINQ query:

var shifts = punches
    .Where (x => x.EmployeeId == 1 )
    .OrderBy (x => x.TimeStamp)
    .Select ((x, i) => new { Index = i, Punch = x })
    .GroupBy (x => x.Index / 2)
    .Select (x => x.Select (v => v.Punch))
    .Select (x => new Shift
    {
        EmployeeId = x.ElementAt(0).EmployeeId,
        PunchIn = x.ElementAt(0),
        PunchOut = x.ElementAtOrDefault(1)
    });

Which does the following:

1. Filter the punches by employee id or any other criteria (eg shift date).
2. Order the filtered items in chronological order
3. Split this list up into many lists of two adjacent items.
4. For each list of two items - create a new Shift. Shift's without a PunchOut stamp are incomplete (or in progress).

Here is a complete LINQPad script you can try out.

Answer 2

You can group punches by employee and date. Then from each group you can select punch in and out pairs and calculate sum of total hours:

 from p in punches
 group p by new { p.EmployeeID, p.ShiftDate } into g
 let punch = g.First()
 select new Shift
 {
      EmployeeID = punch.EmployeeID,
      FirstName = punch.FirstName,
      LastName = punch.LastName,
      TimeClockID = punch.TimeClockID,
      Hours = g.OrderBy(p => p.TimeStamp)
               .Select((p,i) => new {Punch = p, Index = i})
               .GroupBy(x => x.Index / 2)
               .Select(inOut => new {
                   PunchInTime = inOut.First().Punch.TimeStamp, 
                   PunchOutTime = inOut.Last().Punch.TimeStamp 
               })
               .Sum(x => x.PunchOutTime.Subtract(x.PunchInTime).TotalHours)
  }).ToList();

Here is how selecting of in-out pairs works

• order records of employee for current date by timestamp
• project each punch in group on { Punch, Index } object with punch object and its index in ordered list of punches
• group these objects by index / 2 (thus index is an integer, then this division will produce same value for two consecutive punches) E.g. for indexes 0, 1, 2, 3, 4, 5 you will have grouping key values 0, 0, 1, 1, 2, 2
• from each group of two punches calculate sum of total hours (note, inOut is a grouping of two anonymous objects { Punch, Index })