MySQL .NET Entity Framework Code-First Migration

I was recently downloading and trying to run a MySQL docker image  which worked just fine on top of a Hyper-V based Ubuntu 14.04 LTS. Don’t worry, this is still Entity Framework Code-First migration article with Visual Studio 2015 and MySQL 5.6.27 (latest as of today).

I setup a Docker MySQL Image on my Ubuntu based Docker host. I was thinking what could be a best way to test this instance out with .NET than testing it with our good friend Entity Framework Code-First migration. This is really going to hit the MySQL instance hard by creating DB on its own and creating all the Model Tables on its own with little or no involvement of MYSQL DDL /DMLs.

Prerequisites : –

To start using Entity Framework 6 and Visual Studio 2015 is necessary to install

• MySQL Connector/Net 6.9.7 (GA) (It may work with earlier releases, but this is the one I used for my POC)
• MySQL for Visual Studio 1.2.4 GA (Starting with version 6.7, Connector/Net will no longer include the MySQL for Visual Studio integration. That functionality is now available in a separate product called MySQL for Visual Studio available using the MySQL Installer for Windows)

Action Begins

1. Create a new Console Application. Select Framework as 4.5 or 4.0. This is because MySQL.Data.Entity Nuget package is supported only on these frameworks, though EntityFramework 6 is supported on .net 2.0, .net 4.0 and .net 4.5.
2. Open Nuget Package Manager Console by navigating through Toos –> Nuget Package Manager –> Package Manager Console…  and run the below commands to install the Nuget packages needed for this project. Make sure that you select correct Project in Package Manager console if you are having multiple Projects in same Solution.
   1. Install-package EntityFramework -Version 6.0.0
   2. Install-package MySql.Data # as of today it installs 6.9.7
   3. Install-package MySql.Data.Entity # as of today it installs 6.9.7 version
   4. Install-package MySql.Data.Entities # as of today it installs 6.8.3 version (required for MYSQLGenerator in Step #9 below)
3. Add MySQL ConnectionString to App.config / Web.config. Ensure the providerName is exactly same as provider defined in entityFramework-> providers section

<connectionStrings>

<add name=”mysqlconn” connectionString=”server=192.168.0.15;uid=root;pwd=root;database=testdb” providerName=”MySql.Data.MySqlClient“/>

</connectionStrings>

4. You can remove any defaultConnectionFactory configs from entityFramework section.
5. Add a DBContext class

class myDBContext : DbContext {

public myDBContext() : base(“mysqlconn”)

{

}

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

base.OnModelCreating(modelBuilder);

}

public DbSet<Person> People { get; set; }

}

6. Add a Person class in Model folder

class Person

{

public int Id { get; set; }

public string Name { get; set; }

public int Age { get; set; }

}

7. Compile the Project. this is most important as all further steps depend on ‘Reflection’ libs of the Project.
8. Enable the Migrations from Nuget Package Manager console

PM> enable-migrations

Checking if the context targets an existing database…

Code First Migrations enabled for project mysqltester.

PM>

9. Set SQL Generator in the Migration Configurations class.In the Migrations/Configuration.cs – Constructor. Add below line to set SQLGenerator attribute.

SetSqlGenerator(“MySql.Data.MySqlClient”, new MySql.Data.Entity.MySqlMigrationSqlGenerator());

10. Create initial migration, which means the SQL Generation works for the Code-First entity Migration defined above.

PM> add-migration initial

Scaffolding migration ‘initial’.

The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running ‘Add-Migration initial’ again.

PM>

11. Check if update-database works , which means the EF was able to use the DBContext defined and connection from app / web.config.

PM> update-database

Specify the ‘-Verbose’ flag to view the SQL statements being applied to the target database.

Applying explicit migrations: [201510041122429_initial].

Applying explicit migration: 201510041122429_initial.

Running Seed method.

PM>

12. Write a Sample Program to test/check the DBContext and data..

class Program

{

static void Main(string[] args)

{

myDBContext dbc = new myDBContext();

foreach (var item in dbc.People)

{

Console.WriteLine(“Name {0}, Age {1}”, item.Name, item.Age);

}

string ans = “n”;

do

{

Person newperson = new Person();

string temp=””;

int age = 0;

do

{

Console.WriteLine(“Enter Age”);

temp = Console.ReadLine();

} while (! (int.TryParse(temp, out age) && age > 0));

temp = null;

newperson.Age = age;

Console.WriteLine(“Enter Name “);

newperson.Name = Console.ReadLine();

dbc.People.Add(newperson);

Console.Write(“More People ?”);

ans = Console.ReadLine();

} while (ans.Equals(“y”, StringComparison.CurrentCultureIgnoreCase));

dbc.SaveChanges();

}

}

All the source code for this article can be found at GitHub repository here.