Insert when primary key is VARBINARY(16) Guid

[jkuek]

[Edited with new information]

My app is using MySqlConnector with an AWS Aurora server and I have a table with an Id field of type VARBINARY(16).

I've been trying to migrate from Dapper.SimpleCrud but can't seem to get it to set my Id field correctly on InsertAsync().

Dommel seems to support the following scenario (according to the integration test): insert to MS SQL server, database server generates the GUID, and Dommel reads it back. This doesn't transfer to MySQL as there's no way to read back the generated GUID of an inserted row.

I added [DatabaseGenerated(DatabaseGeneratedOption.None)] attribute to the Id property and generated the Guid in my app. Dommel then explicitly sets the Guid, but sends the value as a string rather than VARBINARY(16).

Is the correct approach to create a new class inheriting from MySqlSqlBuilder, override BuildInsert and then do the conversion manually?

Is automatic conversion from Guid/VARBINARY(16) a feature that could be added?

[jkuek]

To partially answer my own question, here is the code to get the insert working:

    public class GuidMySqlSqlBuilder : MySqlSqlBuilder
    {
        public override string BuildInsert(Type type, string tableName, string[] columnNames, string[] paramNames)
        {
            var index = Array.IndexOf(columnNames, "`Id`");
            if (index >=0)
            {
                // Replace the Id column with a new parameter name
                paramNames[index] = $"UUID_TO_BIN({paramNames[index]})";
            }

            return $"insert into {tableName} ({string.Join(", ", columnNames)}) values ({string.Join(", ", paramNames)}); select LAST_INSERT_ID() id";
        }

    }

Then if you call DommelMapper.AddSqlBuilder(typeof(MySqlConnection), new GuidMySqlSqlBuilder()); before calling InsertAsync(), the correct SQL is generated.

But there is now a new issue, where Dommel can't Get(Id) when the underlying data type in the table is VARBINARY(16) and there's no way I can see to override BuildGetById()

My current conclusion is that Dommel just doesn't support Guids that are internally stored as VARBINARY(16). Has anyone else got it to work?