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?
Other than client-side eval, one could use a provider-specific function (I am using Postgres):
But then that property must be nullable:
The issue is
Ticks
is not (currently) supported. That was requested here, please upvote it.