Description:
When using Entity Frameworks: Code First in Visual Studio 2012 and using a GUID as the primary key in one of my POCO classes I receive the following error: Incorrect column specifier for column 'PlayerID'.
As far as I (and others) can tell, my code is all correct, and the correct behaviour of this should be that Entity Framework tells the provider to inform the database that it must create a GUID (UUID in MySQL, I believe) in the primary key column.
The code I have used has been tested and has worked with SQL Server Express and SQL Compact Server, which means that it should work -without alteration- with MySQL.
Upon further investigation, it looks as though MySQL is attempting to apply AUTO INCREMENT to the primary key field, believing it to be an INT.
How to repeat:
First, create a POCO class in VS2012 that uses a GUID PK, such as this:
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; }
}
Then create a configuration class for the Player like this:
public class PlayerConfiguration : EntityTypeConfiguration<Player>
{
public PlayerConfiguration()
{
ToTable("Players", "ACDB");
Property(p => p.PlayerID)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
Property(p => p.UserName).HasMaxLength(25);
}
}
Remember to register the configuration with the DbContext.
When a new Player objected is saved to the database a GUID should be created by MySQL and inserted into the PK field, instead the db will fail to create the table.
Suggested fix:
At this time the only work around I can think of is to use:
PlayerID = Guid.NewGuid();
In the Player constructor, which has entity framework create the GUID and pass it along to the db with the other fields. I also need to use DatabaseGeneratedOption.None instead of Identity.
This is far from ideal as the purpose of Entity Framework is to allow software developers to write code applicable to all database providers without alteration.
To this end, the MySQL .NET Provider needs to take into account the DatabaseGeneratedOption.Identity on GUIDs and understand that MySQL itself is responsible for creating it as well as not attempting to apply AUTO INCREMENT to the column.
Description: When using Entity Frameworks: Code First in Visual Studio 2012 and using a GUID as the primary key in one of my POCO classes I receive the following error: Incorrect column specifier for column 'PlayerID'. As far as I (and others) can tell, my code is all correct, and the correct behaviour of this should be that Entity Framework tells the provider to inform the database that it must create a GUID (UUID in MySQL, I believe) in the primary key column. The code I have used has been tested and has worked with SQL Server Express and SQL Compact Server, which means that it should work -without alteration- with MySQL. Upon further investigation, it looks as though MySQL is attempting to apply AUTO INCREMENT to the primary key field, believing it to be an INT. How to repeat: First, create a POCO class in VS2012 that uses a GUID PK, such as this: 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; } } Then create a configuration class for the Player like this: public class PlayerConfiguration : EntityTypeConfiguration<Player> { public PlayerConfiguration() { ToTable("Players", "ACDB"); Property(p => p.PlayerID) .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); Property(p => p.UserName).HasMaxLength(25); } } Remember to register the configuration with the DbContext. When a new Player objected is saved to the database a GUID should be created by MySQL and inserted into the PK field, instead the db will fail to create the table. Suggested fix: At this time the only work around I can think of is to use: PlayerID = Guid.NewGuid(); In the Player constructor, which has entity framework create the GUID and pass it along to the db with the other fields. I also need to use DatabaseGeneratedOption.None instead of Identity. This is far from ideal as the purpose of Entity Framework is to allow software developers to write code applicable to all database providers without alteration. To this end, the MySQL .NET Provider needs to take into account the DatabaseGeneratedOption.Identity on GUIDs and understand that MySQL itself is responsible for creating it as well as not attempting to apply AUTO INCREMENT to the column.