Using MySQL as the Model Store with ASP MVC in Azure

Introduction

Microsoft is allowing students with Dreamspark accounts to have free Azure web hosting. However, using MS SQL Server is not free, but MySQL support is free (for small DBs) in Azure. So for the benefit of all the students who want to write ASP MVC programs and use Azure, I looked into how to do this. Searching the web, there are many articles that have parts of the puzzle, but I could not find a working example. This "Frankenstein" program pulls in bits of code from several articles to create a simple example. I am assuming the reader is familiar with building MVC apps with MS SQL Server, and just wants to know how to swap in MySQL.

Background

A good bit of the primary code came from here, and here and solving the "The Migration History table, __migrationhistory" problem came from here. I strongly suggest you look over those articles if there is code in my project that you don't understand. I didn't repeat all the good words that are in those articles.

Using the Code

This is a small version of the Contoso University MVC example that shows up in many articles, this one has only students as data to remove clutter and focus on the MySQL connection challenge. As you paste my code, various class references will be undefined, however the new VS2015 works nicely, just right click and select "quick fix" and let VS add the correct Using statements for you. Be very careful with Student and Students. Student is a class and a class file. Students is the DB table. An extra or missing "s" will break the project.

To recreate it, using VS2015, create a new MVC project with no authentication. I suggest using the same project name I did (ContosoMySQLAzure), so that cutting and pasting my code has a better chance of working. Replace the code in the Home Index.cshtm View with this:

@{
    ViewBag.Title = "Home Page";
}

<div class="jumbotron">
    <h1>Contoso University</h1>
</div>
<div class="row">
    <div class="col-md-4">
        <h2>Welcome to Contoso University</h2>
        <p>
            Contoso University is a sample application that
            demonstrates how to use Entity Framework 6 in an
            ASP.NET MVC 5 web application. This one uses MySQL.
        </p>
    </div>

</div>

From the Tools menu, select NuGetPackage Manager and then Package Manager Console. Insert the following command:

>Install-Package MySql.Data.Entity

This loads the Entity Framework code that works with MySQL.

Now create your data model for students by adding a new Student.cs class file in the Model folder:

using System;
using System.Collections.Generic;

namespace ContosoMySQLAzure.Models
{
    public class Student
    {
        public int ID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public DateTime EnrollmentDate { get; set; }
    }
}

Now create the DB context (class which handles and keeps track of the various Entity Framework operations) by adding a new folder to the project, called DAL, and then create a new SchoolContext.cs class file in that folder.

using ContosoMySQLAzure.Models;
using System.Data.Entity;

namespace ContosoMySQLAzure.DAL
{
    public class SchoolContext : DbContext
    {
        public SchoolContext() : base("SchoolContext")
        {
            Database.SetInitializer<SchoolContext>(new MyDbInitializer());
        }

        public DbSet<Student> Students { get; set; }
    }
}

Add another new class, MyDbInitializer.cs to the DAL folder, this is used to pre-load an empty DB with some test values.

using ContosoMySQLAzure.Models;
using System;
using System.Data.Entity;

namespace ContosoMySQLAzure.DAL
{
    // after you have the application running, you might want to use:
    // public class MyDbInitializer : CreateDatabaseIfNotExists<SchoolContext>

    // but while debugging, when you want a clean and seeded DB, use:
    public class MyDbInitializer : DropCreateDatabaseAlways<SchoolContext>
    {
        protected override void Seed(SchoolContext context)
        {
            // create 3 students to seed the database
            context.Students.Add(new Student 
            { ID = 1, FirstName = "Mark", LastName = "Richards", EnrollmentDate = DateTime.Now });
            context.Students.Add(new Student 
            { ID = 2, FirstName = "Paula", LastName = "Allen", EnrollmentDate = DateTime.Now });
            context.Students.Add(new Student 
            { ID = 3, FirstName = "Tom", LastName = "Hoover", EnrollmentDate = DateTime.Now });
            base.Seed(context);
        }
    }
}

Edit the Web.config file and add a connection string to the Azure based instance of MySQL. I used the Azure Portal to create my MySQL DB. You need to have previously created an Azure MySQL account and have created an empty database named ContosoDataBase. When you create that DB, get and save the connection string, and edit my code below with values appropriate for your account.

<connectionStrings>
    <add name="SchoolContext" providerName="MySql.Data.MySqlClient" 
connectionString="Server=us-cdbr-azure-west-c.cloudapp.net; Database=ContosoDataBase; 
User Id=xxxxxxxxxxxxxx; Password=xxxxxxxx;" />
</connectionStrings>

Careful: Many of the earlier MySQL EF articles on the web show changes to the <entityFramework> sections in the Web.config file. However, for these current versions of EF, everything else in Web.config worked with no changes required.

Now to enable migrations (if your model changes) and to allow the EF to create the datebase table to hold your student model, you need to do a few more steps. First, again from the Tools menu, select NuGet Package Manager and then Package Manager Console. Insert the following command:

>enable-migrations

This creates a new folder, Migrations and a new class file within, Configuration.

Entity Framework Code First uses Migration History to keep track of model changes and ensure consistency with the database, however the Migration History table, __migrationhistory, has a primary key that is too large for MySql. I got the fix for this from this tip.

Under the migration folder, add a new class named MySqlHistoryContext.cs and add the following code:

using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Migrations.History;

namespace ContosoMySQLAzure.Migrations
{
    public class MySqlHistoryContext : HistoryContext
    {
        public MySqlHistoryContext(DbConnection connection, string defaultSchema)
            : base(connection, defaultSchema)
        {

        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<HistoryRow>().Property(h => h.MigrationId).HasMaxLength(100).IsRequired();
            modelBuilder.Entity<HistoryRow>().Property(h => h.ContextKey).HasMaxLength(200).IsRequired();
        }
    }
}

And in this migration folder, change the Configuration.cs file to:

namespace ContosoMySQLAzure.Migrations
{
    using DAL;
    using System.Data.Entity;
    using System.Data.Entity.Migrations;

    internal sealed class Configuration : DbMigrationsConfiguration<SchoolContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;

            // register mysql code generator
            SetSqlGenerator("MySql.Data.MySqlClient", 
            	new MySql.Data.Entity.MySqlMigrationSqlGenerator());

            SetHistoryContextFactory("MySql.Data.MySqlClient", 
            (conn, schema) => new MySqlHistoryContext(conn, schema));
        }

        protected override void Seed(SchoolContext context)
        {

        }
    }
}

Next, create a custom database initializer since the MySQL provider does not support Entity Framework migrations. Add a new class file named MySqlInitializer.cs to the project (not to be confused with the file MyDbInitializer.cs down in the DAL folder), and use this code:

using ContosoMySQLAzure.DAL;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;

namespace ContosoMySQLAzure
{
    public class MySqlInitializer : IDatabaseInitializer<SchoolContext>
    {
        public void InitializeDatabase(SchoolContext context)
        {
            if (!context.Database.Exists())
            {
                // if database did not exist before - create it
                context.Database.Create();
            }
            else
            {
                // query to check if MigrationHistory table is present in the database
                var migrationHistoryTableExists = 
                ((IObjectContextAdapter)context).ObjectContext.ExecuteStoreQuery<int>(
                string.Format(
                  "SELECT COUNT(*) FROM information_schema.tables 
                  WHERE table_schema = '{0}' AND table_name = '__MigrationHistory'",
                  "ContosoDataBase"));  // this last parameter is the name of your DB

                // if MigrationHistory table is not there 
                // (which is the case first time we run) - create it
                if (migrationHistoryTableExists.FirstOrDefault() == 0)
                {
                    context.Database.Delete();
                    context.Database.Create();
                }
            }
        }
    }
}

For the this new MySqlInitializer database initializer to work, you need to change the application startup code to register the initializer. Edit Global.asax.cs and add one line in this method:

protected void Application_Start()
{
    Database.SetInitializer(new MySqlInitializer());  // <<<<<<   add this line

    AreaRegistration.RegisterAllAreas();
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);  // ...

Now, create an MVC controller to use this data. Right click on the Controller folder and do an Add New Scaffold Item, select and MVC 5 Controller with Views, using Entity Framework. The Model class is Student, the Data Context class is SchoolContext, and the name is StudentController.

Lastly, edit the _Layout.cshtml file to add a link to the new controller:

<ul class="nav navbar-nav">
    <li>@Html.ActionLink("Home", "Index", "Home")</li>
    <li>@Html.ActionLink("About", "About", "Home")</li>
    <li>@Html.ActionLink("Contact", "Contact", "Home")</li>
    <li>@Html.ActionLink("Students", "Index", "Student")</li>
</ul>

It should work now, either locally to the Azure MySQL, or you can push the app to Azure and run it all in the Cloud.

History

October 1, 2015: Added the required change to the Global.asax.cs file and changed the text around here for the MyDbInitializer.cs.

// probably want this after app is debugged and "live"
//public class MyDbInitializer : CreateDatabaseIfNotExists<SchoolContext>

// this is appropriate for debugging when you want a fresh copy and seeded
public class MyDbInitializer : DropCreateDatabaseAlways<SchoolContext>