I need to get the Average, rounded to 2 decimal points, of a difference of two dates.
Using Entity Framework Core I used the following:
var average = await games .Where(x => x.Finish.HasValue) .AverageAsync(x => Math.Round((x.Finish.Value - x.Start).TotalDays, 2));
This seems to be running in memory.
How can I calculate the average on the database (SQL)?
The problem is not the
Average, but the time span calculation - currently EF Core (2.x) does not support generic SQL translation of time span calculations.
For SqlServer though you could use some of the DateDiff methods introduced in EF Core 2.1. All they return
int values, so you can't use directly
DateDiffDay to get the equivalent of
TimeSpan.TotalDays, but you could use
DateDiffHour / 24d or
DateDiffMinute / (24d * 60) etc. to simulate it.
var average = await games .Where(x => x.Finish.HasValue) .AverageAsync(x => Math.Round(EF.Functions.DateDiffHour(x.Start, x.Finish.Value) / 24d, 2));
SELECT AVG(ROUND(DATEDIFF(HOUR, [x].[Start], [x].[Finish]) / 24.0E0, 2)) FROM [Game] AS [x] WHERE [x].[Finish] IS NOT NULL