CODEWITHSUNDEEP
c#.netpostgresqldappernpgsql
Николай Шевченко
Николай Шевченко

Reputation: 63

How create type mapping for custom Postgresql range type?

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?

Upvotes: 2

Views: 2291

Answers (1)

Shay Rojansky
Shay Rojansky

Reputation: 16692

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.

Upvotes: 1

Related Questions