I'm working with Npgsql and and Dapper I have some tables in PostgreSql with a composite data type:
create type datetimeoffset as
(
"DateTimeUtc" timestamp without time zone,
"Offset" smallint
);
To represent this data type I have the following class:
public class PgDateTimeOffset
{
public PgDateTimeOffset(DateTime dateTimeUtc, short offset)
{
DateTimeUtc = DateTime.SpecifyKind(dateTimeUtc, DateTimeKind.Utc);
Offset = offset;
}
public DateTime DateTimeUtc { get; set; }
public short Offset { get; set; }
}
Let's say I have a table:
create table mytable
(
Id int not null generated always as identity,
Timestamp datetimeoffset
);
And I have code to write to this table:
public void Insert(PgDateTimeOffset timestamp)
{
Connection.Execute("INSERT INTO mytable (Timestamp) VALUES (@timestamp)", new { timestamp });
}
I also created a type handler:
public class DapperDateTimeOffsetTypeHandler: SqlMapper.TypeHandler<DateTimeOffset>
{
public override void SetValue(IDbDataParameter parameter, DateTimeOffset value)
{
// When sending data, create an instance of DateTimeOffsetPg
parameter.Value = (PgDateTimeOffset)value;
}
public override DateTimeOffset Parse(object value)
{
// When reading data, cast the object to DateTimeOffsetPg
if (value is PgDateTimeOffset composite)
{
return composite;
}
if(value is DateTimeOffset dto)
{
return dto;
}
if (value is DateTime dt)
{
return dt;
}
throw new DataException("Unexpected type when converting to DateTimeOffset.");
}
}
And I am registering the type handler:
SqlMapper.AddTypeHandler(new DapperDateTimeOffsetTypeHandler());
And the composite type is registered:
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
dataSourceBuilder.MapComposite<PgDateTimeOffset>("datetimeoffset");
var dataSource = dataSourceBuilder.Build();
The problem is I continue getting this exception:
System.NotSupportedException
HResult=0x80131515
Message=The member timestamp of type Neurologistica.Data.Repositories.PostgreSql.PgDateTimeOffset cannot be used as a parameter value
Source=Dapper
StackTrace:
Any idea? What am I missing?
I'm working with Npgsql and and Dapper I have some tables in PostgreSql with a composite data type:
create type datetimeoffset as
(
"DateTimeUtc" timestamp without time zone,
"Offset" smallint
);
To represent this data type I have the following class:
public class PgDateTimeOffset
{
public PgDateTimeOffset(DateTime dateTimeUtc, short offset)
{
DateTimeUtc = DateTime.SpecifyKind(dateTimeUtc, DateTimeKind.Utc);
Offset = offset;
}
public DateTime DateTimeUtc { get; set; }
public short Offset { get; set; }
}
Let's say I have a table:
create table mytable
(
Id int not null generated always as identity,
Timestamp datetimeoffset
);
And I have code to write to this table:
public void Insert(PgDateTimeOffset timestamp)
{
Connection.Execute("INSERT INTO mytable (Timestamp) VALUES (@timestamp)", new { timestamp });
}
I also created a type handler:
public class DapperDateTimeOffsetTypeHandler: SqlMapper.TypeHandler<DateTimeOffset>
{
public override void SetValue(IDbDataParameter parameter, DateTimeOffset value)
{
// When sending data, create an instance of DateTimeOffsetPg
parameter.Value = (PgDateTimeOffset)value;
}
public override DateTimeOffset Parse(object value)
{
// When reading data, cast the object to DateTimeOffsetPg
if (value is PgDateTimeOffset composite)
{
return composite;
}
if(value is DateTimeOffset dto)
{
return dto;
}
if (value is DateTime dt)
{
return dt;
}
throw new DataException("Unexpected type when converting to DateTimeOffset.");
}
}
And I am registering the type handler:
SqlMapper.AddTypeHandler(new DapperDateTimeOffsetTypeHandler());
And the composite type is registered:
var dataSourceBuilder = new NpgsqlDataSourceBuilder(connectionString);
dataSourceBuilder.MapComposite<PgDateTimeOffset>("datetimeoffset");
var dataSource = dataSourceBuilder.Build();
The problem is I continue getting this exception:
System.NotSupportedException
HResult=0x80131515
Message=The member timestamp of type Neurologistica.Data.Repositories.PostgreSql.PgDateTimeOffset cannot be used as a parameter value
Source=Dapper
StackTrace:
Any idea? What am I missing?
Share Improve this question edited 2 days ago silkfire 26k16 gold badges91 silver badges114 bronze badges asked Feb 17 at 14:54 Yván EcarriYván Ecarri 1,73818 silver badges42 bronze badges1 Answer
Reset to default 3There are several things that I did to make this work.
First of all as far as I can see the parameterless ctor is required for the complex type:
public class PgDateTimeOffset
{
public PgDateTimeOffset()
{
}
// ...
}
Then since you don't use the standard snake-case naming for the PostgreSQL then NpgsqlNullNameTranslator usage is needed for the mapping:
dataSourceBuilder
.MapComposite<PgDateTimeOffset>("datetimeoffset", new NpgsqlNullNameTranslator());
Then dapper mapping I used was to DbType.Object:
SqlMapper.AddTypeMap(typeof(PgDateTimeOffset), DbType.Object);
And last but not least - the I've used non-UTC time to insert:
PgDateTimeOffset timestamp = new PgDateTimeOffset
{
DateTimeUtc = DateTime.Now,
Offset = 3
};
The full snippet I've used for testing:
var dataSourceBuilder = new NpgsqlDataSourceBuilder(...);
dataSourceBuilder.MapComposite<PgDateTimeOffset>("datetimeoffset", new NpgsqlNullNameTranslator());
var dataSource = dataSourceBuilder.Build();
SqlMapper.AddTypeMap(typeof(PgDateTimeOffset), DbType.Object);
using var conn = dataSource.CreateConnection();
conn.Open();
PgDateTimeOffset timestamp = new PgDateTimeOffset
{
DateTimeUtc = DateTime.Now,
Offset = 3
};
conn.Execute("INSERT INTO mytable (Timestamp) VALUES (@timestamp)", new { timestamp });
var pgDateTimeOffsets = conn.Query<PgDateTimeOffset>("select Timestamp from mytable; ")
.ToList();
Notes:
Since 6th version as far as I remember Npgsql maps UTC DateTime to timestamp with time zone:
The .NET and PostgreSQL types differ in the resolution and range they provide; the .NET type usually have a higher resolution but a lower range than the PostgreSQL types:
PostgreSQL type Precision/Range .NET Native Type Precision/Range timestamp with time zone 1 microsecond, 4713BC-294276AD DateTime (UTC) 100 nanoseconds, 1AD-9999AD timestamp without time zone 1 microsecond, 4713BC-294276AD DateTime (Unspecified) 100 nanoseconds, 1AD-9999AD date 1 day, 4713BC-5874897AD DateOnly (6.0+), DateTime 100 nanoseconds, 1AD-9999AD time without time zone 1 microsecond, 0-24 hours TimeOnly (6.0+), TimeSpan 100 nanoseconds, -10,675,199 - 10,675,199 days time with time zone 1 microsecond, 0-24 hours DateTimeOffset (ignore date) 100 nanoseconds, 1AD-9999AD interval 1 microsecond, -178000000-178000000 years TimeSpan 100 nanoseconds, -10,675,199 - 10,675,199 days
So if you want to use UTC it would be better to use timestamp with time zone for your type.
See also:
- Need in-depth understanding of NpgSQL DateTimeOffset processing
- How to say Datetime - timestamp without time zone in EF Core 6.0