MySQL Connector/NET and Entity Framework

How to let MySQL Connector/NET and Entity Framework play nicely. This blog post is a quicky and an oldy… Since we still receive a reasonable amount of questions about this topic at the customer service of my employer I’m posting this here, with some commonly made mistakes and points of attention.

How to let MySQL Connector/NET and Entity Framework play along nicely…

MySQL Connector/NET version 6.6.4.0

Basically, it all comes down to your web.config configuration if you want to use MySQL Connector/NET and Entity Framework. You can use the website configuration from my article “MySQL Connector/NET 6.5 in partial trust” to set up MySQL Connector/NET for your website application. There are a few other settings you need to look at:

About Entity Framework

Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.

Visual Studio 2008: incorrect connection string

Visual Studio 2008 used to generate an incorrect connection string where it uses ” marks instead of ‘. I can’t imagine this hasn’t been fixed 😉 but do check it in your web.config file if you encounter error message with MySQL Connector/NET and Entity Framework.

Incorrect generated connection string

<add name="modelEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;
  provider=MySql.Data.MySqlClient;
  provider connection string="server=mysql.server.ext;
    User Id=mysqluser;
    Persist Security Info=True;
    database=mysqldb;
    password=mysqlpwd""
  providerName="System.Data.EntityClient"
/>

Correct connection string

<add name="modelEntities" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;
  provider=MySql.Data.MySqlClient;
  provider connection string='server=mysql.server.ext;
    User Id=mysqluser;
    Persist Security Info=True;
    database=mysqldb;
    password=mysqlpwd'"
  providerName="System.Data.EntityClient"
/>

DbProviderFactories

You also have to verify you are using the correct version of assemblies. Therefor it’s wise to add a DbProviderFactories-section to your web.config, to make sure you are loading the assemblies in your ~/bin folder.

<system.data> 
  <DbProviderFactories> 
    <add name="MySQL Data Provider"
      invariant="MySql.Data.MySqlClient"
      description=".Net Framework Data Provider for MySQL"
      type="MySql.Data.MySqlClient.MySqlClientFactory,
        MySql.Data, Version=6.6.4.0,
        Culture=neutral,
        PublicKeyToken=c5687fc88969c44d"
    /> 
  </DbProviderFactories> 
</system.data>

ASP.NET 4.0, MVC 3 en Entity Framework Code First

For EntityFramework 4.3.1.0 en trustLevel=”medium”, add requirePermission=”false” to the entityFramework node of your web.config file:

<configSections>
<!--
[...]
-->
  <section name="entityFramework"
    type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection,
    EntityFramework, Version=4.3.1.0, Culture=neutral, 
    PublicKeyToken=b77a5c561934e089" requirePermission="false" />

<!--
[...]
-->
</configSections>

Source: ScottGu: Code-First Development with Entity Framework 4

Hint: MySQL table engine

To use full power of Entity Framework, use the InnoDB table engine because it supports foreign keys. Learn how to convert MyISAM tables to InnoDB.