daterange is mapped incorrectly

[uhfath]

By default NpgsqlRange<DateTime> seems to map to tsrange. But I need it to be daterange.

Here is my model:

public class ServiceRate
{
    [Key]
    public int Id { get; set; }

    [Column(TypeName = "daterange")]
    public NpgsqlRange<DateTime> Period { get; set; }
}

During migration this maps correctly:

migrationBuilder.CreateTable(
    name: "ServiceRates",
    columns: table => new
    {
        Id = table.Column<int>(type: "integer", nullable: false)
            .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityAlwaysColumn),
        Period = table.Column<NpgsqlRange<DateTime>>(type: "daterange", nullable: false),
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_ServiceRates", x => x.Id);
    });

But the issues arise when using it like this:

dbContext.ServiceRates.Add(new ServiceRate
{
    Period = new NpgsqlTypes.NpgsqlRange<DateTime>(
        new DateTime(2021, 06, 01),
        true,
        false,
        default,
        false,
        true),
});

This maps to (note the time part):

Executing DbCommand [Parameters=[@p0='[01.06.2021 0:00:00,)' (DbType = Object)], CommandType='Text', CommandTimeout='30']
INSERT INTO "ServiceRates" ("Period")
VALUES (@p0)
RETURNING "Id";

And as a result:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
 ---> Npgsql.PostgresException (0x80004005): 42804: column "Period" is of type daterange but expression is of type tstzrange

So how do I properly map my column to daterange? First thought was to use User-defined ranges but I'm not sure how to map to tsrange in other entities.

BTW, docs mention that this should be mapped to daterange already.

[roji]

I can see the issue, but which version of the provider are you using?

[uhfath]

Sorry, forgot to mentioned that. I'm using 5.0.10

[roji]

@uhfath thanks for flagging this - I've fixed this for 5.0.11. In the meantime, you can use the daily build version 5.0.11-ci.20211004T154222 (or above) from the patch nuget feed.

[uhfath]

@roji thanks for taking the time to look into this. Worked flawlessly. However, I've noticed that SQL command that got generated is exactly the same, but it worked this time. How is that? Or is it just some metadata that was passed along with a command that changed?

[roji]

@uhfath the problem was with the internal mapping inside the provider, which caused us to accidentally send a tstzrange (range of timestamp with time zone) instead of a daterange (range of date). The .NET type for both these is the same (NpgsqlRange<DateTime>), but on the PostgreSQL side these are two very different types which don't implicitly convert to one another.

BTW if you're interested in date ranges, #2016 will allow you to map NpgsqlRange<DateOnly> (DateOnly is a new type being introduced .NET 6, which cleanly represents only a date). And if you're open to using NodaTime (which I can't recommend enough), you will even be able to use DateInterval, which maps very nicely to PostgreSQL daterange.

You can take 6.0.0-rc.1 for a spin if you'd like - DateInterval already works there (but NpgsqlRange<DateOnly> doesn't yet work).

[uhfath]

Thanks for the info. Yeah, I'm waiting for DateOnly to see how it works out. As for NodaTime, it's being on my radar for already long time (pun intended), but just didn't have a chance to look at it closely enough. Perhaps later, but thanks for the suggestion.

[pokorsky]

I'm getting the same error using the latest patch 5.0.11-ci.20211004T154222. Is the explicit attribute [Column(TypeName = "daterange")] required? The doc doesn't mention it. Adding the attribute makes it work.

[roji]

@pokorsky I'm unable to reproduce this problem with the latest patch daily build (5.0.11-ci.20211004T154222) - with the code below, I can use NpgsqlRange<DateTime> just fine even without the explicit column type name:

Attempted repro

```c# await using var ctx = new BlogContext(); await ctx.Database.EnsureDeletedAsync(); await ctx.Database.EnsureCreatedAsync(); ctx.Blogs.Add(new Blog { Period = new(new(2020, 1, 1), new(2020, 1, 5)) }); await ctx.SaveChangesAsync(); public class BlogContext : DbContext { public DbSet Blogs { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) => optionsBuilder .UseNpgsql(@"Host=localhost;Username=test;Password=test") .LogTo(Console.WriteLine, LogLevel.Information) .EnableSensitiveDataLogging(); } public class Blog { public int Id { get; set; } public string Name { get; set; } // [Column(TypeName = "daterange")] public NpgsqlRange Period { get; set; } } ```