EntityFramework, Migrations and MySql. Schema name being misinterpreted. How to solve?

I'm having a problem using migrations with MySql .

When I try to create the first version with Migrations it is making the schema / bank name 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 Code First mapping with EntityFramework , so I've created another, simpler base , 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 mappings by Fluent API .

Example of class 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 your 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");
    }
}

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

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

To use the EntityFramework Power Tools to do the mapping 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:

EntityFramework

MySql.Data

MySql.Data.Entity.EF6

The EntityFramework version is 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
    }
}

Configuration 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 Migrations ?

Adding more details:

This is the database I'm mapping with EntityFramework Power Tools :

SoafterusingreverseengineeringwiththeEntityFrameworkPowerToolsIaddamigrations(Add-MigrationInitial)thatgeneratesthefirstcodesnippetofDbMigrationofthatquestion.

SeethenamehegavetheSalestable("vendas.venda" ).

So I do Update-Database and my bank looks like this:

Finally,theMigrationsgeneratedcausesothertablestobecreatedwiththenamesvendas.venda,vendas.produto,venda.vendaitem.

Iimaginethattheprefix/schema/banknameshouldnotbeaddedtothetablenamewhencreatingtheversionofMigrations(firstcodesnippetofthequestion).AnditwouldonlycreatetheUpandDownemptymethods,andbydoingthistheUpdate-Databasecommandonlythe_Migrationstablewouldbecreatedinthedatabasewiththefirstversionrecord.

WhenUpdate-Databasewascreatingthe_Migrationstable,youalsorananerror:

  

Specifiedkeywastoolong;maxkeylengthis767bytes

Thiswillleaveyouforanotherquestion.

Extractingtheschemafromthemappingconfigurationclasses(classeswithFluent)

Ialsotriedtogettheschemanamefromtheconfigurationclasses:

From:

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

To:

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

But then migrations are generated with the schema dbo. .

Example:

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

And so when trying to apply the migration to the database it gives an error. This time he accused that the tables already exist.

  

Table 'product' already exists

It implies that it ignores dbo. but does not treat as an existing table.