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
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)
{
}
}