asp.net-mvcpostgresqlentity-framework-corenpgsql.net-9.0

How to pass tstzrange to postgres


Passing tstzrange to postgres in EF Core using

DateTime algus = DateTime.Parse("2024-11-25T00:00:00+02:00", CultureInfo.InvariantCulture).ToUniversalTime();
DateTime lopp = DateTime.Parse("2025-01-25T22:00:00+02:00", CultureInfo.InvariantCulture).ToUniversalTime();

await ctx.Database.SqlQuery<DateTime>($@"
select lower(tstzrange '[{algus},{lopp})') as ""Value""
").ToArrayAsync();

throws error

invalid input syntax for type timestamp with time zone: "@p0" at character 20
STATEMENT:  select tstzrange '[@p0,@p1)'

How to pass tstzrange to Postgres ?

Code is in ASP.NET 9 MVC controller

Application uses Postgres 12 and later server and latest Npgsql Entity Framework Core Provider


Solution

  • As PostgreSQL doc, tstzrange is a built-in range type https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN

    For Npgsql, range type is mapped to NpgsqlRange<TElement> since no common C# class can hold its value. https://www.npgsql.org/doc/types/basic.html

    The following is an example to parse and select tstzrange:

    using Microsoft.EntityFrameworkCore;
    using NpgsqlTypes;
    
    var options = new DbContextOptionsBuilder<ApplicationDbContext>()
        .UseNpgsql("Host=localhost:5433;Database=postgres;Username=postgres;Password=postgres")
        //.EnableSensitiveDataLogging()
        //.LogTo(Console.WriteLine, LogLevel.Trace)
        .Options;
    
    var ctx = new ApplicationDbContext(options);
    
    DateTime algus = DateTimeOffset.Parse("2024-11-25T00:00:00+02:00").UtcDateTime;
    DateTime lopp = DateTimeOffset.Parse("2025-01-25T22:00:00+02:00").UtcDateTime;
    
    var range = new NpgsqlRange<DateTime>(algus, lopp);
    
    var r1 = await ctx.Database.SqlQuery<NpgsqlRange<DateTime>>($"select {range} as \"Value\"").FirstAsync();
    var list = await ctx.Database.SqlQuery<NpgsqlRange<DateTime>>($"select {range}").ToListAsync();
    
    Console.WriteLine(r1.LowerBound);
    Console.WriteLine(r1.UpperBound);
    
    var r2 = list[0];
    
    Console.WriteLine(r2.LowerBound);
    Console.WriteLine(r2.UpperBound);
    
    
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {
        }
    }