CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sqldatetime
Jacob Huggart
Jacob Huggart

Reputation: 673

LINQ Average TimeSpan?

I have a database table that contains entries for start time and end time of an event. I would like to take those entries and find the average length of an event. The subtraction is no problem. However, when I try to get the average value, the compiler complains.

Here is my current LINQ query:

public TimeSpan? getAverageTime()
    {
        return (from EventTime in db.EventTimes
                where EventTime.EventTimesID > 0
                select (EventTime.TimeEnd - EventTime.TimeStart)).Average();
    }

I also tried .Sum() and .Aggregate() so that I could have something to work with, but none are accepted.

What is the best method for accomplishing my goal?

Upvotes: 7

Views: 6471

Answers (3)

Joey Dias
Joey Dias

Reputation: 82

var avg=(from t in array select (t.endtime.Ticks-t-starttime.Ticks)).Average();
Console.Write(new DateTime((long)avg).TimeOfDay);

Upvotes: -2

EgorBo
EgorBo

Reputation: 6152

It seems that you have to use a SqlMethods.DateDiff in linq2sql statement (DATEDIFF function if SQL):

public int getAverageTime()
    {
        return (from EventTime in db.EventTimes
                where EventTime.EventTimesID > 0
                select (SqlMethods.DateDiffMinute(EventTime.TimeStart, EventTime.TimeEnd)).Average();
    }

Upvotes: 7

Jacob
Jacob

Reputation: 78920

Average doesn't work with TimeSpan values, which are what you get when you subtract two DateTime values. Try averaging (EventTime.TimeEnd - EventTime.TimeStart).TotalMilliseconds then converting that back to a TimeSpan afterward.

Upvotes: 6

Related Questions