Entityframework, Migrations and Mysql. Schema name being misinterpreted. How to resolve?

Question

I have a problem using Migrations with Mysql.

When I try to create the first version with Migrations he’s making the schema/name of the bank part of the table name:

public partial class Initial : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "vendas.produto",  // Aqui
            c => new
                {
                    ...
                })
            .PrimaryKey(t => t.Id);
        
        CreateTable(
            "vendas.vendaitem", // aqui
            c => new
                {
                    ...
                })
            ....
            .Index(t => t.ProdutoId);
        
        CreateTable(
            "vendas.venda", // e aqui
            c => new
                {
                    ...
                })
            .PrimaryKey(t => t.Id);            
    }

I’m trying to reverse engineer an existing base for a mapping Code First with Entityframework, then created another base, simpler, just to simulate the problem:

CREATE DATABASE `vendas` /*!40100 DEFAULT CHARACTER SET utf8 */;

CREATE TABLE `venda` (
  `Id` int(11) NOT NULL,
  `DataVenda` date NOT NULL,
  `SubTotal` double NOT NULL DEFAULT '0',
  `Desconto` double NOT NULL DEFAULT '0',
  `Total` double NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

... entre outras

Using the Entityframework Power Tools I did the mapping that generated the simple classes and the mapping by Fluent API.

Class example Venda:

public class venda
{
    public venda()
    {
        vendaitems = new List<vendaitem>();
    }

    public int Id { get; set; }
    public DateTime DataVenda { get; set; }
    public double SubTotal { get; set; }
    public double Desconto { get; set; }
    public double Total { get; set; }
    public virtual ICollection<vendaitem> vendaitems { get; set; }
}

And its mapping:

public class vendaMap : EntityTypeConfiguration<venda>
{
    public vendaMap()
    {
        // Primary Key
        HasKey(t => t.Id);

        // Properties
        Property(t => t.Id)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

        // Table & Column Mappings
        ToTable("venda", "vendas"); // <-- Aqui está o schema/database name

        Property(t => t.Id).HasColumnName("Id");
        Property(t => t.DataVenda).HasColumnName("DataVenda");
        Property(t => t.SubTotal).HasColumnName("SubTotal");
        Property(t => t.Desconto).HasColumnName("Desconto");
        Property(t => t.Total).HasColumnName("Total");
    }
}

From what I understand, for the first version with Migrations, the mapping being equivalent to the existing database the updating of the database (with update-database in the Package Manager Console) should generate the methods Up and Down empty and only the table Migrations would be added in the database.

However, the Migrations of all tables are created and the command Update-Database using the Migrations generated causes other tables to be created with the names vendas.venda, vendas.produto, venda.vendaitem.

To use Entityframework Power Tools to map I configured my App.config with the following code:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" requirePermission="false" 
    type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, 
    EntityFramework, Version=6.0.0.0, Culture=neutral,PublicKeyToken=b77a5c561934e089" />
  </configSections>
  <connectionStrings>
    <add name="VendasContext" providerName="MySql.Data.MySqlClient"
    connectionString="server=localhost;user id=USUARIO; password=SENHA; 
    persistsecurityinfo=True; database=Vendas" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, 
    MySql.Data.Entity.EF6" />
    <providers>
      <provider invariantName="MySql.Data.MySqlClient" 
       type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
    </providers>
  </entityFramework>
  <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>
</configuration>

I installed via Nuget:

1. Entityframework
2. Mysql.Data
3. Mysql.Data.Entity.EF6

The version of Entityframework is the 6.1.3 with .Net 4.5.
The version of Mysql.Data and Mysql.Data.Entity.EF6 is 6.9.6.

My class Configuration of Migrations:

internal sealed class Configuration : DbMigrationsConfiguration<Models.VendasContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = false;
        SetSqlGenerator("MySql.Data.MySqlClient", 
            new MySql.Data.Entity.MySqlMigrationSqlGenerator()); // necessário para usar 
                // Migrations com MySql
    }
}

Settings in the Context class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new produtoMap());
    modelBuilder.Configurations.Add(new vendaMap());
    modelBuilder.Configurations.Add(new vendaitemMap());

    modelBuilder.Properties<string>().Configure(p => p.HasColumnType("varchar"));
}

Having this information, how can I solve the problem with the Migrations?

---

Adding more details:

This is the bank I’m mapping with Entityframework Power Tools:

Then, after the use of reverse engineering with the Entityframework Power Tools i add a Migrations (Add-Migration Initial) which generates the first chunk of DbMigration of that question.

See the name he gave the table Sales ("vendas.venda").

So I make the Update-Database and my bank goes like this:

Finally, the Migrations generated causes other tables to be created with the names vendas.venda, vendas.produto, venda.vendaitem.

I guess which prefix/schema/database name should not be added to the table name when creating the version of Migrations (first code snippet of the question). And would only create the methods Up and Down empty and thus when performing the command Update-Database only the table _Migrations would be created in the database, with the first version record.

When the Update-Database was create the table _Migrations, also executed an error:

Specified key was Too long; max key length is 767 bytes

I’ll leave that for another question.

---

Removing schema from mapping configuration classes (classes with Fluent)

I also tried to get the schema name out of the configuration classes:

Of:

// Table & Column Mappings
ToTable("venda", "vendas"); // <-- Aqui está o schema/database name

To:

ToTable("venda"); // <-- Aqui está o schema/database name

But then Migrations are generated with schema dbo..

Example:

    CreateTable(
        "dbo.produto",  // <-- schema dbo.
        c => new
            {
                ...
            })
        .PrimaryKey(t => t.Id);

And this way also when trying to apply Migration in the database gives error. This time accuses that the tables already exist.

Table 'product' already exists

Hints that he ignores the dbo. but does not treat as an existing table.

Answer 1

You can customize the code generator to Migrations:

public class MeuProprioMySqlMigrationSqlGenerator : MySqlMigrationSqlGenerator
{
    protected override MigrationStatement Generate(AddForeignKeyOperation addForeignKeyOperation)
    {
        addForeignKeyOperation.PrincipalTable = addForeignKeyOperation.PrincipalTable.Replace("vendas.", "");
        addForeignKeyOperation.DependentTable = addForeignKeyOperation.DependentTable.Replace("vendas.", "");
        MigrationStatement ms = base.Generate(addForeignKeyOperation);
        return ms;
    }
}

Register it in your Migrations/Configuration.cs:

internal sealed class Configuration : DbMigrationsConfiguration<MeuProjeto.Models.MeuProjetoContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator("MySql.Data.MySqlClient", new MeuProprioMySqlMigrationSqlGenerator());
    }

    ...
}

---

EDIT

I reimplemented the wrong class. Also add the following class:

public class CustomMySqlMigrationCodeGenerator : MySqlMigrationCodeGenerator
{
    protected override void Generate(CreateTableOperation createTableOperation, IndentedTextWriter writer)
    {
        // Descomente para debugar
        // if (System.Diagnostics.Debugger.IsAttached == false)
        //    System.Diagnostics.Debugger.Launch();

        var create = new CreateTableOperation(createTableOperation.Name.Replace("Vendas.", ""));

        foreach (var item in createTableOperation.Columns)
            create.Columns.Add(item);

        create.PrimaryKey = createTableOperation.PrimaryKey;

        base.Generate(create, writer);
    }
}

If you want to trade createTableOperation.Name.Replace("Vendas.", "") by a generic solution, make yourself at home.

The configuration was like this:

    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = false;
        SetSqlGenerator("MySql.Data.MySqlClient", new CustomMySqlMigrationSqlGenerator());
        CodeGenerator = new CustomMySqlMigrationCodeGenerator();
        SetHistoryContextFactory("MySql.Data.MySqlClient", (conn, schema) => new MySqlHistoryContext(conn, schema));
    }

I made a pull request with the modifications in your Github repository.

Answer 2

Summarizing and synthesizing my problem, the confusions in you that I committed and what was done.

The Entityframework Power Tools, from what I understand, do not distinguish database when generating mapping classes.

When generating the mapping classes of my existing database he was adding the name of the database as schema (despite finding the term schema in the Mysql also, but I understand the schema in the Mysql as something other than schema in the SQL Server).

Example of mapping:

public class ProdutoMap : EntityTypeConfiguration<Produto>
{
    public ProdutoMap()
    {
        ToTable("Produto", "Vendas");  // isso que ele faz
        ....
    }
}

I believe that was the main confusion because the Mysql does not work with schemas like the SQL Server and, when generating the classes of Migrations, the name of the bank was attached with a dot to the table name as the schema is made in the SQL Server.

So I added the files to the project T4 (.tt), to edit, and no longer add the database name as the schema. This is necessary for any other basis Mysql who will care, especially with many tables.

And then the mapping classes came to have the Totable method written this way by the reverse engineering of the EF Power Tools:

public class ProdutoMap : EntityTypeConfiguration<Produto>
{
    public ProdutoMap()
    {
        ToTable("Produto");  // sem um nome para o esquema.
        ....
    }
}

However...

Even the illustrious and patient help of the colleague Gypsy I hadn’t done that, I was leaving the name of the bank as the name of schema there in the mapping class and wanting to generate a Migration correctly.

The fellow Gypsy smashing his head to understand my confusion attacked with the classes for generation of SQL of Mysql and code generation (of the classes of Migrations).

Okay, you solved!!! =]

Thank you, and I have already marked as accepted reply and the most deserved reward has been given.

However I was thinking, when I was overwriting more methods of CustomMySqlMigrationSqlGenerator and of CustomMySqlMigrationCodeGenerator inherited from MySqlMigrationSqlGenerator and MySqlMigrationCodeGenerator (the example can be seen in this history of Github in the archive Configuration.Cs)... It is not possible that they have released the support dll s EPH with this problem of name for tables.

So what was once set up as:

internal sealed class Configuration : DbMigrationsConfiguration<VendasContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = false;
        SetSqlGenerator("MySql.Data.MySqlClient", new CustomMySqlMigrationSqlGenerator());
        CodeGenerator = new CustomMySqlMigrationCodeGenerator();
        SetHistoryContextFactory("MySql.Data.MySqlClient", 
            (conn, schema) => new MySqlHistoryContext(conn, schema));
    }
}

I tested with:

internal sealed class Configuration : DbMigrationsConfiguration<VendasContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        AutomaticMigrationDataLossAllowed = true;
        CodeGenerator = new MySqlMigrationCodeGenerator();
        SetSqlGenerator("MySql.Data.MySqlClient", new MySqlMigrationSqlGenerator());
        SetHistoryContextFactory("MySql.Data.MySqlClient", 
            (conn, schema) => new MySqlHistoryContext(conn, schema));
    }
}

That is, without needing the classes we were using to remove the name of the name bank/schema table.

Finally, I still don’t understand why Migrations were being generated for the existing tables in the database, so I manually removed:

namespace Venda.Repositorio.Migrations
{
    public partial class Initial : DbMigration
    {
        public override void Up()
        {
        }

        public override void Down()
        {
        }
    }
}

And I performed the command Update-Database.

The table __migrations was added to the database with the registration of the first version.

I added another property to the class Venda, calling for Observacao of the kind string and had one more generated Migration.

Wonderful, worked:

namespace Venda.Repositorio.Migrations
{
    public partial class Versao_2 : DbMigration
    {
        public override void Up()
        {
            AddColumn("Venda", "Observacao", 
                c => c.String(unicode: false, storeType: "text"));
        }

        public override void Down()
        {
            DropColumn("Venda", "Observacao");
        }
    }
}

So, Update-Database again and it all went well!!

I’m working with Mysql, EPH and using Migrations hassle-free.

Grateful!