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.

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 "Connect to a MySQL Database Using ASP.NET" 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.

Entity Framework

Visual Studio 2008

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

The connection string VS 2008 generates for Entity Framework is incorrect:

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

You need to correct this.

Correct connection string in Visual Studio 2008

The correct, working connection string for Entity Framework in .NET as follows:

<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's Blog "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. Here you find more information about the InnoDB table engine and its performance.

I just republished this post (2022-11-30) as it got deleted somehow.

Thank you very much! <3 ❤️