I use Dapper and PostgreSQL in my project. And I have some custom enums, composite and range types in my schema, for example:
create type timerange as range (
subtype = time,
subtype_diff = time_subtype_diff
);
And I use this range in table myproject.working_time_intervals, which is connected with model class 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; }
}
Also when I try to use this class and insert data in db and get result, I catch exception
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.\r\n at Npgsql.TypeMapping.ConnectorTypeMapper.GetByClrType(Type type)\r\n at Npgsql.
How I can create mapping for timerange?
The problem here is that .NET TimeSpan isn't by default mapped to PostgreSQL time - it's mapped to interval (see the docs table on mappings); this means that NpgsqlRange<TimeSpan> also doesn't get mapped to your PostgreSQL timerange by default.
It's still possible for all this to work, but you need to tell Npgsql explicitly which type to send. Without using Dapper, the following should work well:
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();
With Dapper, you'll need a bit of customization to set NpgsqlDbType, this question should help.