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: MySQL Community Server Connector/NET 6.3.5 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. Now you can choose how you want to create your model. Select “Generate from database”
and click OK. 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. 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. 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. 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. 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. After some modifications my class model looks like on the following diagram. Note that I removed attendees navigation property from person class. 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();
And 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.
