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

Question

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?

Answer 1

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).

Answer 2

The way suggested in accepted answer works; but it has a problem.

The code in the question uses uuid_to_bin(uuid(),1); the second swap parameter is set to 1. With this, the INSERT works great; but when you SELECT the row, you get entirely different UUID. This is because, database drivers do not know whether the UUID is swapped or not.

Better solution is to use MySqlConnector instead of Oracle's Connector/NET (MySql.Data.dll).

For ADO.NET:

Configure it as explained here.

For .NET Core 2.1 or later, call DbProviderFactories.RegisterFactory("MySqlConnector", MySqlConnectorFactory.Instance) during application startup. This will register MySqlConnector’s DbProviderFactory implementation in the central DbProviderFactories registry.

_{My observation is that, call to DbProviderFactories.RegisterFactory is not needed. It just works by adding reference of MySqlConnector.dll and removing reference of MySql.Data.dll.}

With MySqlConnector, the OldGuids=True; setting is available but obsolete; avoid it.
Use GuidFormat=TimeSwapBinary16; for uuid_to_bin(uuid(),1) (swap parameter set to 1).
Other possible values are mentioned here:

Determines which column type (if any) should be read as a System.Guid. The options include:

Char36:
All CHAR(36) columns are read/written as a Guid using lowercase hex with hyphens, which matches UUID().

Char32:
All CHAR(32) columns are read/written as a Guid using lowercase hex without hyphens.

Binary16:
All BINARY(16) columns are read/written as a Guid using big-endian byte order, which matches UUID_TO_BIN(x).

TimeSwapBinary16:
All BINARY(16) columns are read/written as a Guid using big-endian byte order with time parts swapped, which matches UUID_TO_BIN(x,1).

LittleEndianBinary16:
All BINARY(16) columns are read/written as a Guid using little-endian byte order, i.e. the byte order used by Guid.ToByteArray() and the Guid(byte[]) constructor.

None:
No column types are automatically read as a Guid.

Default:
Same as Char36 if OldGuids=False; same as LittleEndianBinary16 if OldGuids=True.

For NHibernate:

• Install NHibernate.MySqlConnector from nuget package.
• Add configuration.DataBaseIntegration(c => c.MySqlConnectorDriver()); in Session Factory configuration.
• Set GuidFormat in connection string as explained above.

For other ORMs:

Please refer to this for usage with other ORMs.