EF.Functions.Sum throws

[aradalvand]

Here's a simple repro: https://github.com/aradalvand/efcore-pgsql-bug

Let me know if I'm missing something here because this seems very basic.

I used to have a pretty simple expression to get the sum of a related collection's TimeSpan properties:

var course = db.Courses.Select(c => new
{
    TotalDuration = TimeSpan.FromSeconds(c.Videos.Sum(v => v.Duration.TotalSeconds)),
}).FirstOrDefault();

This worked (still does of course), generating the following SQL:

SELECT (
     SELECT COALESCE(sum(date_part('epoch', v."Duration")), 0.0)
     FROM "Videos" AS v
     WHERE c."Id" = v."CourseId")
  FROM "Courses" AS c
  LIMIT 1

Now, in EF7, with the introduction of provider-specific aggregate functions, I wanted to change the code above to use the EF.Functions.Sum method instead — to avoid converting the column into seconds and back:

var course = db.Courses.Select(c => new
{
    TotalDuration = EF.Functions.Sum(c.Videos.Select(v => v.Duration)),
}).FirstOrDefault();

This looks like the most basic usage of this method.
But to my surprise I found out it actually throws:

Unhandled exception. System.InvalidOperationException: The 'Sum' method is not supported because the query has switched to client-evaluation. This usually happens when the arguments to the method cannot be translated to server. Rewrite the query to avoid client evaluation of arguments so that method can be translated to server.
   at Microsoft.EntityFrameworkCore.NpgsqlAggregateDbFunctionsExtensions.Sum(DbFunctions _, IEnumerable`1 input)
   at lambda_method94(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at lambda_method95(Closure , QueryContext )
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
   at Program.<Main>$(String[] args) in /home/arad/other/efcore-pgsql-bug/Program.cs:line 3

What's wrong here?!

Thank you in advance.

[roji]

Does it work if the thing inside the Sum is a direct property on c rather than on a related entity (Videos)?

[aradalvand]

@roji Thank you for the response.

EF.Functions.Sum receives an IEnumerable of TimeSpan, as you know. So how would you pass a single property there? Unless the property is an array of TimeSpan itself (I haven't tested that I don't know if it works). But are you saying that's its only use case?

I did provide a very simple repro by the way if that helps.

[aradalvand]

Update: I just tested this:

var course = db.Videos.GroupBy(_ => true).Select(g => new
{
    TotalDuration = EF.Functions.Sum(g.Select(v => v.Duration)),
}).FirstOrDefault();

And it seems to work, so apparently EF.Functions.Sum only supports this specific kind of scenario (i.e. GroupBy), why though? That seems strange to me.

Shouldn't something like EF.Functions.Sum(c.Videos.Select(v => v.Duration)) be possible too?! That's what I was expecting.

[roji]

I'll look into this (but it will probably take some time).

[roji]

Duplicate of dotnet/efcore#29200

[roji]

Took a deeper look at this, and it's unfortunately a current limitation of EF (dotnet/efcore#29200) - custom aggregate functions can only be used with GroupBy. For now, until this limitation is lifted, rewrite your query via GroupBy as you've done above.

[roji]

(and please upvote dotnet/efcore#29200!)