Perform calculations on a set using only LINQ

Question

I'd like to perform the following using only LINQ.

I have a list of time sheet entries with user's in and out times. The class looks like this:

public class TimeSheetLog
{
   public Guid EmployeeId { get; set; }
   public DateTime ClockInTimeStamp { get; set; }
   public DateTime ClockOutTimeStamp { get; set; }
}

I'm passing a List<TimeSheetLog>() which contains all logs from the beginning of the year to date.

I'm trying to calculate the total work time -- regardless of employee -- for the month of January. Please also notice that I have a function named GetTimeDifferenceInMinutes() which calculates the number of minutes between two date/time values.

Here's what I currently have but I feel the whole thing can be done using LINQ only.

public static int GetTotalTimeWorked(List<TimeSheetLog> logs, DateTime startDate, DateTime endDate)
{
   // I'm passing 1/1/2018 for startDate and 1/31/2018 for endDate to this function
   var totalTimeWorkedInMinutes = 0;

   var januaryLogs = logs.Where(x => x.ClockInTimeStamp >= startDate && 
   x.ClockOutTimeStamp <= endDate);

   foreach(var item in januaryLogs)
   {
      totalTimeWorkedInMinutes += GetTimeDifferenceInMinutes(item.ClockInTimeStamp, itemClockOutTimeStamp);
   }

   return totalTimeWorkedInMinutes;
}

score 2 · Accepted Answer · answered Feb 20 '18 at 19:41

2

var logsFilteredByDate = logs.Where(x => x.ClockInTimeStamp >= startDate &&
    x.ClockOutTimeStamp <= endDate);
var totalTimeWorkedInMinutes = logsFilteredByDate.Sum(x => 
    GetTimeDifferenceInMinutes(x.ClockInTimeStamp, x.ClockOutTimeStamp));

Or, to combine it all into one query, which is unnecessary and harder to read,

var totalTimeWorkedInMinutes = logs.Where(x => x.ClockInTimeStamp >= startDate &&
    x.ClockOutTimeStamp <= endDate)
    .Sum(x => 
        GetTimeDifferenceInMinutes(x.ClockInTimeStamp, x.ClockOutTimeStamp));

answered Feb 20 '18 at 19:41

Scott Hannen

27,588
3
45
62

This approach excludes any timesheet that partially overlaps the start or end dates, so some employees apparently aren't going to get paid in full. If you worked the graveyard on 1/31 starting at 10 pm, you should get paid for two hours in January and the rest in February. – John Wu Feb 20 '18 at 20:16
@JohnWu I'm just answering how to do the LINQ using the queries already provided. What you're pointing out is valid, but it's not the question. The question is how to take result of the first query and sum it using LINQ. That's a good comment for the OP, but it's not within the scope of the question. – Scott Hannen Feb 20 '18 at 20:19

score 0 · Answer 2 · answered Feb 20 '18 at 19:36

0

you need sum

var tot = januaryLogs.Sum(item=>GetTimeDifferenceInMinutes(item.ClockInTimeStamp, itemClockOutTimeStamp));

answered Feb 20 '18 at 19:36

pm100

48,078
23
82
145

score 0 · Answer 3 · answered Feb 20 '18 at 19:39

0

Couldn't you do the Where with a Sum and do DateTime Subtract in the Sum, so

decimal total = logs.Where(x => x.ClockInTimeStamp >= startDate && x.ClockOutTimeStamp <= endDate).Sum(x.ClockOutTimeStamp.Subtract(x.ClockInTimeStamp).TotalMinutes);

answered Feb 20 '18 at 19:39

Ryan Wilson

10,223
2
21
40

Faruq · Answer 4 · 2018-02-20T20:36:50.490

0

Another option is to use .Aggregate function.

public static int GetTotalTimeWorked(List<TimeSheetLog> logs, DateTime startDate, DateTime endDate)
{
    var totalTimeWorkedInMinutes = 0;

    return logs.Where(x => x.ClockInTimeStamp >= startDate && x.ClockOutTimeStamp <= endDate)
        .Aggregate(totalTimeWorkedInMinutes, (total, item) => total + GetTimeDifferenceInMinutes(item.ClockInTimeStamp, item.ClockOutTimeStamp));

}

edited Feb 20 '18 at 20:36

answered Feb 20 '18 at 19:45

Faruq

1,361
1
11
23

Though Aggregate is generally useful, the Sum method is an aggregator specifically for computing sums. – Eric Lippert Feb 20 '18 at 19:52

John Wu · Answer 5 · 2018-02-20T20:13:53.943

The problem seems easy until you realize that a time sheet can span months. So if someone clocked in on January 31st and clocked out on February 1st, you have to count partial timesheets, to do it right.

Here is my solution:

public static class ExtensionMethods
{
    static public double TotalMinutes(this IEnumerable<TimeSheetLog> input, DateTime startPeriod, DateTime endPeriod)
    {
        return TimeSpan.FromTicks
        (
            input
            .Where( a=>
                a.ClockOutTimeStamp >= startPeriod &&
                a.ClockInTimeStamp <= endPeriod
            )
            .Select( a=>
                Math.Min(a.ClockOutTimeStamp.Ticks, endPeriod.Ticks) - 
                Math.Max(a.ClockInTimeStamp.Ticks, startPeriod.Ticks)
            )
            .Sum()
        )
        .TotalMinutes;
    }
}

Logic:

Find all timesheets that overlap at least partially with the period of interest.
Compute the start time as either the clock in time or the period start time, whichever is later.
Compute the end time as either the clock out time or the period end time, whichever is earlier.
Take the difference of the start and end time as ticks. Sum() these.
To do all this math, we convert all the timestamps to Ticks, since you can't take a Max() of two DateTimes. We can add ticks up just fine, then convert the total back into minutes before returning.

Test program (notice the third timesheet spans both January and February):

public class Program
{
    static public List<TimeSheetLog> testData = new List<TimeSheetLog>
    {
        new TimeSheetLog
        {
            ClockInTimeStamp = DateTime.Parse("1/1/2018 9:00 am"),
            ClockOutTimeStamp = DateTime.Parse("1/1/2018 5:00 pm")

        },
        new TimeSheetLog
        {
            ClockInTimeStamp = DateTime.Parse("1/2/2018 9:00 am"),
            ClockOutTimeStamp = DateTime.Parse("1/2/2018 5:00 pm")

        },
        new TimeSheetLog
        {
            ClockInTimeStamp = DateTime.Parse("1/31/2018 6:00 pm"),
            ClockOutTimeStamp = DateTime.Parse("2/1/2018 9:00 am")

        },
        new TimeSheetLog
        {
            ClockInTimeStamp = DateTime.Parse("2/3/2018 9:00 am"),
            ClockOutTimeStamp = DateTime.Parse("2/3/2018 5:00 pm")

        }
    };


    public static void Main()
    {
        var startPeriod = new DateTime(2018, 1, 1);
        var endPeriod = new DateTime(2018, 1, 31, 23, 59, 59, 9999); 
        Console.WriteLine( testData.TotalMinutes(startPeriod, endPeriod).ToString("0.00") );
    }
}

Output:

1320.00

...which is correct.

See my code on DotNetFiddle

Perform calculations on a set using only LINQ

5 Answers5

Linked