Entity Framework 5 Code First: Enabling Migrations in a Real Project

5 12 2012

In this article I will try to explain how to enable and use Entity Framework’s Code First Migrations in a real project (i.e.: a solution with many projects, a database server, etc).

The Target Entity Framework is version 5.

Open Visual Studio 2010 and create a new console application.

I named the example solution: EF5CodeFirst

and the project (guess how…): ConsoleApplication.

Add a class library project and name it, say: Model.

In my example I replaced the template generated: Class1.cs with a Person.cs file containing the following code:

public class Person
{
	public long PersonId { get; set; }
	public string Name { get; set; }
	public string SurName { get; set; }
}

Add another class library project, name it: Persistence and remove the file: Class1.cs.

Now I want to add Entity Framework to my project

It’s time to close Visual Studio and install the latest version of NuGet, if you haven’t got it yet.

You can find it here (http://nuget.codeplex.com).

Important Notice:

To successfully install the Entity Framework package you need Powershell 2.0 installed, so be sure to upgrade your machine if you work on Windows Xp for example.

Once installed NuGet, open your solution, right click the Persistence project and choose:

Manage NuGet Packages…

In the new window choose: Online in the left menu.

Write: Entity Framework, in the search field on the top right and hit Enter.

You will get a list of options in the center panel. The first one will be: Entity Framework.

Click the corresponding: Install button.

The package will be downloaded, you will be asked to accept the license agreement and eventually the Entity Framework will be installed in your solution folder tree and the Persistence project will get a default configuration for Entity Framework.

Let’s go a little bit in the details of what this configuration is:

If you expand your project in solution explorer you will find that a reference has been added to the EntityFramework assembly. The referenced assembly has been installed in a default location under the solution folder.

NuGet creates a folder named: packages at the same level as the solution file.

Inside that folder you will find a configuration file (used by Nuget) and the EntityFramework.5.0.0 folder.

From now on every new package you will install will have its own folder inside the packages path.

Inside its own folder every package can be organized with its own structure.

You will find the Entity Framework assembly under the path:

EntityFramework.5.0.0\lib\net40 (or net45).

Nuget added a reference to your project choosing the right assembly based on the target framework of your solution.

Going back to solution explorer and to our Persistence project you will find a packages.config file needed by NuGet to track the packages installed and to be able to uninstall them if required.

Strange to see you will find also an App.config file. Such file has been added by NuGet to configure the project to be used with Sql Express, if installed, or with LocalDB.

“If SQL Express is installed (included in Visual Studio 2010) then the database is created on your local SQL Express instance (.\SQLEXPRESS). If SQL Express is not installed then Code First will try and use LocalDb ((localdb)\v11.0) – LocalDb is included with Visual Studio 2012.”

In our example the App.config file should be moved to the: ConsoleApplication project.

Let’s have a look at its contents:

A configuration has been added to the file, specifying the “defaultConnectionFactory” to be used (e.g.: LocalDbConnectionFactory).

In a real environment you will probably have a development database server to be used during the development of your solution, maybe a staging server and eventually a production server.

If you want to configure your project to work with a SqlServer database you need to specify as defaultConnectionFactory the SqlConnectionFactory.

Important thing to know is that the SqlConnectionFactory is the default factory used, if you don’t specify anything in your App.config.

So we can definitely delete the App.config file added by NuGet.

Now you need to specify the connection string that will be used by your start-up project: ConsoleApplication, so you need to add an App.config in the console project and add the connection strings configuration section in it:

<connectionStrings>
	  <add name="TestConnectionString"
	  connectionString="Data Source=machineName;Initial Catalog=EF5Test;User Id=loginname;Password=yourpassword;MultipleActiveResultSets=true;"
	  providerName="System.Data.SqlClient" />
</connectionStrings>

Be sure to specify the credentials of a user with Database creation permissions.

Now add the code first context to the Persistence project:

public class PersonContext : DbContext
{
	public PersonContext() : base("TestConnectionString") {}

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

adding references and importing namespaces as required.

The constructor explicitely invoking the base one allows us to specify the connection string to be used, bypassing the convention that expects a connection string with the same name as our DbContext class (i.e.: PersonContext).

Now we need to create an instance of our context and enumerate a resultset to let the default DatabaseInitializer be invoked and therefore the database be created.

Let’s add the following code to the Program.cs file in the ConsoleApplication project, adding references to the required projects and to the Entity Framework assembly.

If you want to be able to manage Entity Framework entirely via NuGet you need to add the reference again using NuGet, otherwise just add a reference.

If you choose to use NuGet you will have to clean the ConsoleApplication‘s App.config leaving only the ConnectionStrings section we previously added.

Then run the application:

static void Main(string[] args)
{
	try
	{
		using (var context = new PersonContext())
		{
			context.Persons.Add(new Person { Surname = "Smith" });
			context.SaveChanges();

			foreach (var person in context.Persons)
			{
				Console.WriteLine(person.Surname);
			}
		}
	}
	catch (Exception exception)
	{
		Console.WriteLine(exception.ToString());
	}
}

In your database server you will find a new database named: EF5Test with a table named: People.

In the System Tables, the Entity Framework’s database initializer created a table named: __MigrationHistory that Entity Framework will use to check database version and apply schema migrations.

The table contains the first ‘migration’ record which represents the current version of the database.

If we modify the Person type adding a new property, say: Address:

public class Person
{
	public long PersonId { get; set; }

	public string Name { get; set; }

	public string Surname { get; set; }

	public string Address { get; set; }
}

and try to run the application again we will get the following error:

“System.InvalidOperationException: The model backing the ‘PersonContext’ context

has changed since the database was created. Consider using Code First Migrations

to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).”

This depends on the default Database Initializer used by EntityFramework: CreateDatabaseIfNotExists.

If you’re still developing your application you don’t need to create a new Migration for any change in the Model, you just need to configure the Database Initializer you wish to use.

In our case we want to recreate the database every time the model changes so we will add the following line of code just before the using statement in Program.cs in our ConsoleApplication project:

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

I suggest to always have a Fixtures project so that you can leave your application throw an exception if the Model changes with respect to the database schema while your Fixture project would contain a Fixture to regenerate the Database every time you modify the model.

I personally suggest Nunit but for our example we will go with the modification to the Console Application.

Run your application again (or run your test if you’ve got one) and you will see that the database has been regenerated with the new Address column in the People table.

Let’s now comment out for a while the Address property from the Person class and rerun the application to regenerate the database.

We can see the table: __MigrationHistory in the system tables, containing a record representing the first migration (the current database version).

We need to tell EntityFramework to classify such information as Migration number 1 and save this information somehow in the Visual Studio solution for later use.

1)
First of all we have to run the Enable-Migrations command in Package Manager Console. The exact syntax is:

Enable-Migrations -ProjectName "Persistence" -StartUpProjectName "ConsoleApplication"

This command will create in the Persistence project a Configuration class representing how Migrations will behave for my DbContext.

Being that we already generated the Database by running the application, the execution of the Enable-Migrations command also generates an initial migration, named: [timestamp]_InitialCreate.cs with the code below, scaffolded from the existing database.
If we didn’t create the database yet, this initial migration would have been included in the first generated Migration.

The command parameters have the following meaning:
-ProjectName: specifies the target project to create the migration in.
-StartupProjectName: specifies the target project to look for a configuration file containing a connection string.
If not specified the Visual Studio solution startup project is used.
The project must contain the actual configuration file and not a link to it.

 
public partial class InitialCreate : DbMigration
{
	public override void Up()
	{
		CreateTable(
			"dbo.People",
			c => new
				{
					PersonId = c.Long(nullable: false, identity: true),
					Name = c.String(),
					Surname = c.String(),
				})
			.PrimaryKey(t => t.PersonId);
		
	}
	
	public override void Down()
	{
		DropTable("dbo.People");
	}
}

2)
From now on, every time we want to add a new Migration we need to run the: Add-Migration command in the Package Manager Console:

Add-Migration YourMigrationName -ProjectName "Persistence" -StartUpProjectName "ConsoleApplication"

When invoking Add-Migration just specify a name for your Migration (e.g.: AddAddress) and the two parameters specifying the projects as in the Enable-Migrations command.
The code for the new Migration will be scaffolded, by comparing our model with the last Migration created, and the generated code will be added to the Persistence project.

Let’s now uncomment the Address property and suppose we want to release this new version of the application as a new Migration.
Run the following command:

Add-Migration AddAddress -ProjectName "Persistence" -StartUpProjectName "ConsoleApplication"

In the Migrations folder, in the Persistence project, a new migration will appear, named [timestamp]_AddAddress.cs and with the following content:

 
public partial class AddAddress : DbMigration
{
	public override void Up()
	{
		AddColumn("dbo.People", "Address", c => c.String());
	}
	
	public override void Down()
	{
		DropColumn("dbo.People", "Address");
	}
}

If you refresh your Database in Sql Server Management Studio you will see that the ‘People’ table still does not have an ‘Address’ column.

3)
Every time we want to apply a new Migration we need to run the: Update-Database command in the Package Manager Console:

Update-Database -ProjectName "Persistence" -StartUpProjectName "ConsoleApplication"

When invoking Update-Database just specify the two parameters specifying the projects as in the Enable-Migrations command.
All the pending Migrations, after the last applied, will be applied to the Database found in the connection string specified in the StartUpProject specified.
We can specify the name of the target Migration we want to update the Database by adding the following parameter:

–TargetMigration: AddAddress.

In our example after running the Update-Database command and refreshing the Database we will find a new ‘Address’ column in the ‘People’ table.

If we then want to revert the Migration we can do it with the following command:

Update-Database -ProjectName "Persistence" -StartUpProjectName "ConsoleApplication" –TargetMigration: InitialCreate

4)
If we don’t want to apply our Migration directly but release a script to our DBA, which is a very reasonable requirement for a production environment, we can modify the Update-Database to generate a script and not apply any changes to the Database.

To achieve this just add the following parameter:

-Script

that will cause the command to generate an sql file that will be opened in Visual Studio.
The generated file will contain the migration scripts starting from the last applied migration.

If we want to start from a specified Migration we can add the following parameter:

-SourceMigration:MigrationName.

If we want to start with an empty Database we can associate a special keyword as value to that parameter:

-SourceMigration:$InitialDatabase

Let’s generate a script to release in a new production environment all the migrations starting from an empty Database. We can do it with the following command:

Update-Database -ProjectName "Persistence" -StartUpProjectName "ConsoleApplication" -Script -SourceMigration:$InitialDatabase

obtaining:

CREATE TABLE [dbo].[People] (
    [PersonId] [bigint] NOT NULL IDENTITY,
    [Name] [nvarchar](max),
    [Surname] [nvarchar](max),
    CONSTRAINT [PK_dbo.People] PRIMARY KEY ([PersonId])
)
CREATE TABLE [dbo].[__MigrationHistory] (
    [MigrationId] [nvarchar](255) NOT NULL,
    [Model] [varbinary](max) NOT NULL,
    [ProductVersion] [nvarchar](32) NOT NULL,
    CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY ([MigrationId])
)
BEGIN TRY
    EXEC sp_MS_marksystemobject 'dbo.__MigrationHistory'
END TRY
BEGIN CATCH
END CATCH
INSERT INTO [__MigrationHistory] ([MigrationId], [Model], [ProductVersion]) VALUES ('201211152147401_InitialCreate', [hash], '5.0.0.net40')
ALTER TABLE [dbo].[People] ADD [Address] [nvarchar](max)
INSERT INTO [__MigrationHistory] ([MigrationId], [Model], [ProductVersion]) VALUES ('201212032151179_AddAddress', [hash], '5.0.0.net40')
About these ads

Actions

Information

6 responses

5 12 2012
The Morning Brew - Chris Alcock » The Morning Brew #1247

[...] Entity Framework 5 Code First: Enabling Migrations in a Real Project – Matteo Tontini takes a look at working with Entity Framework Code First Migrations in real world projects, walking through the setup of the project and discussing the various helper commands in the package manager console introduced by the Entity Framework Code First package [...]

23 02 2013
Przemysław Staniszewski

Hey Nice article, I found it when looking how to get

23 02 2013
Przemysław Staniszewski

Hey nice article, I found it when looking how to get database to initial state. I see there is the way to recreate it for every run. I have posted my problem here http://stackoverflow.com/questions/15025622/why-i-cant-get-entity-framework-migration-to-initial-state/15031817#15031817 . Maybe You know why after delete all tables in database and Migrations folder projects still remember two migrations?
Best Regards

25 04 2013
Entity Framework | Do VFP ao .NET

[...] Encontrei um artigo bem legal do uso do EF em um projeto “real”. O link é esse: http://ilmatte.wordpress.com/2012/12/05/entity-framework-5-code-first-enabling-migrations-in-a-real-… [...]

6 04 2014
ASP .NET MCV 5 Migrations Links | My great WordPress blog

[…] Entity Framework 5 Code First: Enabling Migrations in a Real Project […]

23 09 2014
EF Database Migrations–No Connection String | Spike Software

[…] I had the connection string defined in the app.config and built the library but no luck.  And I selected the default project in the package manager console.  Simple enough in the end after stumbling upon this post. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: