Entity Framework Core NodaTime Sum Duration

Question

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
    {
      g.Key,
      SummedDurationActual = g.Sum(r => r.DurationActual),
    })
    .ToListAsync(cancellationToken);

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
    {
      g.Key,
      SummedDurationActual = g.Sum(r => r.DurationActual.TotalHours),
    })
    .ToListAsync(cancellationToken)

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

System.InvalidOperationException: The LINQ expression 'GroupByShaperExpression:
KeySelector: r.Date, 
ElementSelector:EntityShaperExpression: 
    EntityType: Report
    ValueBufferExpression: 
        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.

To clarify it a bit furhter: We use Npgsql.EntityFrameworkCore.PostgreSQL together with Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime to establish the connection. Duration is a DataType from NodaTime to represent something similar like TimeSpan. Duration gets mapped to a interval on the Database side.

We heavily use unit-tests which use an InMemoryDatabase (UseInMemoryDatabase) So the solution should work with both, PsQl and InMemory.

For those unfamiliar with the EF-Core integration of NodaTime:

You Add the UseNodaTime() method call to the configuration, example:

services.AddDbContext<AppIdentityDbContext>(
    options => options
                      .UseLazyLoadingProxies()
                      .UseNpgsql(configuration.GetConnectionString("DbConnection"),
                            o => o
                                 .MigrationsAssembly(Assembly.GetAssembly(typeof(DependencyInjection))!.FullName)
                                 .UseNodaTime()
                        )

this adds a type-mapping for the NodaTime types

.AddMapping(new NpgsqlTypeMappingBuilder
                {
                    PgTypeName = "interval",
                    NpgsqlDbType = NpgsqlDbType.Interval,
                    ClrTypes = new[] { typeof(Period), typeof(Duration), typeof(TimeSpan), typeof(NpgsqlTimeSpan) },
                    TypeHandlerFactory = new IntervalHandlerFactory()
                }.Build()

I don't know every detail but I think this adds a ValueConverter. Further information: https://www.npgsql.org/efcore/mapping/nodatime.html

Answer 1

The answer from @mohamed-amazirh helped me to steer in the right direction. It is not possible to change to Period (Since it simply isn't a period, it's a duration). I ended up writing a IDbContextOptionsExtension to fulfill my needs.

Full code here:

public class NpgsqlNodaTimeDurationOptionsExtension : IDbContextOptionsExtension
{
    private class ExtInfo : DbContextOptionsExtensionInfo
    {
        public ExtInfo(IDbContextOptionsExtension extension) : base(extension) { }

        public override long GetServiceProviderHashCode()
        {
            return 0;
        }

        public override void PopulateDebugInfo(IDictionary<string, string> debugInfo)
        {
            return;
        }

        public override bool IsDatabaseProvider => false;
        public override string LogFragment => "using NodaTimeDurationExt ";
    }

    public NpgsqlNodaTimeDurationOptionsExtension()
    {
        Info = new ExtInfo(this);
    }

    public void ApplyServices(IServiceCollection services)
    {
        new EntityFrameworkRelationalServicesBuilder(services)
            .TryAddProviderSpecificServices(x => x.TryAddSingletonEnumerable<IMemberTranslatorPlugin, NpgsqlNodaTimeDurationMemberTranslatorPlugin>());
    }

    public void Validate(IDbContextOptions options) { }

    public DbContextOptionsExtensionInfo Info { get; set; }
}

public class NpgsqlNodaTimeDurationMemberTranslatorPlugin: IMemberTranslatorPlugin
{
    public NpgsqlNodaTimeDurationMemberTranslatorPlugin(ISqlExpressionFactory sqlExpressionFactory)
    {
        Translators = new IMemberTranslator[]
        {
            new NpgsqlNodaTimeDurationMemberTranslator(sqlExpressionFactory),
        };
    }

    public IEnumerable<IMemberTranslator> Translators { get; set; }
}

public class NpgsqlNodaTimeDurationMemberTranslator : IMemberTranslator
{
    private readonly ISqlExpressionFactory sqlExpressionFactory;

    public NpgsqlNodaTimeDurationMemberTranslator(ISqlExpressionFactory sqlExpressionFactory)
    {
        this.sqlExpressionFactory = sqlExpressionFactory;
    }

    public SqlExpression Translate(SqlExpression instance, MemberInfo member, Type returnType, IDiagnosticsLogger<DbLoggerCategory.Query> logger)
    {
        var declaringType = member.DeclaringType;
        if (instance is not null
            && declaringType == typeof(Duration))
        {
            return TranslateDuration(instance, member, returnType);
        }

        return null;
    }

    private SqlExpression? TranslateDuration(SqlExpression instance, MemberInfo member, Type returnType)
    {
        return member.Name switch
        {
            nameof(Duration.TotalHours) => sqlExpressionFactory
                .Divide(sqlExpressionFactory
                        .Function("DATE_PART",
                            new[]
                            {
                                sqlExpressionFactory.Constant("EPOCH"),
                                instance,
                            },
                            true, new[] { true, true },
                            typeof(double)
                        ),
                    sqlExpressionFactory.Constant(3600)
                ),
            _ => null,
        };
    }
}

To use it, I had to add it the same way NodaTime did it:

services.AddDbContext<Cockpit2DbContext>(options =>
    {
        options
            .UseLazyLoadingProxies()
            .UseNpgsql(configuration.GetConnectionString("Cockpit2DbContext"),
                o =>
                {
                    o.UseNodaTime();
                    var coreOptionsBuilder = ((IRelationalDbContextOptionsBuilderInfrastructure) o).OptionsBuilder;
                    var ext = coreOptionsBuilder.Options.FindExtension<NpgsqlNodaTimeDurationOptionsExtension>() ?? new NpgsqlNodaTimeDurationOptionsExtension();
                    ((IDbContextOptionsBuilderInfrastructure) coreOptionsBuilder).AddOrUpdateExtension(ext);
                })
            .ConfigureWarnings(w => w.Ignore(RelationalEventId.MultipleCollectionIncludeWarning));
    }
);
            

Answer 2

Looking at Npgsql.EntityFrameworkCore.PostgreSQL's source code here, you can see that it can't translate Duration's members. The method Translate will return null if the member used belongs to an object whose type is not LocalDateTime, LocalDate, LocalTime or Period. In your case the member used is TotalHours and it belongs to an object whose type is Duration.

So your second example can be made to work if you change DurationActual's type from Duration to Period (TimeSpan will also work).

Still, Period's member TotalHours also can't be translated (see the code here for the full list of the members that can be translated).

So you have to compute the value yourself with something like this :

 await dbContext.Reports
                .GroupBy(r => r.Date)
                .Select(g => new
                {
                    g.Key,
                    SummedDurationActual = g.Sum(r => r.DurationActual.Hours + ((double)r.DurationActual.Minutes / 60) + ((double)r.DurationActual.Seconds / 3600)),
                })
                .ToListAsync(cancellationToken)

If changing DurationActual's type is not an option, you can open an issue with Npgsql developers to add the necessary translations. They suggest doing so in their documentation:

Note that the plugin is far from covering all translations. If a translation you need is missing, please open an issue to request for it.