CODEWITHSUNDEEP
mysqlentity-frameworkentity-framework-6
Catalin
Catalin

Reputation: 11721

Entity Framework + MySql + Guid error

I am using Entity Framework with MySql.

I have problems inserting items in Database using Guid properties.

The error i get is: Incorrect string value: '\x9D?Z\xC2\x1C\xB2...' for column 'Id' at row 1

If i change the property Id from Guid to string, it works fine.

The Collation of the table is set to utf8 - default collation. I have changed it to uft8mb4 - default collation with no effect at all.

The Id property Datatype is VARCHAR(64) PK NN UQ

Here is the simplified code

public class AppFormAppUnitOfWork : DbContext
{
    public DbSet<MenuItem> MenuItems { get; set; }
}

static void MySqlGuid(AppFormAppUnitOfWork unitOfWork)
{
    MenuItem menuItem = new MenuItem();
    menuItem.Id = Guid.NewGuid();
    menuItem.MenuItem_Name = "Hello World";

    unitOfWork.MenuItems.Add(menuItem);

    using (var trans = new TransactionScope())
    {
        // throws
        // Incorrect string value: '\x9D?Z\xC2\x1C\xB2...' for column 'Id' at row 1
        unitOfWork.SaveChanges();

        trans.Complete();
    }
}

static void MySqlString(AppFormAppUnitOfWork unitOfWork)
{
    MenuItem menuItem = new MenuItem();
    menuItem.Id = Guid.NewGuid().ToString();
    menuItem.MenuItem_Name = "Hello World";

    unitOfWork.MenuItems.Add(menuItem);

    using (var trans = new TransactionScope())
    {
        // works ok
        unitOfWork.SaveChanges();
        trans.Complete();
    }
}

Here are the web.config settings

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>

  <connectionStrings>
    <add name="AppFormAppUnitOfWork" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;port=3306;database=appformapp;uid=root;password=microsoft;Old Guids=true;Charset=utf8;" />
  </connectionStrings>

  <system.data>
    <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient" />
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d">
      </provider>
    </providers>
  </entityFramework>
</configuration>

The packages (Entity Framework MySql provider) i am using for this are:

<packages>
  <package id="EntityFramework" version="6.0.0" targetFramework="net45" />
  <package id="MySql.Data" version="6.9.6" targetFramework="net45" />
  <package id="MySql.Data.Entity" version="6.9.6" targetFramework="net45" />
</packages>

How can i fix this problem without changing all the Guid properties to string?

Upvotes: 1

Views: 2746

Answers (2)

Wraith404
Wraith404

Reputation: 92

Use CHAR(36) instead of VARCHAR(64). EF will then auto map it correctly. The same goes for Dapper by the way. I've used both and experimented with BINARY(16) and various VARCHAR() combinations, but CHAR(36) has always worked best with an ORM. Also with BINARY(16) you lose the human readability, which can make debugging difficult. I don't know why the MySQL docs recommend VARCHAR(64); it's overkill, and uniqueidentifiers are always exactly 36 bytes long, so the var byte is a waste of space.

Upvotes: 0

bubi
bubi

Reputation: 6491

GUID is not supported directly in MySql. The right structure to store it is BINARY(16) (you can store the GUID using Guid.ToByteArray). If you want to keep a varchar (I've never used binary to store Ids) you could use 

menuItem.Id = String.Format("{0:N}", Guid.NewGuid());

Upvotes: 1

Related Questions