MySQL and Entity Framework 5 (EF 5) Code First HOW-TO

MySQL is cool, Entity Framework is too... so why not have them work together in Code First mode?

In version 5 of Entity Framework, things have improved a lot, and we can all now admit that it's really boring to write SQL statements anymore... Probably it's time to learn or to switch to Entity Framework!

The "Code First" approach in EF, makes things even more interesting. In this way, you do not bother about making the tables and their associations before you write code. Instead, first you write your POCO (Plain Old CLR Object) classes, and then, on first database access from within your code, the database and its tables are automatically generated and accessed! Cool, huh?

In MS-SQL Server + Entity Framework Code First scenarios, things are quite straightforward, or implemented quite often. But what about MySQL? Well... there is not much on the internet about this case. And all references are rather old, or not detailed. So, I decided to write something about this.

In 8 steps you will get tables automatically created in MySQL from Entity Framework, reflecting your class properties!

Ingredients needed:

• Visual Studio 2010 (2012 should do too) with NuGet Package Manager installed. Download from here.
• .NET 4.0 Framework, download from here (not tested with .NET 4.5 yet)
• MySQL Database, download from here
• MySQL Connector .NET version 6.7.4, download from here and do a typical installation (note: assuming that higher version is always better is not the case for this library, which means that higher version than this may not work as expected). As we can see here, this version finally suppports EF 5.0
• Optional: A MySQL GUI database management tool to see the results (otherwise you use the MySQL command-line tool...). Suggestions: Navicat (commercial), HeidiSQL (free)

Step-by-step instructions:

1. Start a Command Line Project

Start Visual Studio and select New Project... Select Windows template and Console Application type of project. Give select path and application name and click OK.

2. Add the EF reference through NuGet

After solution is created, right click on project in Solution Explorer and select "Manage NuGet Packages..."

 

Select "Entity Framework" and click "Instal"

In the pop-up window "License Agreement" that appears, select "I Accept".

After that, the "Entity Framework" in NuGet will appear with a green "checkbox" icon. Click "Close" to go back to the Visual Studio main window.

3. Add the MySQL and System.Configuration references 

Right click on the "References" of the project in Solution Explorer and select "Add Reference...":

Select the "Browse" tab and navigate to the path where the needed MySQL assemblies are located. In my case, it was under C:\Program Files (x86)\MySQL\MySQL Connector Net 6.7.4\Assemblies\v4.0 (in case of .NET 4.5, I suppose you should look under the v4.5 folder):

Select both MySql.Data.dll and MySql.Data.Entity.dll and click OK.

Again, repeat the same process of adding a reference, but this time don't go to Browse tab, but the .NET one. Select the System.Configuration (4.0) and click OK.

We will need this reference to be able to access the connection string from App.config during run-time. 

4. Modify App.config file

Go to file App.config through Solution Explorer. By default, after the above actions, it should look like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
  </entityFramework>
</configuration>

Look at the highlighted area. This is the Entity Framework section, and as you can see the default option for database is - what else - SQL Server. In our case, we don't want that of course, so we modify as follows:

  <entityFramework>
    <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity" />
  </entityFramework>

We will also need to add another section within the configuration element, which in this case is the system.data section:

  <system.data>
    <DbProviderFactories>
      <remove invariant="MySql.Data.MySqlClient"/>
      <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.7.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
    </DbProviderFactories>
  </system.data>

What does the above configuration do? Well, it registers the MySql data provider. See more here

Of course, we would need a connection string to our database too. So, let's add the ConnectionStrings section anywhere withing the configuration element:

  <connectionStrings>
    <add name="ConnectionStringName" connectionString="server=localhost;Database=ef5;uid=root;pwd=1234;" providerName="MySql.Data.MySqlClient" />
  </connectionStrings>

The above database name/username (uid) and password (pwd) are indicative. Apply the ones from your MySQL installation.

5. Create an entity class to be stored/persisted

For our demo purposes, we need some kind of class/structure to store. Let's have a Customer class. Right click on project name, select "Add", then "Class..." and name the file Customer.cs

Let's give our class some properties, such as LastName and FirstName:

public class Customer
{
    public int Id { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }
}

But what is the Id property as highlighted? Well this is the default primary key for Entity Framework. Of course, you can use other properties as keys with the proper annotations, but that's outside of the scope of this article. Let's stick to Id for now. Remember, you can't have an entity without a key, otherwise you will get an exception.

6. Create your context class

To manage our entities through Entity Framework, we need some sort of "manager class", which in this case is a DbContext class. We create another class file as we did with Customer, but this time we call it MySqlContext. Generally, the naming convention is *Context (see more about this here). Our class will look like this:

using System.Data.Entity;

namespace MySqlEFArticle
{
    public class MySqlContext : DbContext
    {
        public DbSet<Customer> Customers { get; set; }

        public MySqlContext(string connString) : base(connString)
        { }
    }
}

Some comments about the code above:

• Our class inherts from DbContext. This is a must.
• We define properties of type DbSet<your-class> where your-class is anything you would like to persist/store in database, like Customer, Order, Contact classes etc. You can have multiple such class properties.
• Our constructor is defined in such a way so that we can have the connection string as a parameter. This is very important, as by default the context without it does allow access to the connection string.
• The namespace of course is whatever your application name is.

7. Write the main implementation

So let's go back now to Program.cs and write the following code:

using System;
using System.Configuration;
using System.Data.Entity;

namespace MySqlEFArticle
{
    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MySqlContext>());

            var connString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ToString();

            using (var db = new MySqlContext(connString))
            {
                var customer = new Customer { LastName = "van Leusden", FirstName = "Dimitris" };
                db.Customers.Add(customer);
                db.SaveChanges();
            }
            Console.WriteLine("All ok!");
            Console.Read();
        }
    }
}

Let's take a look at what is taking place above. The line

Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MySqlContext>());

makes sure that the database will be created if it doesn't exist. It will also drop the database if the model (in this case our Customer) changes (e.g. properties or property data types). As you can see, our Context class (this time MySqlContext) must be passed. 

This line

var connString = ConfigurationManager.ConnectionStrings["ConnectionStringName"].ToString(); 

of course loads the connection string in app.config and stores it in the connString variable.

Later on, we create an instance of our context class within a using statement. With using statement you can be assured that the Context is closed and disposed after its use. So, one thing less to worry about... Note here that we are also passing the connection string in the constructor, since we adjusted that in MySqlContext.

Then we create a dummy Customer and we initialize it. We add it to the Customers DbSet and then we call SaveChanges(). This is where the magic takes place. Here is where Entity Framework realizes that there is no database and/or tables and it creates them.

Note that apart from the Customers table that was created, there is another table called __migrationhistory. This table is to track Migrations, or the changes that you will make in the model (Customer) that will need to be reflected in the database. This is outside of the scope of this article, but you can read more about it here.

8. Compile and run

Compile and run by pressing the key F5. The console application will appear and create a table Customers in your MySQL database, with a sample record! No table design or anything! 

If you run the program again, it will add another customer with the same data. That is because the policy is to drop the database only if the model is changed. If you replace DropCreateDatabaseIfModelChanges with DropCreateDatabaseAlways then the database will always be dropped, and no matter how many times you run the application, you will only have one customer.