Code-based migrations is a new feature as part of the Connector/Net support for Entity
Framework 4.3.1. In this tutorial we'll see how we can use it so we can keep track of the changes done to our database creating a new application using the code first approach. If you don't have a clear idea about how code first works we highly recommend you to check this subject before going further with this tutorial.
Creating our Model and Database with Code First
From VS 2010
1. Create a new console application
2. Add the latest Entity
Framework official package using Package Manager Console (Tools Menu, then Library Package Manager -> Package Manager Console).
In the Package Manager Console we have to type
Install-Package EntityFramework
This will add the latest version of this library. We will also need to make some changes to your config file. A <configSections> was added which contains the version you have from EntityFramework. An <entityFramework> section was also added where you can set up some initialization. This section is optional and by default is generated to use SQL Express. Since we don't need it for now (we'll see more about it below) let's leave this section empty as shown below.
3. Create a new Model with a simple entity.
4. Enable Migrations to generate the our Configuration class.
In the Package Manager Console we have to type
Enable-Migrations;
This will make some changes in our application. It will create a new folder called Migrations where all the migrations representing the changes we do to our model. It will also create a Configuration class that we'll be using to initialize our SQL Generator and some other values like if we want to enable Automatic Migrations.
You can see that it already has the name of our DbContext. You can also create you Configuration class manually.
5. Specify our Model Provider.
We need to specify in our Class Configuration that we'll be using MySQLClient since this is not part of the generated code. Also please make sure you have added the MySql.Data and the MySql.Data.Entity references to your project.
using MySql.Data.Entity; // Add the MySQL.Data.Entity namespace
public Configuration()
{
this.AutomaticMigrationsEnabled = false;
SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator()); // This will add our MySQLClient as SQL Generator
}
6. Add our Data Provider and set up our connection string
<connectionStrings>
<add name="PersonalContext" connectionString="server=localhost;User Id=root;database=Personal;" providerName="MySql.Data.MySqlClient" />
</connectionStrings>
<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.6.2.
0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
* The version recommended to use of Connector/Net is 6.6.2 or earlier.
At this point we can create our database and then start working with Migrations. So let's do some data access so our database get's created.
You can run your application and you'll get your database Personal as specified in our config file.
Add our first migration
Migrations are a great resource as we can have a record for all the changes done and will generate the MySQL statements required to apply these changes to the database.
Let's add a new property to our Person class
public string Email { get; set; }
If you try to run your application it will throw an exception saying
The model backing the 'PersonelContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).
So as suggested let's add our first migration for this change.
In the Package Manager Console let's type
Add-Migration AddEmailColumn
Now we have the corresponding class which generate the necessary operations to update our database.
namespace MigrationsFromScratch.Migrations
{
using System.Data.Entity.Migrations;
public partial class AddEmailColumn : DbMigration {
public override void Up(){
AddColumn("People", "Email", c => c.String(unicode: false));
}
public override void Down()
{
DropColumn("People", "Email");
}
}
}
In the Package Manager Console let's type
Update-Database
Now you can check your database to see all changes were succesfully applied.
Now let's add a second change and generate our second migration
public class Person
{
[Key]
public int PersonId { get; set;}
public string Name { get; set; }
public string Address {get; set;}
public string Email { get; set; }
public List<Skill> Skills { get; set; }
}
public class Skill
{
[Key]
public int SkillId { get; set; }
public string Description { get; set; }
}
public class PersonelContext : DbContext
{
public DbSet<Person> Persons { get; set; }
public DbSet<Skill> Skills { get; set; }
}
If you would like to customize any part of this code you can do that at this step. You can see there is the up method which can update your database and the down that can revert the changes done. If you customize any code you should make sure to customize in both methods.
Now let's apply this change.
Update-database -verbose
I added the verbose flag so you can see all the SQL generated statements to be run.
Downgrading changes
So far we have always upgraded to the latest migration, but there may be times when you want downgrade to a specific migration. Let's say we want to return to the status we have before our last migration. We can use the -TargetMigration option to specify the migration we'd like to return. Also you can use the -verbose flag.
If you like to go back to the Initial state you can do:
Update-Database -TargetMigration:$InitialDatabase
or equivalent:
Update-Database -TargetMigration:0
Migrations doesn't allow by default a migration that would ocurr in a data loss. One case when you can got this message is for example in a DropColumn operation.
You can override this configuration by setting AutomaticMigrationDataLossAllowed to true in the configuration class.
Also you can set your Database Initializer in case you want that these Migrations can be applied automatically and you don't have to go all the way through creating a migration and updating later the changes.
Let's see how.
Database Initialization by Code
We can specify an initialization strategy by using Database.SetInitializer (http://msdn.microsoft.com/en-us/library/gg679461(v=vs.103)). One of the strategies that I found very useful when you are at a development stage (I mean not for production) is the MigrateDatabaseToLatestVersion. This strategy will make all the necessary migrations each time there is a change in our model that needs a database replication, this also implies that we have to enable AutomaticMigrationsEnabled flag in our Configuration class.
public Configuration()
{
AutomaticMigrationsEnabled = true;
AutomaticMigrationDataLossAllowed = true;
SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator()); // This will add our MySQLClient as SQL Generator
}
In the new EntityFramework section of your Config file we can set this at a context level basis.
The syntax is as follows:
<contexts>
<context type="Custom DbContext name, Assembly name">
<databaseInitializer
type="System.Data.Entity.MigrateDatabaseToLatestVersion`2[[
Custom DbContext name, Assembly name],
[Configuration class name, Assembly name]],
EntityFramework" />
</context>
</contexts>
In our example this would be:
The syntax is kind of odd but very convenient. This way all changes will always be applied when we do any data access in our application. There are a lot of new things to explore in EF
4.3.1 and Migrations so we'll continue writing some more posts about it.
Please let us know if you have any questions or comments, also please check our forums here where we keep answering questions in general for the community.
Hope you found this information useful.
Happy MySQL/.Net Coding!
