#StackBounty: #c# #postgresql #entity-framework-core #nodatime Entity Framework Core NodaTime Sum Duration

Bounty: 50

How do you write the following sql in EF Core

select r."Date", sum(r."DurationActual")
from public."Reports" r
group by r."Date"

We’ve got the following Model (mwe)

public class Report 
    public LocalDate Date { get; set; }
    public Duration DurationActual { get; set; }  ​

I tried the following:

await dbContext.Reports
    .GroupBy(r => r.Date)
    .Select(g => new
      SummedDurationActual = g.Sum(r => r.DurationActual),

but this does not compile since Sum only works for int,double,float,Nullable<int>, etc.

I also tried to sum the total hours

await dbContext.Reports
    .GroupBy(r => r.Date)
    .Select(g => new
      SummedDurationActual = g.Sum(r => r.DurationActual.TotalHours),

which compiles but cannot be translated by EF with the following error

System.InvalidOperationException: The LINQ expression 'GroupByShaperExpression:
KeySelector: r.Date, 
    EntityType: Report
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False

    .Sum(r => r.DurationActual.TotalHours)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', ....

Of course I can enumerate it earlier but this is not efficient.

Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.