Sum time intervals in Linq To SQL and C#

I need to sum the hours worked. I have 2 fields: startTime and endTime. Are "datetime" and "nullable"

I tried this but it returns error:

var timeWorked = res.AsEnumerable()
                    .Sum(f => f.endTime.HasValue ? f.endTime.Value : 0 - f.startTime .HasValue ? f.startTime .Value : 0);

Error: "Operator '-' cannot be applied to operands of type 'bool' and 'int'"

The above code is in the following context

 try
 {                           
     using (serviciosDBDataContext miDataContext = new serviciosDBDataContext("Data Source='xxxxxxx'"))
      {
          var res = (from p in miDataContext.works
                     select new
                      {
                           p.idWork,
                           p.status,
                           p.amount,
                           p.startTime,
                           p.endTime    
                      }).ToList();                                 
//HOURS WORKED

    var timeWorked = res.AsEnumerable()
                        .Sum(f => f.endTime.HasValue ? f.endTime.Value : 0 - f.startTime .HasValue ? f.startTime .Value : 0);       
      }    
 }
 catch (Exception)
 {
        MessageBox.Show(AppResources.errCargaEstad);
 }

Answers


You can use the GetValueOrDefault and Aggregate methods here:

var timeWorked = res.AsEnumerable().Aggregate(TimeSpan.Zero, (total, f) => total + (f.endTime.GetValueOrDefault() - f.startTime.GetValueOrDefault());

FYI in your example there's a precedence issue, ie the following gets evaluated:

f.endTime.HasValue ? f.endTime.Value : (0 - f.startTime.HasValue)

Also, there is no implicit conversion between an int and a DateTime, so the conditional ternary operator cannot succeed.

Update

You can access the TotalMilliseconds, TotalSeconds... properties of TimeSpan to use these values in a method requiring a numeric type, however if you are dealing with time periods, the TimeSpan struct was created specifically for this purpose and mas reasoning about code far easier IMO.

Update 2

Okay, based on your comment of hours worked, I'd suggest renaming the variable from timeWorked to hoursWorked since an int representing a time without a unit given in the name can be misunderstood. The property the you require is TotalHours. This returns a double, you can explicitly cast to an int, however there may be a loss of precision:

var hoursWorked = (int)res.Sum(f => (f.endTime.GetValueOrDefault() - f.startTime.GetValueOrDefault()).TotalHours);

Also, since you are calling ToList to make res a list this forces the result into memory, so the call to AsEnumerable is unnecessary.


Need Your Help

How to make a responsive RatingBar inside a sub-layout

android android-layout ratingbar

The activity itself uses a table layout, in order to avoid the fill_parent width for the RatingBar (which messes up the number of starts) I placed a LinearLayout inside the TableLayout, and placed ...

rewriting single url help

mod-rewrite url-rewriting friendly-url

I'm trying to rewrite the following url: