SqlMapper.TypeHandler<DateTimeOffset?> - Parse works, SetValue never called

[voltagex]

Dapper.SqlMapper.AddTypeHandler is not working as I would expect.

You will need to add the following PackageReference to your csproj (see dotnet/efcore#28124), but this issue is related to the mapping itself, not the strict table support - strict tables just show the problem better.

		<PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.0" />

using Microsoft.Data.Sqlite;
using System.Data;
using Dapper;

namespace BugReport
{
    public class DateTimeToTimestampHandler : SqlMapper.TypeHandler<DateTimeOffset?>
    {
        public override void SetValue(IDbDataParameter parameter, DateTimeOffset? value)
        {
            Console.WriteLine($"SetValue was hit, input value is {value.Value}");
            parameter.Value = value.Value.ToUnixTimeSeconds();
        }
        public override DateTimeOffset? Parse(object value)
        {
            Console.WriteLine($"Parse was hit, input value is {value} - converted to {DateTimeOffset.FromUnixTimeSeconds((long)value)}");
            return DateTimeOffset.FromUnixTimeSeconds((long)value);
        }
    }

    public class BugReport
    {
        public static void Main()
        {
            DateTimeOffset? myTimestamp = DateTimeOffset.UtcNow;
            Dapper.SqlMapper.AddTypeHandler(new DateTimeToTimestampHandler());

            var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            SQLitePCL.raw.sqlite3_trace(connection.Handle, (_, statement) => Console.WriteLine($"Sent to SQLite: {statement}"), null);

            Console.WriteLine("SQLite version is " + connection.ExecuteScalar("SELECT sqlite_version()"));
            connection.Execute("CREATE TABLE BugReport (ThisIsAnIntColumn INTEGER) STRICT");
            connection.Execute("INSERT INTO BugReport Values (1653915600)");
            var firstSelect = connection.Query<DateTimeOffset?>("SELECT * FROM BugReport");
            Console.WriteLine($"Mapped result is {firstSelect.First()}");
            try
            {
                connection.Execute("INSERT INTO BugReport VALUES (@MyTimestamp)", new { MyTimestamp = myTimestamp });
            }

            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

        }
    }
}

Expected: DateTimeToTimestampHandler.SetValue is called, INTEGER (long) value inserted into the table

Actual:

Sent to SQLite: SELECT sqlite_version()
SQLite version is 3.38.3
Sent to SQLite: CREATE TABLE BugReport (ThisIsAnIntColumn INTEGER) STRICT
Sent to SQLite: INSERT INTO BugReport Values (1653915600)
Sent to SQLite: SELECT * FROM BugReport
Parse was hit, input value is 1653915600 - converted to 30/05/2022 1:00:00 PM +00:00
Mapped result is 30/05/2022 1:00:00 PM +00:00
Sent to SQLite: INSERT INTO BugReport VALUES ('2022-05-30 13:03:01.4304754+00:00')
SQLite Error 19: 'cannot store TEXT value in INTEGER column BugReport.ThisIsAnIntColumn'.

SetValue is never called, and ToString is called on the DateTimeOffset instead.

I'm using the following versions:

		<PackageReference Include="Dapper" Version="2.0.123" />
		<PackageReference Include="Microsoft.Data.Sqlite" Version="6.0.5" />
		<PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.1.0" />

on .NET 6.

[voltagex]

It looks like Dapper.SqlMapper.LookupDbType(System.Type type, string name, bool demand, out Dapper.SqlMapper.ITypeHandler handler) will never actually resolve my type handler.

[voltagex]

Similar to #1728?

[voltagex]

I see we're waiting for #471, I guess this means I'll need to have my own fork of Dapper for now.