Can not use with Dapper.Contrib InsertAsync and MySQL

[andrewjsaid]

Summary

I'm having trouble integrating this library into my project which uses Dapper.Contrib to insert entities to a mysql database.

Dapper.Contrib throws System.InvalidCastException: 'Invalid cast from 'System.UInt64' to 'StronglyTypedIdTest.PersonId'. when calling InsertAsync. This is because of a combination of mysql's LAST_INSERT_ID() returning a ulong and Dapper.Contrib's InsertAsync function for mysql using LAST_INSERT_ID() and using the method Convert.ChangeType(id, idp.PropertyType) to set the Id property on the object.

Code to Reproduce

CREATE TABLE Persons(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(100));

using Dapper.Contrib.Extensions;
using MySqlConnector;
using StronglyTypedIds;

[assembly: StronglyTypedIdDefaults(
    backingType: StronglyTypedIdBackingType.Int,
    converters: StronglyTypedIdConverter.DapperTypeHandler | StronglyTypedIdConverter.TypeConverter)]

namespace StronglyTypedIdTest;

[StronglyTypedId]
public partial struct PersonId { }

public class Person
{
    [Key]
    public PersonId Id { get; set; }

    public string Name { get; set; }
}

public static class Program
{
    private const string ConnectionString = "xxxxxxxxxx";

    public static async Task Main()
    {
        Dapper.SqlMapper.AddTypeHandler(new PersonId.DapperTypeHandler());
        await using var conn = new MySqlConnection(ConnectionString);
        await conn.OpenAsync();
        await conn.InsertAsync(new Person { Name = "Andrew" });
    }
}

  <ItemGroup>
    <PackageReference Include="Dapper.Contrib" Version="2.0.78" />
    <PackageReference Include="MySqlConnector" Version="2.1.0" />
    <PackageReference Include="StronglyTypedId" Version="1.0.0-beta05">
        <PrivateAssets>all</PrivateAssets>
        <ExcludeAssets>runtime</ExcludeAssets>
    </PackageReference>
  </ItemGroup>

Solution

Unfortunately the way that the conversion happens with Convert.ChangeType makes it difficult to find a real solution. I wanted to log this issue to see if anybody else can figure one out.

Thanks for the great library and associated articles.

[andrewlock]

If I understand the code correctly, I can't see any way for this library to handle this 🤔 Convert.ChangeType() requires that the value supports the target type, which in this case, would mean that ulong would have to support PersonId.

I feel like the fix is for the Dapper.Contrib's InsertAsync function to use the registered SqlMapper.TypeHandler<> to convert the value? Maybe it's worth opening an issue in that project instead?

[andrewjsaid]

I think you're right; this is an issue for Dapper.Contrib.

[andrewlock]

I'll close this for now, as I don't think there's anything I can do, but feel free to re-open it if anything changes 🙂