MySQL 5.5 + .NET Connector + Entity Framework + Migrations = FormatException

Question

I tried to find a solution for my problem but my efforts until now was in vain. :-(

I created a web project using Visual Studio 2010, .NET Framework 4, C#, Entity Framework 5.0, MySQL 5.5 and it's corresponding .NET connector (version 6.5.4). I'm using the code first approach for the entities and O/R mapping.

The problem I'm faced with is that I'm unable to execute what seemed to be a simple migration. Here are my entity classes:

public class Usuario
{
    public int Id { get; set; }

    [Required]
    [StringLength(100)]
    public string NomeCompleto { get; set; }

    [Required]
    [StringLength(100)]
    [DataType(DataType.EmailAddress)]
    public string Email { get; set; }

    [Required]
    [StringLength(30)]
    public string Login { get; set; }

    [Required]
    [StringLength(64)]
    public string Senha { get; set; }

    [Required]
    public bool Ativo { get; set; }

    //[Timestamp]
    [ConcurrencyCheck]
    public int Versao { get; set; }
}

public class Perfil
{
    public int Id { get; set; }

    [Required]
    [StringLength(50)]
    public string Nome { get; set; }

    [StringLength(100)]
    public string Descricao { get; set; }

    //[Timestamp]
    [ConcurrencyCheck]
    public int Versao { get; set; }

    public virtual ICollection<Usuario> Usuarios { get; set; }
    public virtual ICollection<Permissao> Permissoes { get; set; }
}

public class Permissao
{
    public int Id { get; set; }

    [Required]
    [StringLength(50)]
    public string Nome { get; set; }

    [StringLength(100)]
    public string Descricao { get; set; }

    //[Timestamp]
    [ConcurrencyCheck]
    public int Versao { get; set; }

    public virtual ICollection<Perfil> Perfis { get; set; }
}

The code generated by Add-Migration Acesso (only Up() method):

public partial class Acesso : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Usuario",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    NomeCompleto = c.String(nullable: false, storeType: "mediumtext"),
                    Email = c.String(nullable: false, storeType: "mediumtext"),
                    Login = c.String(nullable: false, storeType: "mediumtext"),
                    Senha = c.String(nullable: false, storeType: "mediumtext"),
                    Ativo = c.Boolean(nullable: false),
                    Versao = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.Id);

        CreateTable(
            "dbo.Perfil",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Nome = c.String(nullable: false, storeType: "mediumtext"),
                    Descricao = c.String(storeType: "mediumtext"),
                    Versao = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.Id);

        CreateTable(
            "dbo.Permissao",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Nome = c.String(nullable: false, storeType: "mediumtext"),
                    Descricao = c.String(storeType: "mediumtext"),
                    Versao = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.Id);

        CreateTable(
            "dbo.PerfilPermissao",
            c => new
                {
                    PerfilId = c.Int(nullable: false),
                    PermissaoId = c.Int(nullable: false),
                })
            .PrimaryKey(t => new { t.PerfilId, t.PermissaoId })
            .ForeignKey("dbo.Perfil", t => t.PerfilId, cascadeDelete: true)
            .ForeignKey("dbo.Permissao", t => t.PermissaoId, cascadeDelete: true)
            .Index(t => t.PerfilId)
            .Index(t => t.PermissaoId);

        CreateTable(
            "dbo.UsuarioPerfil",
            c => new
                {
                    UsuarioId = c.Int(nullable: false),
                    PerfilId = c.Int(nullable: false),
                })
            .PrimaryKey(t => new { t.UsuarioId, t.PerfilId })
            .ForeignKey("dbo.Usuario", t => t.UsuarioId, cascadeDelete: true)
            .ForeignKey("dbo.Perfil", t => t.PerfilId, cascadeDelete: true)
            .Index(t => t.UsuarioId)
            .Index(t => t.PerfilId);

    }
}

First of all I had to change the properties named Versao (version) from

[Timestamp]
public byte[] Versao { get; set; }

to

[ConcurrencyCheck]
public int Versao { get; set; }

because an error ocurred before the change (something about the type rowversion not been qualified with a namespace or alias). After this change I was able to generate the migration but the Update-Database command failed with the following error shown in the console:

System.FormatException: Cadeia de entrada não estava em um formato incorreto.
    em System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)

(Input string was not in not in a correct format.)

I tried to use MySQL versions 5.5 and 5.1; the versions 6.5.4, 6.4.5 and 6.3.9 of the connector and couldn't solve the problem.

Is it possible to use MySQL, Entity Framework and code first approach? If not, what are the consequences of switching to ODBC connector instead of the .NET one?

Thanks in advance and sorry about the big question.

Solution 1

You should also try .NET Connector 6.6 because it is the first release claiming support for EF 4.3 (the first release with migrations). If it doesn't help you should try dotConnect for MySql (at least trial) to find if the problem is in .NET Connector or in EF. ODBC connector will not work with EF.

Solution 2

I've created a fork of MySQL Data Connector v.6.6.4 that supports the latest version of Entity Framework (v.5).

To use it, you can just download the binaries, which are replacement assemblies for MySql.Data and MySql.Data.Entity. Also make sure your project is dependent on EF5 rather than 4.3.

After you Enable-Migrations the first time, modify your Configuration class' constructor to include the line:

SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());

At this point, you should be able to run Add-Migration and Update-Database without issues.

The key changes of my fork are as follows:

• The binaries are dependent on EF5 rather than EF4.3.

• EF5 prepends dbo. to all of your table names, which MySQL cannot handle. Thus, my version hacks the SQL migration generator to strip out the dbo. prefix from the table names. This all assumes you are not overriding the schema via the TableAttribute on the entity classes.

• It removes the usage of CreatedOn as Jimi mentioned in his answer.

Solution 3

I don't think Code Migration is supported by .Net Connector 6.6 . I tried it already, the error you will get, when you run 'Update-Database' No MigrationSqlGenerator found for provider 'MySql.Data.MySqlClient'. Use the SetSqlGenerator method in the target migrations configuration class to register additional SQL generators. dotConnect for MySql might work as they say they have added the migration support

Solution 4

I was getting below mentioned error

No MigrationSqlGenerator found for provider 'MySql.Data.MySqlClient'. Use the SetSqlGenerator method in the target migrations configuration class to register additional SQL generators.

and got fixed with this statement

SetSqlGenerator("MySql.Data.MySqlClient", new ySql.Data.Entity.MySqlMigrationSqlGenerator());

This needs to be set under Configuration constructor.

Solution 5

There are some useful how to guides about using EF+Connector/Net+EFMigrations.. I think it would help if you check them. The latest version of Connector/Net (6.6.4) depends on EF 4.3.1 The Connector/Net version supporting EF 5 is still in code stage.

HowTo using EF Migrations and Connector/Net

Upgrading to Code Based Migrations EF 4.3.1 with Connector/Net 6.6

Hope this helps!

View more solutions