I'm trying to store a Guid in a MySql (8.0) database. In my DotNet 6 app I use the package MySql.Data and Dapper on top of it for data access. It seems the recommended approach is to store Guids in binary(16) fields so I wrote a TypeHandler<Guid> but it doesn't seem to get invoked (breakpoints in the TypeHandler never get hit). The resulting MySql Error then is:
Data too long for column 'Id' at row 1
I have thrown together a little console app to illustrate the problem:
using System.Data;
using Dapper;
using MySql.Data.MySqlClient;
/*
CREATE TABLE `music_genres` (
`Id` BINARY(16) NOT NULL,
`Name` VARCHAR(25) NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE INDEX `Id_UNIQUE` (`Id` ASC) VISIBLE);
*/
SqlMapper.AddTypeHandler(typeof(Guid), new GuidTypeHandler());
using var con = new MySqlConnection("<some valid connectionstring here>");
con.Open();
var genre = new MusicGenre(Guid.NewGuid(), "Techno");
con.Execute("insert into music_genres (Id,Name) values (@Id,@Name)", genre); // fails with "Data too long for column 'Id' at row 1"
public record MusicGenre(Guid Id, string Name);
public class GuidTypeHandler : SqlMapper.TypeHandler<Guid>
{
public override void SetValue(IDbDataParameter parameter, Guid value)
{
parameter.Value = value.ToByteArray(); // breakpoints here will never got hit
}
public override Guid Parse(object value)
{
return new Guid(value as byte[]); // breakpoints here will never got hit
}
}
Code is simplified just to illustrate the point. My question: Why is the TypeHandler not getting invoked? In the real project there are quite a few TypeHandlers which all work as expected. Only the one mapping Guids doesn't seem to get invoked.