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:

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