Calculate Average in Database SQL

c# entity-framework-core sql-server

Question

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)?

1
4
4/1/2019 8:51:11 PM

Accepted Answer

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.

For instance,

var average = await games
    .Where(x => x.Finish.HasValue)
    .AverageAsync(x => Math.Round(EF.Functions.DateDiffHour(x.Start, x.Finish.Value) / 24d, 2));

translates to

  SELECT AVG(ROUND(DATEDIFF(HOUR, [x].[Start], [x].[Finish]) / 24.0E0, 2))
  FROM [Game] AS [x]
  WHERE [x].[Finish] IS NOT NULL
5
4/1/2019 8:48:07 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow