3
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:
- Entityframework
- Mysql.Data
- 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.
I didn’t understand what the problem is. Apparently everything is right. What would be the prefix that the tables generated by Migration should have?
– Leonel Sanches da Silva
Hello @Ciganomorrisonmendez, thanks for the interest. I’m sorry if I expressed myself badly. I added two images, one before using
Migrations
and another after performing the commandUpdate-Database
.– JamesTK
If anyone else is interested in helping, you can get the fonts from Github. Thanks! Link: https://github.com/JamesTKdev/Venda/tree/master
– JamesTK