get average Datetime from DB and subtract one Date from another

Question

• How to get an average time from an Datetime field via the Entity Framework ?
• How to subtract one Date from another ?

I am thinking of something like:

ObjectQuery<Visit> visits = myentitys.Visits;
            var uQuery =
            from visit in visits
            group visit by visit.ArrivalTime.Value.Day into g
            select new
            {
                 Day = g.Key,
                 Hours = g.Average(visit => (visit.LeaveTime.Value - visit.ArrivalTime.Value).TotalMinutes)
            };

to get the average residence time of an visitor grouped by Day.

Answer 1

I would rather get data from database, and then do average function in memory. Altough I'm not sure what could be impact on perfomances...

            List<Visit> visits = myentitys.Visits.ToList();//Get the Visits entities you need in memory,
                                           //of course,you can filter it here
            var uQuery =
            from visit in visits
            group visit by visit.ArrivalTime.Value.Day into g
            select new
            {
                Day = g.Key,
                Hours = g.Average(visit => (visit.LeaveTime.Value - visit.ArrivalTime.Value).TotalMinutes)
            };

This kind of arithmetic operation is not possible to translate in sql query (like exception says: DbArithmeticExpression arguments must have a numeric common type), but it is possible with objects in memory.

Hope this helps.

Answer 2

Here's how you do this, assuming your backing store is SQL Server. By the way, I corrected what I assume was a typo -- you assign a total number of minutes into a field called Hours. I renamed the field to Minutes for the purposes of future audiences.

ObjectQuery<Visit> visits = myentitys.Visits;
var uQuery = from visit in visits
             group visit by visit.ArrivalTime.Value.Day into g
             select new
             {
                 Day = g.Key,
                 Minutes = g.Average(visit => System.Data.Objects.SqlClient.SqlFunctions.DateDiff("m", visit.LeaveTime.Value, visit.ArrivalTime.Value))
             };

It is disappointing that LINQ to Entities doesn't support intrinsic conversion of DateTime subtraction into a DateDiff and then yield a TimeSpan object as a result.