主页 / coding / 问题 / 79310533
Accepted
Andrus
Asked: 2024-12-27 06:17:26 +0800 CST

如何将 tstzrange 传递给 postgres

  • 772

使用以下方法将 tstzrange 传递给 EF Core 中的 postgres

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();

抛出错误

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

如何将 tstzrange 传递给 Postgres？

代码位于 ASP.NET 9 MVC 控制器中

应用程序使用 Postgres 12 及更高版本的服务器以及最新的 Npgsql Entity Framework Core Provider

asp.net-mvc

1 个回答

  1. Best Answer

    作为PostgreSQL文档，tstzrange是一种内置范围类型https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN

    对于Npgsql，范围类型被映射到，NpgsqlRange<TElement>因为没有通用的 C# 类可以保存其值。https ://www.npgsql.org/doc/types/basic.html

    以下是解析和选择的示例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)
    {
    }
}
    • 0

相关问题