Code first with MySQL and EF6

Last week, I had to start a .NET Project that requires a database – our shared server however only provides MySQL as a database solution (Low cost solution has its price eh?).

Let’s have a quick look at how to get started with MySQL and EF6 with Code first approach.

Dependencies

Let’s install the main libraries that we need to get this working. Run the following install commands on Package Manager Console :

Install-Package EntityFramework
Install-Package MySQL.Data.Entities

Create the database

Let’s create the database – login with your favourite mysql client with any account that has permission to create database e.g. root. I create mine quickly with the default mysql cli client.

create database library;

Configuration

Let’s now configure our app to use the MySQL client service provider and add our connection string.

The official MySQL website has some documentation for this here

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
   <connectionStrings>
    <add name="LibraryContext" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;
      port=3306;database=library;uid=root;password=******"/>
  </connectionStrings>
  <entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
    <providers>
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, 
        MySql.Data.Entity.EF6" />
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, 
        EntityFramework.SqlServer" />
    </providers>
  </entityFramework>
</configuration>

Models

Let’s add some simple data models to try code first out

public class LibraryContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
}

public class Book
{
    [Key]
    public int Id { get; set; }
    public string Title { get; set; }
    public virtual Author Author { get; set; }
}

public class Author
{
    [Key]
    public int Id { get; set; }
    public string Forenames { get; set; }
    public string Surname { get; set; }
}

Migrations

We’ll be running Entity Framework migration commands via the Package Manager Console. You can find a fairly comprehensive guide to the migration commands here.

In the Package Manager Console, enable migrations by typing in the following command.

Enable-Migrations

then add our current context as our initial migration

Add-Migration Initial

And finally, update the database with our initial migration

Update-Database

After running this command, we should now have a database created for us.

mysql> show tables;
+--------------------+
| Tables_in_library  |
+--------------------+
| __migrationhistory |
| authors            |
| books              |
+--------------------+

Testing

Lets try to do things with our models with some tests or a simple console app.

For mine, I did a simple unit test

[TestMethod]
public void TestMethod1()
{
    using (var libraryContext = new LibraryContext())
    {
        var author = new Author() { Forenames = "Robert C.", Surname = "Martin" };
        var book = new Book() { Author = author, Title = "Clean Code" };

        libraryContext.Authors.Add(author);
        libraryContext.Books.Add(book);
        libraryContext.SaveChanges();

        var bookResult = (from b in libraryContext.Books
                          where book.Author.Surname == "Martin"
                          select b).First<Book>();

        Assert.AreEqual("Clean Code", bookResult.Title);

        libraryContext.Books.Remove(book);
        libraryContext.Authors.Remove(author);

        libraryContext.SaveChanges();

        var bookCount = (from b in libraryContext.Books
                         select b).Count<Book>();

        Assert.AreEqual(0, bookCount);
    }
}

Run the test to make sure everything works.

Modifying our models

Let’s modify our context to check if we can produce migration scripts for updates.

For mine, I simply added some length constraints like so:

public class LibraryContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
}

public class Book
{
    [Key]
    public int Id { get; set; }
    [MaxLength(255)]
    public string Title { get; set; }
    public virtual Author Author { get; set; }
}

public class Author
{
    [Key]
    public int Id { get; set; }
    [MaxLength(128)]
    public string Forenames { get; set; }
    [MaxLength(128)]
    public string Surname { get; set; }
}

Again, run the Add-Migration command

Add-Migration Length_Constraints

This should produce some migration code like so:

namespace MySQL_Entity.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class Length_Constraints : DbMigration
    {
        public override void Up()
        {
            AlterColumn("dbo.Authors", "Forenames", c => c.String(maxLength: 128, storeType: "nvarchar"));
            AlterColumn("dbo.Authors", "Surname", c => c.String(maxLength: 128, storeType: "nvarchar"));
            AlterColumn("dbo.Books", "Title", c => c.String(maxLength: 255, storeType: "nvarchar"));
        }

        public override void Down()
        {
            AlterColumn("dbo.Books", "Title", c => c.String(unicode: false));
            AlterColumn("dbo.Authors", "Surname", c => c.String(unicode: false));
            AlterColumn("dbo.Authors", "Forenames", c => c.String(unicode: false));
        }
    }
}

Now update the database again

Update-Database

The result

mysql> describe books;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| Id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| Title     | varchar(255) | YES  |     | NULL    |                |
| Author_Id | int(11)      | YES  | MUL | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

Findings

I’ve only used this type of setup for a few days so far – and currenty, no hiccups. Will keep this updated if I find anything troubling.

You can view and download the entire solution on github.