Bug #73278 EF6 MySql Migrations Code Generator Bug, Broken Column Index Code
Submitted: 11 Jul 2014 17:00 Modified: 5 Mar 2015 18:31
Reporter: Ryan Mann Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.8.3.0 OS:Any
Assigned to: Roberto Ezequiel Garcia Ballesteros CPU Architecture:Any
Tags: codefirst, CodeGenerator, EF6, migrations, MySql.Data.Entity.EF6

[11 Jul 2014 17:00] Ryan Mann
Description:
I am using code first migrations with Entity Framework 6 on MySql.  I am using version 6.8.3.0 of the MySql.Data/EF6 libraries and .Net Connector.

The problem is that when you use Add-Migration to create the initial database creation migration code, The CodeGenerator in MySql is generating sql that is missing quotes around index names.

note: I am using strictly fluent API, absolutely no DataAnnotation attributes.

If you add a multi column unique contraint you have to manually specify the name of the index.  MySql generators the c# code like this,

            CreateTable(
                "configentries",
                c => new
                    {
                        ConfigEntryId = c.Long(nullable: false, identity: true),
                        ConfigEntryCategoryId = c.Long(nullable: false),
                        Name = c.String(nullable: false, maxLength: 255, fixedLength: true, unicode: false, storeType: "char"),
                        Value = c.String(unicode: false),
                    })
                .PrimaryKey(t => t.ConfigEntryId)                
                .ForeignKey("configentrycategories", t => t.ConfigEntryCategoryId, cascadeDelete: true)
                .Index(t => new { t.ConfigEntryCategoryId, t.Name }, unique: true, name: UX_Category_Name);

The last part is the problem

name: UX_Category_Name is invalid, it should be name: "UC_Category_Name" with quotes around it.

To work around this I just defined global constants for UX_Category_Name etc.

How to repeat:
Create a Class Library,  Use nuget package manager to add

-EntityFramework 6.1.1
-MySql.Data 6.8.3
-MySql.Data.Entities 6.8.3

Make sure the latest MySql Visual Studio integration extension is installed.

Create a simple EF6 entity, something like Person with a property called PersonId and FirstName.

Create a Data Context with a a DbSet<Person> accessor.  Override on ModelBuilding. Configure PersonId to be the key with FluentApi.  And configure FirstName to be unique (just as an example) and give the index a name.

modelBuilder.Entity<Person>.Property(e => e.FirstName).IsRequired().HasColumnType("char").HasMaxLength(128).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("UQ_FirstName") { IsUnique = true }));      

run Enable-Migrations in the Package Manager console.

Add a new migration called InitialCreate

Afterwards the project should fail to build because the UQ_FirstName does not have quotes around it in the generated code.

Suggested fix:
When the code generator generates Index set's, if the Index has a name, put quotes around it.
[11 Jul 2014 17:02] Ryan Mann
Changed title to reflect what the bug is
[31 Aug 2015 9:54] Oleg Savelos
This is still present in 6.9.7.0
[6 Jan 2016 13:29] Aliaksei Kartynnik
This is still present in 6.9.8.0 version to...
[11 Jan 2016 13:32] Stian Skjerveggen
For a simple fix, override GenerateInline(CreateIndexOperation, IndentedTExtwriter) from MysqlMigrationCodeGenerator like so:

    public class FixedMySqlMigrationCodeGenerator : MySql.Data.Entity.MySqlMigrationCodeGenerator
    {
        private string TrimSchemaPrefix(string table)
        {
            if (table.StartsWith("dbo."))
                return table.Replace("dbo.", "");

            return table;
        }

        protected override void GenerateInline(CreateIndexOperation createIndexOperation, IndentedTextWriter writer)
        {
            writer.WriteLine();
            writer.Write(".Index(");

            Generate(createIndexOperation.Columns, writer);

            writer.Write(createIndexOperation.IsUnique ? ", unique: true" : "");
            writer.Write(!createIndexOperation.HasDefaultName ? string.Format(", name: \"{0}\"", TrimSchemaPrefix(createIndexOperation.Name)) : "");

            writer.Write(")");
        }

    }

Then use the new class as CodeGenerator in your migration Configuration constructor