c# - Composite type parameter in PostgreSql using Npgsql, - Stack Overflow

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?

• c#
• .net
• dapper
• npgsql
• .net-9.0

Share Improve this question Follow edited Feb 18 at 21:41 silkfire 26.1k1616 gold badges9292 silver badges114114 bronze badges asked Feb 17 at 14:54 Yván EcarriYván Ecarri 1,7481818 silver badges4242 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

There 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 - 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 datetimeoffset type.

See also:

• Need in-depth understanding of NpgSQL DateTimeOffset processing
• How to say Datetime - timestamp without time zone in EF Core 6.0