How to do timestamp arithmetic in EF Core if NodaTime uses Duration and Postgres uses INTERVAL/Period?

Question

In my .NET Core 3.1 projects I'm currently moving my time-related code to NodaTime.

I'm having trouble translating some queries, because the difference between two Instants in NodaTime yields a Duration, while in Postgres the difference between two TIMESTAMPs yields an INTERVAL.

Npgsql maps an Instant to TIMESTAMP, but an INTERVAL is a Period.

Let's say I have an entity called "Visit" which has an ID, and two times: "arrived" and "left". (This is not my actual use-case, I have several entities with that problem, so this is a minimal example)

public class Visit
{
    public int Id { get; set; }
    public Instant Arrived { get; set; }
    public Instant Left { get; set; }
}

Now I want to find visits where the visitor stayed less than a given amount of time:

public async Task<List<Visit>> FindVisitsShorterThan(DbSet<Visit> dbSet, Duration duration)
{
    var visits =
        from visit in dbSet
        where (visit.Left - visit.Arrived) < duration
        select visit;
    return await visits.ToListAsync();
}

If I do this, Npgsql complains with something like System.InvalidCastException: Can't write CLR type NodaTime.Duration with handler type TimestampHandler - because durations are not supported.

On the other hand, if I convert the Duration to a Period, NodaTime complains because from its point of view, that difference is a Duration, and that cannot be compared with < to a Period.

I could do a couple of things, but none of them seem ideal:

• Change the entity and replace "Instant Left" with "Period Stay", so that I don't have to do that computation in the query. But this would only be for one specific query - what if I need "Left" for another query?
• Only use LocalDateTime instead of Instant. The difference is a Period, so that would work in both NodaTime and Postgres. But that way I'm changing the semantics of my entity - I wanted those times to be UTC timestamps for a reason. LocalDateTime essentially means "TZ unknown or stored elsewhere", which is simply not true.
• Revert to the BCL types and start fighting with time zones again.
• Split my entities into domain types with Instants and Npgsql-specific DTO types with LocalTime and do some mapping between them. Probably the best solution, but seems unnecessary.

Is there a better way?

Answer 1

This is indeed an unfortunate limitation in the current Npgsql EF support for NodaTime types.

The good news is that I've just implemented the above, along with some other NodaTime arithmetic translations - see this tracking issue. This will be included in the upcoming EF Core 5.0 release next week - just wait a few days and all should be well.