mysqlnhibernateuuidnhibernate-mappingnhibernate-mapping-by-code

NHibernate: How to insert C# [Guid] into MySQL [BINARY(16) DEFAULT (uuid_to_bin(uuid(),1))] column?


Environment: MySQL Server 8.0, .NET Core 3.1, MySql.Data 8.0.28, NHibernate 5.3.11

I have following table:

CREATE TABLE `Master` (
  `Row_Id` char(36) NOT NULL DEFAULT (uuid()),
  `Path` varchar(1000) NOT NULL,
  PRIMARY KEY (`Row_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Following is entity definition and mapping:

public class MasterEntity
{
    public virtual Guid RowId { get; set; }
    public virtual string Path { get; set; }
}

internal sealed class MasterMap : ClassMapping<MasterEntity>
{
    public MasterMap()
    {
        Table("Master");

        Id
        (
            x => x.RowId,
            map =>
            {
                map.Column("Row_Id");
                map.Generator(Generators.GuidComb);
            }
        );

        Property(x => x.Path, map => { map.Column("Path"); map.NotNullable(true); map.Type(TypeFactory.GetAnsiStringType(1000)); });
    }
}

Following is how I INSERT this entity using NHibernate:

using(ISession session = SessionFactory.OpenSession())
{
    MasterEntity entity = new MasterEntity();
    entity.Path = "c:\whatever";
    session.Save(entity);
    session.Flush();
}

This inserts the record correctly. Up to here, everything is fine.

Now, I change the definition of Row_Id column as below:

`Row_Id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),1)),

I do not change anything in my C# code. Now, the session.Flush(); call throws below exception:

NHibernate.Exceptions.GenericADOException: could not execute batch command.[SQL: SQL not available]
 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Data too long for column 'Row_Id' at row 1

The error looks obvious. The Guid in C# is 32 and column length is 16.
What changes I need to make in my mapping or entity definition (or other part of code) to insert C# Guid into BINARY(16) DEFAULT (uuid_to_bin(uuid(),1)) column?


Solution

  • By default, MySql.Data will store a Guid as CHAR(36). You can use BINARY(16) instead by specifying Old Guids = True; in your connection string.

    From Connector/NET 8.0 Connection Options Reference:

    The back-end representation of a GUID type was changed from BINARY(16) to CHAR(36). This was done to allow developers to use the server function UUID() to populate a GUID table - UUID() generates a 36-character string. Developers of older applications can add 'Old Guids=true' to the connection string to use a GUID of data type BINARY(16).