Styjun

Generally i have report data for some date range and i want to insert new, and delete old one for the same period.

I want to use column of type daterange in postgresql db.
I am using newest version of EF for postgre.
I want to be able to map my field to some property.
I want to be able select, remove and insert new based on this column.

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 understand that it is happening because the mapping works that way: NpgsqlRange => tsrange

I tried to use:
NpgsqlRange
but got next exception:
The property 'DBTable1.DateRangeNoda' is of type 'NpgsqlRange' which is not supported by current database provider – but shouldn’t this be already working ?
So my question is: Is there way to work in .net core using EF with column of type daterange?How?

using (var context = new postgresContext())
 
 var d = new DBTable1()Text = "aaa" ;
 d.DateRange1 = new NpgsqlRange<DateTime>(DateTime.Today, DateTime.Today);

 //d.DateRangeNoda = new NpgsqlRange<LocalDate> 
 // (LocalDate.FromDateTime(DateTime.Today), 
 // LocalDate.FromDateTime(DateTime.Today) );
 //efNpgsqlDate> not supported by provider

 context.dbTable1.Add(d); 

 context.SaveChanges();


public partial class DBTable1 

 [Column("id")]
 public int Id get; set; 

 public NpgsqlRange<DateTime> DateRange0 get; set; 

 public NpgsqlRange<LocalDate> DateRangeNoda get; set; 


protected override void OnModelCreating(ModelBuilder modelBuilder)

 modelBuilder.Entity<DBTable1>(entity =>
 
 entity.ToTable("test1");

 entity.Property(e => e.DateRange1).HasColumnName("daterange1"); 

 entity.Property(e => e.DateRangeNoda).HasColumnName("daterange"); 

 ); 

For now i end up using date column with FirstOfTheRange date :(

Thank you for your help,

There seems to be a bit of confusion in your code sample above - you are accessing property DateRange1 whereas the DBTable1 class seems to contain DateRange0.

Npgsql supports two ways of interacting with date/time types: the built-in BCL types (e.g. DateTime) and the NodaTime library. The built-in DateTime type is supported, but is mapped by default to PostgreSQL timestamp rather than date, since it has a time components. Therefore, a property of type NpgsqlRange<DateTime> will cause Npgsql to create a tsrange column (range of timestamp), rather than a daterange). It's possible to explicitly specify daterange as the column type, but you will encounter some subtle problems with this (see this comment).

If using NodaTime is OK, then things should work better, since NodaTime has a date-only type - LocalDate - so NpgsqlRange<LocalDate> automatically maps to daterange. To do this you will need to use the NodaTime plugin, as specified in the docs.

Here's a full code sample for using a date range with NodaTime, including insertion and query:

class Program

 static void Main(string[] args)
 
 using (var ctx = new BlogContext())
 
 ctx.Database.EnsureDeleted();
 ctx.Database.EnsureCreated();

 ctx.Blogs.Add(new Blog Duration = new NpgsqlRange<LocalDate>(new LocalDate(2011, 1, 1), new LocalDate(2011, 1, 3)) );
 ctx.SaveChanges();

 var x = ctx.Blogs.FirstOrDefault(b => b.Duration.Contains(new LocalDate(2011, 1, 2)));
 
 


public class BlogContext : DbContext

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 => optionsBuilder.UseNpgsql("<connection string>", o => o.UseNodaTime());

 public DbSet<Blog> Blogs get; set; 


public class Blog

 public int Id get; set; 
 public NpgsqlRange<LocalDate> Duration get; set; 

Problem was using wrong package for NodaTime:

<!--Wrong one <PackageReference Include="Npgsql.NodaTime" Version="4.0.5" /> --> <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.2.0" /> 
<!--Good one --> 
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.NodaTime" Version="2.2.0" />