Entity Framework Core NodaTime grouping by date

Question

I'm using NodaTime and Entity Framework Core and now I'm trying to group my query by date, though I'm using Instant as field data type. As far as I've understood, using dateColumn.Date (when using DateTime) should work and transfer query to date_trunc('day', dateColumn), but how can I achieve that with NodaTime Instant type?

Problem I see is that Instant requires timezone to be passed in order to resolve the day, which would throw

Either rewrite the query in a form that can be translated...

Answer 1

If you need to perform operations with human calendar units (e.g. days, months...), then considering using LocalDateTime instead. The idea of Instant is precisely that it doesn't support human calendar operations etc.

Something like this should work:

_ = await ctx.Blogs.GroupBy(b => b.Creation.Date).CountAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql(@"...", o => o.UseNodaTime());
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
    public LocalDateTime Creation { get; set; }
}