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

 

 

6 Responses to “6 Steps To Get Entity Framework 5 Working with MySql 5.5”

Read below or add a comment...

  1. jonah says:

    Nice tutorial! I have one question though, where do we put the codes to Configure the Custom MySql Database Initializer?

  2. Gabriel RB says:

    Help me so much.

    Thanks.

  3. Ki Won Kim says:

    Where is seed method?

    replace line 97
    context.Database.Create();

    to

    context.Database.Create();
    Seed(context);
    context.SaveChanges();

    insert 126 line above same contents.

    and append below method to override

    protected virtual void Seed(TContext context)
    {
    }

    Thanks

    • Joshua says:

      Thanks for the comment – seeding the database is only required if you require test data or have initial data that needs to be created in your database.

  4. caschan says:

    Can anybody provide above example with full working code downlodable?

  5. przemek pod says:

    hello, now from nuget i can get version 6.7.4 but when i set conn string and DbProviderFactories i got exception on context(generated by mysql visualstudio tools- so i got my whole edmx from mysql db) taht provider is not installed or not configured. Any ideas?

Leave A Comment...

*