如何为自定义Postgresql范围类型创建类型映射

Question

我在项目中使用了Dapper和PostgreSQL。我的模式中有一些自定义枚举、复合和范围类型，例如：

create type timerange as range (
subtype = time,
subtype_diff = time_subtype_diff
);

我在myproject.working_time_intervals表中使用了这个范围，它与模型类WorkingInterval:相连

create table working_time_intervals
(
id              serial                   not null
constraint working_time_intervals_pk
primary key,
day_of_week     myproject.day_of_week not null,
begin_end_range myproject.timerange
);

public class WorkingInterval
{
public int Id { get; set; }
public DayOfWeek DayOfWeek { get; set; }
public NpgsqlRange<TimeSpan> BeginEndRange { get; set; }
}

另外，当我尝试使用这个类并在数据库中插入数据并获得结果时，我会捕获异常

var result = await connection.QueryAsync<WorkingInterval>(
@"insert into myproject.working_time_intervals(day_of_week, begin_end_range) 
select unnest(@DayOfWeeks), myproject.timerange(unnest(@Begins)::time, unnest(@Ends)::time, '[]')
returning *",
new
{
DayOfWeeks = intervals.Select(i => i.DayOfWeek).ToArray(),
Begins = intervals.Select(i => i.Begin).ToArray(),
Ends = intervals.Select(i => i.End).ToArray()
});

System.NotSupportedException: The CLR array type NpgsqlTypes.NpgsqlRange`1[System.TimeSpan][] isn't supported by Npgsql or your PostgreSQL. If you wish to map it to an  PostgreSQL composite type array you need to register it before usage, please refer to the documentation.rn   at Npgsql.TypeMapping.ConnectorTypeMapper.GetByClrType(Type type)rn   at Npgsql.

如何创建时间范围的映射？

Answer 1

这里的问题是，.NET TimeSpan在默认情况下没有映射到PostgreSQLtime，而是映射到interval(请参阅文档中的映射表(；这意味着默认情况下NpgsqlRange<TimeSpan>也不会映射到PostgreSQLtimerange。

所有这些仍然可以工作，但您需要明确地告诉Npgsql要发送哪种类型。在不使用Dapper的情况下，以下内容应该可以很好地工作：

cmd = new NpgsqlCommand(@"INSERT INTO foo (range) VALUES (@range)", conn);
cmd.Parameters.AddWithValue("range", NpgsqlDbType.Range | NpgsqlDbType.Time, new NpgsqlRange<TimeSpan>(TimeSpan.FromHours(1), TimeSpan.FromHours(2)));
cmd.ExecuteNonQuery();
cmd = new NpgsqlCommand(@"INSERT INTO foo (ranges) VALUES (@ranges)", conn);
cmd.Parameters.AddWithValue("ranges", NpgsqlDbType.Range | NpgsqlDbType.Array | NpgsqlDbType.Time, new[]
{
new NpgsqlRange<TimeSpan>(TimeSpan.FromHours(1), TimeSpan.FromHours(2)),
new NpgsqlRange<TimeSpan>(TimeSpan.FromHours(3), TimeSpan.FromHours(4))
});
cmd.ExecuteNonQuery();

使用Dapper，您将需要一些自定义来设置NpgsqlDbType，这个问题应该会有所帮助。