Can't write CLR type NodaTime.Instant with handler type TextHandler

[danielgratzl]

I have an issue getting Dapper Plus (latest trial 4.0.12) to work with NodaTime

My simple test code

DapperPlusManager.AddCustomSupportedType(typeof(Instant));
DapperPlusManager.Entity<MyEntity>()
  .Table("mytable");

var connectionString = "";
var connection = new NpgsqlConnection(connectionString);
connection.OpenAsync();
connection.TypeMapper.UseNodaTime();
connection.BulkInsert(new [] {
  new MyEntity(Instant.FromUtc(2021, 9, 9, 12, 0, 0))
});

public record MyEntity(Instant timestamp);

The table schema is this

create table mytable
(
    Timestamp         timestamp(3) with time zone not null
);

This will fail with

System.InvalidCastException
Can't write CLR type NodaTime.Instant with handler type TextHandler
   at lambda_method159(Closure , NpgsqlTypeHandler , Object , NpgsqlLengthCache& , NpgsqlParameter )
   at Npgsql.NpgsqlParameter.ValidateAndGetLength()
   at Npgsql.NpgsqlCommand.ValidateParameters(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteNonQuery()
   at .(DbCommand , BulkOperation , Int32 )
   at .( , DbCommand )
   at .Execute(List`1 actions)
   at .(List`1 )
   at Z.BulkOperations.BulkOperation.Execute()
   at Z.BulkOperations.BulkOperation.BulkInsert()
   at Z.Dapper.Plus.DapperPlusAction.Execute()
   at Z.Dapper.Plus.DapperPlusActionSet`1.AddAction(String mapperKey, DapperPlusActionKind actionKind, TEntity item)
   at Z.Dapper.Plus.DapperPlusActionSet`1.DapperPlusActionSetBuilder(DapperPlusContext context, IDbConnection connection, IDbTransaction transaction, String mapperKey, DapperPlusActionKind actionKind, TEntity item, Func`2[] selectors)
   at Z.Dapper.Plus.DapperPlusActionSet`1..ctor(DapperPlusContext context, IDbConnection connection, String mapperKey, DapperPlusActionKind actionKind, TEntity item, Func`2[] selectors)
   at Z.Dapper.Plus.DapperPlusExtensions.BulkInsert[T](IDbConnection connection, String mapperKey, T item, Func`2[] selectors)
   at Z.Dapper.Plus.DapperPlusExtensions.BulkInsert[T](IDbConnection connection, T item, Func`2[] selectors)
   at Tests.Dapper.Run() in Test.cs:line 206
   at Xunit.Sdk.TestInvoker`1.<>c__DisplayClass48_1.<<InvokeTestMethodAsync>b__1>d.MoveNext() in C:\Dev\xunit\xunit\src\xunit.execution\Sdk\Frameworks\Runners\TestInvoker.cs:line 264
--- End of stack trace from previous location ---
   at Xunit.Sdk.ExecutionTimer.AggregateAsync(Func`1 asyncAction) in C:\Dev\xunit\xunit\src\xunit.execution\Sdk\Frameworks\ExecutionTimer.cs:line 48
   at Xunit.Sdk.ExceptionAggregator.RunAsync(Func`1 code) in C:\Dev\xunit\xunit\src\xunit.core\Sdk\ExceptionAggregator.cs:line 90

If I do not use the Npgsql Noda plugin (.UseNodaTime();) it will still fail with a slightly different error message

System.InvalidCastException: Can't write CLR type NodaTime.Instant with handler type TimestampTzHandler

[JonathanMagnan]

Hello @danielgratzl ,

Could you try the solution here: https://github.com/zzzprojects/Dapper-Plus/issues/94#issuecomment-865980185

// Do it in the code only once
DapperPlusManager.AddCustomSupportedType(typeof(NodaTime.Instant));

Let me know if that worked.

Best Regards,

Jon

[danielgratzl]

Hi @JonathanMagnan, thanks for the quick reply.

This is exactly what I'm doing already. See line 1 of my example code :)

[JonathanMagnan]

Sorry @danielgratzl ,

My bad! Sometimes we get so used to an answer that we forget to look correctly.

Could you let us know the version of the providers you are using? We will try to reproduce the issue by using the same version as yours.

[danielgratzl]

No problem :)

• Dapper 2.0.90
• Dapper.Contrib 2.0.90
• Z.Dapper.Plus 4.0.12
• Npgsql 5.0.7
• Npgsql.NodaTime 5.0.7
• NodaTime 3.0.5

[JonathanMagnan]

Hello @danielgratzl ,

My developer tried it and everything seem to work correctly.

Can you tell us what we are doing different that could explain why it doesn't work on your side?

using Dapper;
using NodaTime;
using Npgsql;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Z.Dapper.Plus;

namespace Z.Lab.PostgreSQL
{
    public partial class Form_Request_NodeTimecs : Form
    {
        public Form_Request_NodeTimecs()
        {
            InitializeComponent();

            using (var connectionMaster = new Npgsql.NpgsqlConnection(My.ConnectionMaster))
            {

                var commandText = connectionMaster.Query<string>(@"SELECT 1 FROM pg_database WHERE datname = 'dapperpluslab'").FirstOrDefault();

                if (!string.IsNullOrEmpty(commandText))
                {
                    using (var command = connectionMaster.CreateCommand())
                    {
                        connectionMaster.Open();
                        command.CommandText = @"DROP DATABASE dapperpluslab";
                        command.ExecuteNonQuery();
                        connectionMaster.Close();
                    }
                }

                using (var command = connectionMaster.CreateCommand())
                {
                    connectionMaster.Open();
                    command.CommandText = @"CREATE DATABASE dapperpluslab";
                    command.ExecuteNonQuery();
                    connectionMaster.Close();
                }

            }

            using (var connectionTable = new Npgsql.NpgsqlConnection(My.Connection))
            {
                using (var command = connectionTable.CreateCommand())
                {
                    connectionTable.Open();
                    command.CommandText =
                @"CREATE SEQUENCE entitysimples_id_seq;  ";
                    command.ExecuteNonQuery();
                    connectionTable.Close();
                }
                using (var command = connectionTable.CreateCommand())
                {
                    connectionTable.Open();
                    command.CommandText =
                @" 

CREATE TABLE public.""EntitySimples""
(
    ""ID"" integer NOT NULL DEFAULT nextval('""entitysimples_id_seq""'),
    ""ColumnInt"" integer NOT NULL, 
    ""BookedOnDate"" timestamp(3) with time zone not null,
    ""ColumnString"" text COLLATE pg_catalog.""default"",
    CONSTRAINT ""PK_EntitySimples"" PRIMARY KEY(""ID"")
)
WITH(
    OIDS = FALSE
)
TABLESPACE pg_default;

                ALTER TABLE public.""EntitySimples""
    OWNER to postgres;";
                    command.ExecuteNonQuery();
                    connectionTable.Close();
                }
            }

            var connection = new Npgsql.NpgsqlConnection(My.Connection); 
            object p = NpgsqlConnection.GlobalTypeMapper.UseNodaTime();
            DapperPlusManager.AddCustomSupportedType(typeof(NodaTime.Instant));
            DapperPlusManager.Entity<EntitySimple>().Table("EntitySimples").Identity(x => x.ID); 
            var date = Instant.FromUtc(2011, 1, 1, 10, 30); 

            var list = new List<EntitySimple>();
            for (int i = 0; i < 3; i++)
            { 
                list.Add(new EntitySimple() { ColumnInt = i, BookedOnDate = date });
            }

            connection.BulkInsert(list); 
            // Query
            var list2 = connection.Query<EntitySimple>("SELECT * FROM \"EntitySimples\"");
        }

        public class EntitySimple
        {
            public int ID { get; set; }
            public int ColumnInt { get; set; }
            public string ColumnString { get; set; }
            public Instant BookedOnDate { get; set; }
        }
    }
}

[danielgratzl]

Thank you so much! It turned out we had another Dapper TypeHandler for NodaTime.Instant registered (enabled via Autofac) which somehow messed with the DapperPlus handling of Instant types.

Once we disabled our own mapper, DapperPlus works like a charm.

[JonathanMagnan]

Hello again,

Great to hear everything works well!

Don't hesitate to contact us if you have any questions.

Best regards,

Jon