6 Steps To Get Entity Framework 5 Working with MySql 5.5

After much battling with configuration and such I have finally managed to get EF5 Code First and MySql 5 working together using the Oracle/MySql .NET Connector 6.5.4 or the official Connector version 6.6.4. This configuration does not currently support Migrations.

The steps are as follows:

1. Grab EF 5 from NuGet
2. Grab MySql.Data and MySql.Data.Entity from NuGet (6.5.4) or MySql (6.6.4)
3. Configure a MySql Data Provider
4. Configure a MySql Connection String
5. Create a Custom MySql Database Initializer
6. Configure the Custom MySql Database Initializer

1 & 2 are selfexplanatory, if you use the official 6.6.4 version then steps 5 & 6 above are not required. The rest of this page will presume you are using the NuGet version 6.5.4.

Configure the MySql Data Provider

Add this to your configuration.

<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>

If you requre the strong name then use: type=”MySql.Data.MySqlClient.MySqlClientFactory,MySql.Data, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d”

 Configure a MySql Connection String

<connectionStrings>
  <add name="ConnectionStringName" connectionString="Datasource=hostname;Database=schema_name;uid=username;pwd=Pa$$w0rd;" providerName="MySql.Data.MySqlClient" />
</connectionStrings>

Create a Custom MySql Database Initializer

The standard Database Initializers for Entity Framework 5 don’t work with the MySql .Net Connector 6.5.4, there is a full code sample for DropCreateAlways, DropCreateIfModelChanges & CreateIfNotExists at the bottom of this post based on Brice Lambson’s basic customized initializer: http://brice-lambson.blogspot.se/2012/05/using-entity-framework-code-first-with.html

Configure the Custom MySql Database Initializer

Using code

 Database.SetInitializer(new DropCreateMySqlDatabaseIfModelChanges<Namespace.YourContext>());

Using configuration and setting the default connection factory (which is optional)

<configSections>
  <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</configSections>
<entityFramework>
  <contexts>
    <context type="Namespace.YourContextName, AssemblyName"><databaseInitializer type="Habanero.EntityFramework.MySql.DropCreateMySqlDatabaseIfModelChanges, AssemblyName"></databaseInitializer>
    </context>
  </contexts>
  <defaultConnectionFactory type="MySql.Data.MySqlClient.MySqlClientFactory,MySql.Data" />
</entityFramework>

Full code for custom MySql Database Initializers

This code is provided as is with a “works on my machine” stamp! This is based entirely on Brice Lambson’s code at http://brice-lambson.blogspot.se/2012/05/using-entity-framework-code-first-with.html

There are three initializers in the code:

• DropCreateMySqlDatabaseAlways<TContext>

• DropCreateMySqlDatabaseIfModelChanges<TContext>

• CreateMySqlDatabaseIfNotExists<TContext>

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using MySql.Data.MySqlClient;

namespace Habanero.EntityFramework.MySql
{
    public class DropCreateMySqlDatabaseAlways<TContext>
      : MySqlDatabaseInitializer<TContext> where TContext : DbContext
    {
        public override void InitializeDatabase(TContext context)
        {
            context.Database.Delete();
            CreateMySqlDatabase(context);
        }
    }

    public class DropCreateMySqlDatabaseIfModelChanges<TContext>
     : MySqlDatabaseInitializer<TContext> where TContext : DbContext
    {
        public override void InitializeDatabase(TContext context)
        {
            bool needsNewDb = false;
            if (context.Database.Exists())
            {
                if (!context.Database.CompatibleWithModel(false))
                {
                    context.Database.Delete();
                    needsNewDb = true;
                }
            }
            else
            {
                needsNewDb = true;
            }
            if (needsNewDb) CreateMySqlDatabase(context);
        }
    }

    public class CreateMySqlDatabaseIfNotExists<TContext>
   : MySqlDatabaseInitializer<TContext> where TContext : DbContext
    {
        public override void InitializeDatabase(TContext context)
        {
            if (context.Database.Exists())
            {
                if (!context.Database.CompatibleWithModel(false))
                {
                    throw new InvalidOperationException(
                        "The model has changed!");
                }
            }
            else
            {
                CreateMySqlDatabase(context);
            }
        }
    }

    public abstract class MySqlDatabaseInitializer<TContext>
     : IDatabaseInitializer<TContext>
         where TContext : DbContext
    {
        public abstract void InitializeDatabase(TContext context);

        protected void CreateMySqlDatabase(TContext context)
        {
            try
            {
                // Create as much of the database as we can
                context.Database.Create();

                // No exception? Don't need a workaround
                return;
            }
            catch (MySqlException ex)
            {
                // Ignore the parse exception
                if (ex.Number != 1064)
                {
                    throw;
                }
            }

            // Manually create the metadata table
            using (var connection = ((MySqlConnection)context
                .Database.Connection).Clone())
            using (var command = connection.CreateCommand())
            {
                command.CommandText =
    @"
CREATE TABLE __MigrationHistory (
    MigrationId mediumtext NOT NULL,
    Model mediumblob NOT NULL,
    ProductVersion mediumtext NOT NULL);

ALTER TABLE __MigrationHistory
ADD PRIMARY KEY (MigrationId(255));

INSERT INTO __MigrationHistory (
    MigrationId,
    Model,
    ProductVersion)
VALUES (
    'InitialCreate',
    @Model,
    @ProductVersion);
";
                command.Parameters.AddWithValue(
                    "@Model",
                    GetModel(context));
                command.Parameters.AddWithValue(
                    "@ProductVersion",
                    GetProductVersion());

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        private byte[] GetModel(TContext context)
        {
            using (var memoryStream = new MemoryStream())
            {
                using (var gzipStream = new GZipStream(
                    memoryStream,
                    CompressionMode.Compress))
                using (var xmlWriter = XmlWriter.Create(
                    gzipStream,
                    new XmlWriterSettings { Indent = true }))
                {
                    EdmxWriter.WriteEdmx(context, xmlWriter);
                }

                return memoryStream.ToArray();
            }
        }

        private string GetProductVersion()
        {
            return typeof(DbContext).Assembly
                .GetCustomAttributes(false)
                .OfType<AssemblyInformationalVersionAttribute>()
                .Single()
                .InformationalVersion;
        }
    }
}