Using MySql with Entity Framework 4 and the Code-F

Question

I thought I'd experiment a bit with Scott Guthrie's latest post on code-first dev with Entity Framework 4. Instead of using Sql Server, I'm trying to use MySql. Here are the relevant parts of my web.config (this is an Asp.Net MVC 2 app):

<connectionStrings>
    <add name="NerdDinners"
         connectionString="Server=localhost; Database=NerdDinners; Uid=root; Pwd=;"
         providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>
  <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.2.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>

Just like the tutorial, I'm expecting EF4 to generate the db for me automatically. Instead, it throws a ProviderIncompatibleException, with an inner exception complaining that the NerdDinners database doesn't exist.

Fair enough; I went and created the MySql db for it just to see if things would work, and got another ProviderIncompatibleException instead. This time, "DatabaseExists is not supported by the provider".

I'll admit, this is the first time I'm really delving into Entity Framework (I've stuck mostly to Linq to Sql), and this is all running on the Code-First CTP released only last week. That said, is there something I'm doing wrong here, or a known problem that can be worked around?

Answer 1

Right, finally got it working with a few points of interest.

• Cannot create a DB, must exist already
• You have to create a connection string for each DB contest using the DBContext name (in the above example a connectionstring must exist with the name "NerdDinners"), not just a default one (else it will use SQL)
• It will use the name of the DBSet name you use to define your context as the name of the table, so be careful when naming them.

All in all, a long road but there in the end

**Update Another point to note, when deploying your MVC site using MySQL you will most like need also add a DataFactory to your web.config. Usually because of the difference in MySql connectors out there and the versions of MySQL that are supported. (answer found through other sources after much head scratching) Just add:

  <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.3.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" /> 
    </DbProviderFactories> 
  </system.data>

As a seperate section to your web.config making sure to set the version number of the MySQL.Data.dll you deploy with the site (also a good idea to "copy as local" your MySQL DLLs to ensure compatibility.

Answer 2

Another point of interest - If you add the "MySQL Data Provider" entry to your local machine.config (C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config in my case) you can then connect to your MySql instance through Visual Studio...

Answer 3

This question and answer has been very useful for me migrating a larger EF project from SQL to mySQL so I thought I would add my notes and hope they are useful:

As noted the name of the connection string has to match the name of the Class which extends the System.Data.Entity.DbContext.

There seems still no way to create tables in EF using the mySQL connector but you can use and modify the SQL create scripts to generate the mySQL tables. The easiest way I found to do this was to comment in and out the OnModelCreating function on the extended DbContext depending on whether the code was needed to recreate tables. If I find I am doing this more often I plan to resolve this by using dependency injection and have seperate classes based on either a mySQL or MSSQL configuration.

I found it easier to make sure that the dev boxes and servers had the correct mySQL connector .dll packaged in the release than mess with the DbFactoryProviders in the webconfig. Getting the packaging correct in the project/solution build package meant I only needed the connection string lines and not the DbFactoryProviders lines which I found to be difficult to work consistently across a number of machines.

I needed to change the mySQL Identifier Case Sensitivity from the setting I had of 0 to 1. Without this setting the SQL that EF connected could not find the tables that were there due to the mixed case names of my objects compared to the fixed case tables that mySQL creates.