Bug #70795 Entity Framework migration with Foreign Key fails
Submitted: 1 Nov 2013 10:44 Modified: 11 Dec 2013 18:22
Reporter: Toivo Aasma Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.7.4 OS:Microsoft Windows
Assigned to: Francisco Alberto Tirado Zavala CPU Architecture:Any
Tags: entityframework migrations

[1 Nov 2013 10:44] Toivo Aasma
Description:
Using EF 5 Code First with automatic migrations enabled.
Adding a new POCO class and relating it to an existing one.
Running Update-Database -Verbose, fails as follows:

Using StartUp project 'MigrationTest'.
Using NuGet project 'MigrationTest'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'migrationtest' (DataSource: 127.0.0.1, Provider: MySql.Data.MySqlClient, Origin: Configuration).
No pending code-based migrations.
Applying automatic migration: 201311011025410_AutomaticMigration.
create table `People` (`Id` int not null  auto_increment primary key ,`Name` longtext) engine=InnoDb auto_increment=0
alter table `Blogs` add column `Owner_Id` int
alter table `dbo.Blogs` add constraint `FK_dbo.Blogs_dbo.People_Owner_Id`  foreign key (`Owner_Id`) references `dbo.People` ( `Id`)
MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'migrationtest.dbo.blogs' doesn't exist
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading, Boolean auto)
   at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
   at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()
Table 'migrationtest.dbo.blogs' doesn't exist

How to repeat:
1. VS2012, new project, C#, Console Application, named MigrationTest
2. Set target framework to  .NET Framework 4.5
3. PM console: Install-Package EntityFramework -Version 5.0.0
4. PM console: Install-package MySql.Data (installs 6.7.4)
5. PM console: Install-package MySql.Data.Entities (installs MySQL.Data.Entities 6.7.4.1)
6. In app.config: remove <entityFramework> element
7. In app.config: add   
  <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" />
    </DbProviderFactories>
  </system.data>
8. Add a file Model.cs with the following content
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MigrationTest
{
    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
    }
    public class BlogContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
    }
}

9. Add the following code to your Main function
            using (var db = new BlogContext())
            {
                db.Blogs.Add(new Blog { Name = "Another Blog " });
                db.SaveChanges();

                foreach (var blog in db.Blogs)
                {
                    Console.WriteLine(blog.Name);
                }
            }

            Console.WriteLine("Press any key to exit...");
            Console.ReadKey();

10. In app.config: add connectionString to your MySql Db
  <connectionStrings>
    <add name="BlogContext" connectionString="server=127.0.0.1;database=migrationtest;uid=root;pwd=somepassword" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>

11. Run the application, Db-table is created as expected in Db migrationtest
12. PM console: Enable-Migrations -EnableAutomaticMigrations
13. In the constructor Configuration() add the following call:
SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
14. In Model.cs, modify code by adding class, relating it to the existing one, and adding to context, giving following:
    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }
        virtual public Person Owner { get; set; }
    }
    public class Person
    {
        public Person()
        {
            Blogs = new List<Blog>();
        }
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual List<Blog> Blogs { get; set; }
    }
    public class BlogContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Person> People{ get; set; }
    }
15. PM console: Update-Database -Verbose, the following output is obtained
Using StartUp project 'MigrationTest'.
Using NuGet project 'MigrationTest'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'migrationtest' (DataSource: 127.0.0.1, Provider: MySql.Data.MySqlClient, Origin: Configuration).
No pending code-based migrations.
Applying automatic migration: 201311011025410_AutomaticMigration.
create table `People` (`Id` int not null  auto_increment primary key ,`Name` longtext) engine=InnoDb auto_increment=0
alter table `Blogs` add column `Owner_Id` int
alter table `dbo.Blogs` add constraint `FK_dbo.Blogs_dbo.People_Owner_Id`  foreign key (`Owner_Id`) references `dbo.People` ( `Id`)
MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'migrationtest.dbo.blogs' doesn't exist
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable`1 migrationStatements)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable`1 operations, Boolean downgrading, Boolean auto)
   at System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.AutoMigrate(String migrationId, XDocument sourceModel, XDocument targetModel, Boolean downgrading)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration)
   at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore()
   at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run()
Table 'migrationtest.dbo.blogs' doesn't exist

Suggested fix:
The following added code works as a workaround solution meanwhile.
Add the following class in Configuration.cs
    public class MyOwnMySqlMigrationSqlGenerator : MySqlMigrationSqlGenerator
    {
        protected override MigrationStatement Generate(AddForeignKeyOperation addForeignKeyOperation)
        {
            addForeignKeyOperation.PrincipalTable = addForeignKeyOperation.PrincipalTable.Replace("dbo.", "");
            addForeignKeyOperation.DependentTable = addForeignKeyOperation.DependentTable.Replace("dbo.", "");
            MigrationStatement ms = base.Generate(addForeignKeyOperation);
            return ms;
        }
    }

Modify sql generator:
SetSqlGenerator("MySql.Data.MySqlClient", new MyOwnMySqlMigrationSqlGenerator());
[11 Dec 2013 18:22] Daniel So
Added the following entry into the Connector/Net 6.7.5 and 6.8.2 changelogs:

"Code First automatic migration failed with foreign keys when using Entity Framework 5."