Bug #70079 Code To SQL Generation Broken For Foreign Keys/Indexes On Table Create
Submitted: 19 Aug 2013 0:50 Modified: 5 Feb 2014 18:28
Reporter: William Roush Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.7.4.0 OS:Any
Assigned to: Assigned Account CPU Architecture:Any

[19 Aug 2013 0:50] William Roush
Description:
Index/Foreign keys are not being generated for new tables. If tables are generated without foreign keys/indexes, upgraded, then keys/indexes are added and re-generated, the new update script properly imports them.

How to repeat:
  public class Post
  {
      [Key]
      public Guid Id { get; set; }

      public User Author { get; set; }
  }   
  public class User
  {
      [Key]
      public Guid Id { get; set; }
      public string UserName { get; set; }
      public string Password { get; set; }
      public string FullName { get; set; }
  }

  public class DatabaseContext : DbContext
  {
      public DbSet<Post> Posts { get; set; }
      public DbSet<User> Users { get; set; }
  }
  
  internal sealed class Configuration : DbMigrationsConfiguration<RoushTech.Website.DAL.DatabaseContext>
  {
      public Configuration()
      {
          this.AutomaticMigrationsEnabled = false;
          SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
          CodeGenerator = new MySql.Data.Entity.MySqlMigrationCodeGenerator();
      }
  }
  
  
  
  Creates upgrade:

  public override void Up()
  {
      CreateTable(
          "Posts",
          c => new
              {
                  Id = c.Guid(nullable: false),
                  Author_Id = c.Guid(),
              })
          .PrimaryKey(t => t.Id);
      
      CreateTable(
          "Users",
          c => new
              {
                  Id = c.Guid(nullable: false),
                  UserName = c.String(unicode: false),
                  Password = c.String(unicode: false),
                  FullName = c.String(unicode: false),
              })
          .PrimaryKey(t => t.Id);
  }
  
  Expected upgrade:
  
  CreateTable(
      "Posts",
      c => new
          {
              Id = c.Guid(nullable: false),
              Author_Id = c.Guid(),
          })
      .PrimaryKey(t => t.Id)
      .ForeignKey("Users", t => t.Author_Id)
      .Index(t => t.Author_Id);
  
  CreateTable(
      "Users",
      c => new
          {
              Id = c.Guid(nullable: false),
              UserName = c.String(unicode: false),
              Password = c.String(unicode: false),
              FullName = c.String(unicode: false),
          })
      .PrimaryKey(t => t.Id);
  
   
   Note missing Index/Foreign keys.
        

Suggested fix:
MySqlMigrationCodeGenerator.Generate(CreateTableOperation createTableOperation, IndentedTextWriter writer)

In this class there are a couple private Enumerables, _newTableForeignKeys and _newTableIndexes, they're a tuple that is bound to the current CreateTableOperation, by creating a new one we're losing that information.

This would be EASY to fix if Microsoft would STOP making critical components to overridden operation private and set them to protected, we could easily point the Tuples to the right and new information.

I have sort of an OK fix, where I basically override ScaffoldedMigration Generate(...) and make the renames there immediately prior to the private variables being set.

-----

# HG changeset patch
# User William Roush <william.roush@roushtech.net>
# Date 1376873321 14400
# Node ID 50a63625efde8b6d35b10df48f608340fa952b82
# Parent  d043d1353bf09d09a7f7e0367866ac8e0db093a7
Fixes bug

diff -r d043d1353bf0 -r 50a63625efde MySqlMigrationSqlGenerator.cs
--- a/MySqlMigrationSqlGenerator.cs	Sun Aug 18 20:47:52 2013 -0400
+++ b/MySqlMigrationSqlGenerator.cs	Sun Aug 18 20:48:41 2013 -0400
@@ -49,8 +49,36 @@
         return table.Replace("dbo.", "");
 
       return table;
+    }
+
+    private CreateTableOperation MigrateToNewName(CreateTableOperation createTableOperation)
+    {
+        var create = new CreateTableOperation(TrimSchemaPrefix(createTableOperation.Name));
+
+        foreach (var item in createTableOperation.Columns)
+            create.Columns.Add(item);
+
+        create.PrimaryKey = createTableOperation.PrimaryKey;
+        return create;
+    }
+
+    public override ScaffoldedMigration Generate(string migrationId, IEnumerable<MigrationOperation> operations, string sourceModel, string targetModel, string @namespace, string className)
+    {
+        var list = new List<MigrationOperation>();
+        foreach (var operation in operations)
+        {
+            var currentOperation = operation;
+
+            if (currentOperation is CreateTableOperation)
+            {
+                currentOperation = MigrateToNewName(currentOperation as CreateTableOperation);
+            }
+
+            list.Add(currentOperation);
+        }
+
+        return base.Generate(migrationId, list, sourceModel, targetModel, @namespace, className);
     }
-
     protected override void Generate(AddColumnOperation addColumnOperation, IndentedTextWriter writer)
     {
       var add = new AddColumnOperation(TrimSchemaPrefix(addColumnOperation.Table), addColumnOperation.Column);
@@ -60,7 +88,7 @@
     protected override void Generate(AddForeignKeyOperation addForeignKeyOperation, IndentedTextWriter writer)
     {
       addForeignKeyOperation.PrincipalTable = TrimSchemaPrefix(addForeignKeyOperation.PrincipalTable);
-      addForeignKeyOperation.DependentTable = TrimSchemaPrefix(addForeignKeyOperation.DependentTable);      
+      addForeignKeyOperation.DependentTable = TrimSchemaPrefix(addForeignKeyOperation.DependentTable);
       base.Generate(addForeignKeyOperation, writer);
     }
 
@@ -92,14 +120,7 @@
 
     protected override void Generate(CreateTableOperation createTableOperation, IndentedTextWriter writer)
     {
-      var create = new CreateTableOperation(TrimSchemaPrefix(createTableOperation.Name));
-      
-      foreach (var item in createTableOperation.Columns)	    
-        create.Columns.Add(item);
-
-      create.PrimaryKey = createTableOperation.PrimaryKey;
-
-      base.Generate(create, writer);
+      base.Generate(MigrateToNewName(createTableOperation), writer);
     }
 
     protected override void Generate(DropColumnOperation dropColumnOperation, IndentedTextWriter writer)

-----

My fix creates this output, close enough:

            CreateTable(
                "Posts",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        Author_Id = c.Guid(),
                    })
                .PrimaryKey(t => t.Id);
            
            CreateTable(
                "Users",
                c => new
                    {
                        Id = c.Guid(nullable: false),
                        UserName = c.String(unicode: false),
                        Password = c.String(unicode: false),
                        FullName = c.String(unicode: false),
                    })
                .PrimaryKey(t => t.Id);
            
            AddForeignKey("Posts", "Author_Id", "Users", "Id");
            CreateIndex("Posts", "Author_Id");
[19 Aug 2013 1:04] William Roush
Additionally, I didn't spend the time to understand what Entity is doing too much here... it may be better to override all renames in ScaffoldedMigration Generate(...) instead of the individual methods.
[5 Feb 2014 18:28] Francisco Alberto Tirado Zavala
This bug is duplicated with MySql Bug #71287, description differs but is the same behaviour.