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.
