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.

Answer 1

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.

Answer 2

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.