Gunnar Peipman's ASP.NET blog

ASP.NET, C#, SharePoint, SQL Server and general software development topics.

Sponsors

News

 
 
 
DZone MVB

Links

Social

Getting MySQL work with Entity Framework 4.0

Does MySQL work with Entity Framework 4.0? The answer is: yes, it works! I just put up one experimental project to play with MySQL and Entity Framework 4.0 and in this posting I will show you how to get MySQL data to EF. Also I will give some suggestions how to deploy your applications to hosting and cloud environments.

MySQL stuff

As you may guess you need MySQL running somewhere. I have MySQL installed to my development machine so I can also develop stuff when I’m offline. The other thing you need is MySQL Connector for .NET Framework. Currently there is available development version of MySQL Connector/NET 6.3.5 that supports Visual Studio 2010.

Before you start download MySQL and Connector/NET:

If you are not big fan of phpMyAdmin then you can try out free desktop client for MySQL – HeidiSQL. I am using it and I am really happy with this program.

NB! If you just put up MySQL then create also database with couple of table there. To use all features of Entity Framework 4.0 I suggest you to use InnoDB or other engine that has support for foreign keys.

Connecting MySQL to Entity Framework 4.0

Now create simple console project using Visual Studio 2010 and go through the following steps.

1. Add new ADO.NET Entity Data Model to your project.

For model insert the name that is informative and that you are able later recognize.

Adding ADO.NET Entity Data Model

Now you can choose how you want to create your model. Select “Generate from database” and click OK.

Choosing source of model contents

2. Set up database connection

Change data connection and select MySQL Database as data source. You may also need to set provider – there is only one choice. Select it if data provider combo shows empty value.

Changing data source

Click OK and insert connection information you are asked about. Don’t forget to click test connection button to see if your connection data is okay.

Setting connection properties

If everything works then click OK.

3. Insert context name

Now you should see the following dialog. Insert your data model name for application configuration file and click OK.

Data connection settings

Click next button.

4. Select tables for model

Now you can select tables and views your classes are based on. I have small database with events data. Uncheck the checkbox “Include foreign key columns in the model” – it is damn annoying to get them away from model later. Also insert informative and easy to remember name for your model.

Select tables for model

Click finish button.

5. Define your classes

Now it’s time to define your classes. Here you can see what Entity Framework generated for you. Relations were detected automatically – that’s why we needed foreign keys. The names of classes and their members are not nice yet.

Automatically created classes

After some modifications my class model looks like on the following diagram. Note that I removed attendees navigation property from person class.

Final version of classes

Now my classes look nice and they follow conventions I am using when naming classes and their members.

NB! Don’t forget to see properties of classes (properties windows) and modify their set names if set names contain numbers (I changed set name for Entity from Entity1 to Entities).

6. Let’s test!

Now let’s write simple testing program to see if MySQL data runs through Entity Framework 4.0 as expected. My program looks for events where I attended.


using(var context = new MySqlEntities())
{
    var myEvents = from e in context.Events
                    from a in e.Attendees
                    where a.Person.FirstName == "Gunnar" &&
                            a.Person.LastName == "Peipman"
                    select e;
 
    Console.WriteLine("My events: ");
 
    foreach(var e in myEvents)
    {
        Console.WriteLine(e.Title);
    }
}
 
Console.ReadKey();

MySQL Entity Framework test succeededAnd when I run it I get the result shown on screenshot on right. I checked out from database and these results are correct.

At first run connector seems to work slow but this is only the effect of first run. As connector is loaded to memory by Entity Framework it works fast from this point on.

Now let’s see what we have to do to get our program work in hosting and cloud environments where MySQL connector is not installed.

Deploying application to hosting and cloud environments

If your hosting or cloud environment has no MySQL connector installed you have to provide MySQL connector assemblies with your project. Add the following assemblies to your project’s bin folder and include them to your project (otherwise they are not packaged by WebDeploy and Azure tools):

  • MySQL.Data
  • MySQL.Data.Entity
  • MySQL.Web

You can also add references to these assemblies and mark references as local so these assemblies are copied to binary folder of your application. If you have references to these assemblies then you don’t have to include them to your project from bin folder.

Also add the following block to your application configuration file.


<?xml version="1.0" encoding="utf-8"?>
<
configuration>
...
  <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.0.0, Culture=neutral,
                  PublicKeyToken=c5687fc88969c44d
 
        />
    </
DbProviderFactories
>
  </
system.data>
...
</configuration
>

Conclusion

It was not hard to get MySQL connector installed and MySQL connected to Entity Framework 4.0. To use full power of Entity Framework we used InnoDB engine because it supports foreign keys. It was also easy to query our model. To get our project online we needed some easy modifications to our project and configuration files.

Comments

eksimba said:

I've also been using EF4 with MySQL, and it works great. To manage the database I've been using the MySQL Workbench: http://wb.mysql.com/

# December 9, 2010 11:04 AM

DigiMortal said:

Thanks for pointing out WB :)

# December 9, 2010 2:12 PM

David Hill said:

MySQL does work very well except for stored procedures. I couldn't get it to map over any of the stored procedure parameters; we gave up with the EF in the end due to this bug!

# December 10, 2010 7:56 AM

DigiMortal said:

Thanks for valuable feedback, David!

MySQL EF4 support is right now under development and the version pointed out here is also located under dev site of MySQL. So, please don't go to production with it yet.

It is good to know that support for stored procedures has problems right now. If somebody else faces problems with current version then please feel free to leave me a comment here.

# December 10, 2010 5:37 PM

Wenbin Xu said:

Hi DigiMortal, I got exactly the same problem as David pointed out - the stored procedure stubs generated from EF 4.0 model do NOT contain any parameters. This is a serious issue preventing us from using MySQL with EF 4.0.

Any idea when this bug will be fixed?

Thanks a lot in advance!

# January 16, 2011 12:15 PM

Anders V said:

How the *bleep* did you get this to work? Did you get it to work when deployed to Azure as well?

I've spent quite a bit of time on this issue earlier in 2010, and the big problem was that the Connector/Net from MySQL was not x64 and therefore could not run in Windows Azure. Does that not create problems here?

Thanks,

Anders

# January 17, 2011 8:35 PM

jsaski said:

This is great, and I have been trying to do this myself.

The problem is that when I do step 2, MySql is not there as a provider.

I am using 6.3.5 connector and VS2010, how did you get 2010 to reconize the provider?

# January 21, 2011 6:43 AM

DigiMortal said:

I wrote this blog posting when building stuff on my 32-bit laptop and I faced no issues. The version of connector that supports VS2010 is still under (hopefully heavy) development and it may have issues. As I am not very familiar with MySQL guys work I suggest you to ask help from MySQL forums.

# January 24, 2011 8:10 AM

Alex said:

I thought The ADO.Net Data Service Item  has been replaced by the WCF Data Service in VS2010

# February 5, 2011 12:24 PM

fell said:

Hi DigiMortal, I got exactly the same problem as David pointed out - the stored procedure stubs generated from EF 4.0 model do NOT contain any parameters.

# February 8, 2011 1:56 PM

DigiMortal said:

Thanks for feedback, fell. It is known issue with current version of MySQL EF provider. Hopefully it will be solved during next releases as guys are moving closer to stable release.

# February 13, 2011 6:30 AM

mwhouser said:

If you have an EF member with ConcurrencyMode = Fixed, then inserts fails in many circumstances.

A sample project can be found here:  www.houser.ca/.../MySQLEFTest.zip

# February 22, 2011 1:57 PM

BlackLightMobile said:

Hi,

Are you able to create your entities first and then generate the tables from that?

# March 2, 2011 6:33 AM

Alfredo said:

I am running Windows 2008 Server R2 and connector 6.4 latest version of MySQL and Visula Studio 2010 Ultimate when I choose to add a new ADO.NET entity data amodel I get the option to choose MySQL I connect to my database, choose my table when Finish I get this error. The connection string gets created in the app.config and .edms file is created but Entity framework does not generate anything for me (no entities). Can you tell me what is the problem?

Unable to generate the model because of the following exception: 'An error occurred while executing the command definition. See the inner exception for details.

Unknown column 'UnionAll3.Ordinal' in 'field list'

# June 10, 2011 5:37 PM

hajan said:

Gun, very nice post! :)

# August 19, 2011 4:13 AM

softwareconsultancy said:

Thanks for that - I used DD for what turned out to be a fairly large project in .net 3.5 - not an experience without pain. Has the mysql stored procedure parameter mapping been sorted out yet, as I now need to evaluate usage for a new project.

Thanks

# September 6, 2011 3:46 AM

Daniel said:

Thanks for this. A last question, is there any workaround on speeding up entities framework first connection?, in my case it looks that upon an inactivity time the next connection becames slow again, thanks in advance

# October 28, 2011 8:55 AM

DigiMortal said:

Daniel, it should be problem on MySQL connector side. Just take last version of it or report this problem to appropriate MySQL forum(s)

# November 1, 2011 5:07 PM

Ricardo Nascente said:

Thanks man, it really helped me.

# November 5, 2011 6:13 PM

Sonia said:

Hi DigiMortal - Has the mysql stored procedure parameter mapping been sorted out yet ? I am creating a funtionImport for stored procedure using model browser, it generates the function but parameters are not specified there.

Can you please help?

# December 2, 2011 1:22 PM

Kasi P said:

We are facing issues if we have one-many relations between the tables.

Here one Department is having multiple Employees.

While saving Entity, we are getting following error

Error:     You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (`COLUMN001` = '1c4f160b-bf7c-4da9-99f6-053fdfd20d2e') AND (`COLUMNC04` = ' at line 1"}                System.Exception MySql.Data.MySqlClient.MySqlException}

While Fetching entity with ".Include"

var tem4 = (from p in ctx.Department.Include("Employee") where p.DeptId == "4F1B4A73-B41B-4CA6-9F0A-10640D0BA88C" select p).ToList();

Error: Object must implement IConvertible.

Please advise us to solve these issues.

Tanks

Kasi

# December 12, 2011 2:20 AM

GopiKrishna said:

Hello Guys,

I followed this tutorial.

Iam using VisualStudio2010 , MySql Connector 6.

4.4 .

Myrproblem: I Want to connecto to mysql via EntityFramework , even though it shows it is connecting to mysql , Iam unable to see Entities in my model browser.

Please help me.

# February 13, 2012 3:00 AM

asdf said:

Nope, doesn't work. It should work, but EF tries to connect to MSSql no matter what I do. Some days I just hate the world, everything in it, everything that ever was and ever will be, with a passion that burns like sulfuric acid.

# February 17, 2012 10:55 AM

Andres felipe said:

My app can't read the assembly I get this error:

Could not load file or assembly 'MySql.Data, Version=5.1.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

I have the dll's and <system.data> tags correctly.

# March 12, 2012 1:06 PM

DigiMortal said:

Andres, make sure that what assemblies your app is actually trying to load and where it is trying to load them from (what folder or is it GAC).

# March 12, 2012 4:56 PM

samegoldinbutbetter said:

Does the latest EF version work with mySQL? I mean, does it map all objects: tables, SP, functions, and .....?

# October 9, 2012 12:54 PM

DigiMortal said:

I tried only with tables as it is enough in most cases.

# October 18, 2012 10:30 AM

Dorababu said:

How to use routines or stored procedures in entity framework along with mysql.

# December 8, 2012 3:48 AM

Jun said:

Does the configuration shown apply to a windows services as well?

I have installed the .NET connector but can't get this to work in Windows 2008 SP2.

# December 14, 2012 5:55 AM

shadower said:

gracias... muy útil/thanks... very helpful.

# February 20, 2013 12:58 PM