Using MySql with Entity Framework 4 and the Code-First Development CTP

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?

Sapir

You are right, a mysql provider does exist (#76988). It's possible the CTP bits have bugs or that the MySQL provider is missing functinoality (or both). – Funiculus

are you able to use simple membership in MVC 4 with MySQL? – Njord

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.

Grigson

Marked yours as the answer, since you found a way :). Awesome work! – Sapir

Did you have to create the tables yourself or does it generate the tables for you? – Adapt

Entity framework still cannot create tables unfortunatly, so you will have to do that yourself (also note on some hosts tablenames and column names are case sensitive). – Grigson

One work around though, is to test and generate your DB using SQL Express or SQL CE, and then dump the creation script for the DB. you can then run that on MySQL to create the DB (taking care with any non-post tsql differences) – Grigson

Can you clarify "It will use the name of the DBSet name you use to define your context as the name of the table"? On the SQL Server side, it uses the Model name (Class name), pluralized if you leave that Convention enabled. Are you saying in MySQL it uses the property name you assign in the DbContext to that DbSet instead? That would seem a strange and arbitrary change of approach... – Outcross

Thanks, Above worked for me in Code First approach. Just a note, I was using different version of MySql.Data assembly, so just changed the Version 6.3.6.0 to Version=6.4.4.0 – Moffatt

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

Henrion

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.

Danica