Get Average of TimeSpans with EF Core

Question

I'm using EF Core 7 with Postgres. I need to calculate some stats:

public record Job(string Name, DateTime Started, DateTime Stopped);
public record Stats(TimeSpan Min, TimeSpan Mean, TimeSpan Max);

// 

var stats = await _context
  .Jobs
  .GroupBy(x => x.Name)
  .Select(x => new Stats(
    x.Min(y => y.Stopped - y.Started), 
    new TimeSpan((long)x.Select(y => y.Stopped - y.Started).Select(x => x.Ticks).Average()), 
    x.Max(y => y.Stopped - y.Started)
  ))
  .ToListAsync();

The average subquery is weird-looking but works as a non-EF query. But EF refuses with:

InvalidOperationException: The LINQ expression 'RelationalGroupByShaperExpression:
...
.Average()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation

I think EF can't translate Ticks. I didn't find anything useful in EF.Functions.

I don't want to do this on the client, I want to do this on the database as the data set is large. Is that possible?

Answer 1

Other than client-side eval, one could use a provider-specific function (I am using Postgres):

var stats = await _context
  .Jobs
  .GroupBy(x => x.Name)
  .Select(x => new Stats(
    x.Min(y => y.Stopped - y.Started), 
    EF.Functions.Average(x.Select(y => y.Stopped - y.Started)),  // <---
    x.Max(y => y.Stopped - y.Started)
  ))
  .ToListAsync();

But then that property must be nullable:

public record Stats(TimeSpan Min, TimeSpan? Mean, TimeSpan Max);

The issue is Ticks is not (currently) supported. That was requested here, please upvote it.