EF5 Code First GUIDs with MySQL

Question

I'm sure that I'm just missing something obvious but I can't seem to get the following code to function correctly.

This is one of my POCO classes that uses a Guid:

public class Player
{
    public virtual Guid PlayerID { get; set; }
    public virtual int? GuildID { get; set; }

    public virtual int AccountNumber { get; set; }
    public virtual string UserName { get; set; }

    public virtual Guild Guild { get; set; }

    public virtual ICollection<Coordinate> Coordinates { get; set; }
}

It is linked to it's own configuration class using the Fluent API:

public class PlayerConfiguration : EntityTypeConfiguration<Player>
{
    public PlayerConfiguration()
    {
        ToTable("Players", "ACDB");

        Property(p => p.PlayerID)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        Property(p => p.UserName).HasMaxLength(25);
    }
}

The configuration is then registered with the DbContext.

The error I'm getting is the following: Incorrect column specifier for column 'PlayerID'

Any ideas on where I'm going wrong?

Answer 1

What I think is happening here is that the Entity Framework creates the PlayerID column as a GUID (UUID in MySql) and then applies the AUTO_INCREMENT attribute to it.

From http://docs.oracle.com/cd/E17952_01/refman-5.5-en/numeric-type-attributes.html it seems that AUTO_INCREMENT is valid only for integer columns, which may be why you are receiving this error.

I think the thing to try here is to change PlayerID from a Guid to an int - this should at least narrow down the cause of the error.

Edit 1

From Convert and modify a model field as an DataColumn object and Disabling identity (auto-incrementing) on integer primary key using code first you should be able to turn off the auto incrementing with:

HasKey(p => p.PlayerID)
Property(p => p.PlayerID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)

Edit 2

At least on SQL Server, using Property(p => p.PlayerID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None) will result in an empty Guid being inserted when a new row is created (though it seems reasonable to assume it would behave this way elsewhere). The only sensible workaround I can see is to populate the PlayerID in the Player constructor:

public Player()
{
    this.PlayerID = Guid.NewGuid();
}