Efcore.pg: NpgsqlRange<DateTime>.Contains not working when filtering

[smblee]

Hello,

I have the following scenario and error:

```c#
MyEntity.cs

NpgsqlRange DateRange { get; set; }

```c#
MainController.cs

var toAdd = new MyEntity {
  DateRange = new NpgsqlRange<DateTime>(DateTime.MinValue, DateTime.MaxValue)
};

dbContext.MyDbSet.Add(toAdd);
dbContext.SaveChanges(); // successful!

dbContext.MyDbSet.Where(o => o.DateRange.Contains(DateTime.Now)).ToList(); // Throws PostgresException: operator does not exist: daterange @> timestamp with time zone

I am following the example shown here:

http://www.npgsql.org/efcore/mapping/range.html

Anyone else having this problem?

EntityFrameworkCore 2.1.1
EntityFrameworkCore.Design 2.1.1
Npgsql.EntityFrameworkCore.PostgresSQL 2.1.0

[austindrenski]

@smblee Thanks for opening this issue. It looks like this is related to the default type mappings and how the range operators are currently translated.

Currently, NpgsqlRange<DateTime> is mapped to daterange and DateTime.Now is translated to timestamp not date which is the element type of daterange.

https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/blob/c752898ec356f457a82f4df404be76b5e6b84cd9/src/EFCore.PG/Query/ExpressionTranslators/Internal/NpgsqlDateTimeMemberTranslator.cs#L116-L123

I think the real issue is that the range operators don't cast the element-operand to the range's element type. This was intentionally avoided at the time, but it could make sense given this example.

We would need to see if casting would truncate and lead to an unexpected comparison result.

@roji Do you have any thoughts on this?

[roji]

Currently, NpgsqlRange is mapped to daterange and DateTime.Now is translated to timestamp not date which is the element type of daterange.

This doesn't seem right... In NpgsqlTypeMappingSource, NpgsqlRange<DateTime> is by default mapped to tsrange, not daterange... It would indeed be very inconsistent to map an element to one PostgreSQL type and its range to another... @smblee, are you using the fluent API to explicitly set the range's type to daterange?

More generally, this is once again the issue of not being able to explicitly specify a store type on a parameter/literal included in a query (EF.StoreType(value, storeType)), see the additional comment I just posted). This is an issue wherever we have a single CLR type mapped to two store types (e.g. CLR DateTime mapped to both date and timestamp), and it's on the EF Core side.

Fortunately NodaTime actually does have CLR LocalDate and LocalDateTime, allowing us to cleanly map everything without any ambiguity. Unfortunately I forgot to map the range types in the NodaTime plugin, opened #495 to track this for 2.1.1. This should be the best answer for this problem, at least until something like EFCore.StoreType() is implemented on the EF Core side.

There's nothing more to do on this issue so I'll close it, but I'm still interested in confirming that @smblee explicitly set the proerty's store type to daterange to get the above problem.

[roji]

FYI pushed a fix to #495 which maps the built-in ranges in the NodaTime plugin, and confirmed that NpgsqlRange<LocalDate>.Contains(x) translates correctly.

[smblee]

I do use Database First EntityFrameworkCore with fluent definition like below

entity.Property(e => e.DateRange).HasColumnName("daterange").HasColumnType("daterange")

I explicitly map to daterange as it is saved as daterange in the database.

Would using NodaTime solve this problem? Or... I can just make 2 columns and not use the range feature.

[roji]

Thanks for confirming. If you wait for 2.1.1 (to be released in a couple of days) you should be able to use the NodaTime plugin for this.

[mar111111]

Hello,
I read a lot of conversations in the NpgsqlRange issues, but still don't understand that can i do.

I want to use column of type daterange in postgresql db.
I am using newest version of EF for postgre:

I tried to map my context to :
public NpgsqlRange month_year { get; set; }
but got exception: column "month_year" is of type daterange but expression is of type tsrange
I tried to use , but got next exception:
The property 'DBTable1.DateRangeNoda' is of type 'NpgsqlRange' which is not supported by current database provider

So my question is: Is there way to work in .net core using EF with column of type daterange?How?

Thank you,

[roji]

@mar111111 for a basic DbContext that works with date ranges, you can use the following:

```c#
public class Blog
{
public int Id { get; set; }
public NpgsqlRange Duration { get; set; }
}

// This allows you to write queries such as the following:
var blogs = ctx.Blogs.Where(b => b.Duration.Contains(new DateTime(2001, 1, 1))).ToList();

// Producing the following SQL:
SELECT b."Id", b."Duration"
FROM blog AS b
WHERE (b."Duration" @> TIMESTAMP '2001-01-01 00:00:00') = TRUE
```

If you encounter more issues, please open a separate issue or post a question on stackoverflow, tagged npgsql.