EntityFramework CodeFirst 4.3, Adding Data Migration To Simple Example (Part 2)

 

Introduction

This three part series demonstrates a very simple example of using Entity Framework Code First (Version 4.3) to create a SqlServer Table and populate it with data (that is part 1).  Part 2 adds two new columns to the table a populates the data conditionally (while migrating from a non migration enabled project), and Part 3 adds a new column with a default value to a migration enabled code first project.

As it happens, I watched President Obama drive by me in San Francisco yesterday so he will be the star of the post.  The table we will use is called Presidents, and the columns we will add are Year Elected and Current.  We will obviously only have one current president so we will have to have our migration conditionally set CurrentPresident to true for Obama.

 

 

 
 

Our Goal

 
We simply want to update the table “Presidents” which currently has two columns, Id and LastName.  We want to add the columns “YearElected” and “Current”.  If the president (LastName) is Obama, we want to set CurrentPresident to true, if not false.
 
 
image
 

Here We Go

 
First thing, let’s make sure that our SiteDBInitialize (see part 1) is set to implement CreateDatabaseIfNotExists.  That code should look like the following:
 
public class SiteDBInitialize :
        CreateDatabaseIfNotExists<SiteDB>
    {
 
Next, let’s add the columns to both our Seed method as well as the Presidents class.  For clarity, I’m pasting the complete program below (remember, this is it!)
 
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Data.Entity;

namespace ConApp
{
    internal class Program
    {
        private static void Main()
        {
            Database.SetInitializer<SiteDB>(new SiteDBInitialize());
            using (var myContext = new SiteDB())
            {
                var x = myContext.Presidents.ToList();
            }
        }
    }

    public class SiteDB : DbContext
    {
        public DbSet<Presidents> Presidents { get; set; }
    }

    public class Presidents
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }
        public string LastName { get; set; }

        // New Columns:
        public int YearElected { get; set; }
        public bool CurrentPresident { get; set; }
    }

    public class SiteDBInitialize :
        CreateDatabaseIfNotExists<SiteDB>
    {
        protected override void Seed(SiteDB context)
        {
            context.Presidents.Add(new Presidents
                                       {
                                           LastName = "Reagan",
                                           CurrentPresident = false,
                                           YearElected = 1984
                                       });
            context.Presidents.Add(new Presidents
                                       {
                                           LastName = "Bush",
                                           CurrentPresident = false,
                                           YearElected = 1992
                                       });
            context.Presidents.Add(new Presidents
                                       {
                                           LastName = "Obama",
                                           CurrentPresident = true,
                                           YearElected = 2008
                                       });
            context.SaveChanges();
        }
    }

}

In our case, the initializer will only be called if the database does not exist.  Let’s change the data in the database just so we know that our data has been migrated and not simply replaced.  Here is a screen shot of what I’ve changed it to (notice I’ve added an “x” to the end of each LastName).

 

image

 

Now, if I run this, I get an error that says I need to run migrations.  The error is as follows:

Unhandled Exception: System.InvalidOperationException: The model backing the ‘Si

teDB’ context has changed since the database was created. Consider using Code Fi

rst Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=23

8269).

   at System.Data.Entity.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext

context)

This is great!  It is telling me my model has changed and telling me where I need to go look to learn what to do.  So, after looking there, I discover (with a little digging) that I need to –EnableAutomatic.

 

image

 

After doing this, I’m surprised to get this error (OK, I admit I’m learning this new feature, it just came out this week) as I’m writing this blog post.

PM> Enable-Migrations

Detected database created with a database initializer. Scaffolded migration ’201202171606443_InitialCreate’ corresponding to current database schema. To use an automatic migration instead, delete the Migrations folder and re-run Enable-Migrations specifying the -EnableAutomaticMigrations parameter.

Code First Migrations enabled for project ConApp.

What I see happened is that Enable-Migrations create a new folder structure as follows:

 

image

 

Basically, what has happened is “Enable-Migrations” did the following:

  1. Create a file Migrations/Configuration (determines how migrations behave for the current context, one in this case, DBSite).
  2. 201..443_InitialCreate.cs was created because our initial database was created before migrations were enabled.  It represents just the table before the latest columns were added.

Now, we run the package manager commanda: 

Add-Migration AddingNewColumnsElectedYearAndCurrentPresident

And, from that we get the message:

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 201202171748276_AddingNewColumnsElectedYearAndCurrentPresident’ again.

Now, we have some new files in our Migrations folder as follows:

 

image

This file: 201202171748276_AddingNewColumnsElectedYearAndCurrentPresident.cs has code in it that both upgrades and downgrades our database.  Very nice Microsoft!

public partial class AddingNewColumnsElectedYearAndCurrentPresident : DbMigration
    {
        public override void Up()
        {
            AddColumn("Presidents", "YearElected", c => c.Int(nullable: false));
            AddColumn("Presidents", "CurrentPresident", c => c.Boolean(nullable: false));
        }
        
        public override void Down()
        {
            DropColumn("Presidents", "CurrentPresident");
            DropColumn("Presidents", "YearElected");
        }
    }

Then, to the Up method, let’s add our custom datacolumn settings:

public override void Up()
{
    AddColumn("Presidents", "YearElected", c => c.Int(nullable: false));
    AddColumn("Presidents", "CurrentPresident", c => c.Boolean(nullable: false));

    Sql("UPDATE Presidents SET CurrentPresident = 0,YearElected = 1980 WHERE LastName='ReaganX'");
    Sql("UPDATE Presidents SET CurrentPresident = 1,YearElected = 1992 WHERE LastName='BushX'");
    Sql("UPDATE Presidents SET CurrentPresident = 1,YearElected = 2008 WHERE LastName='ObamaX'");
}

Not forgetting, we need to enable automatic migrations as follows in the Configuration.cs file

internal sealed class Configuration : DbMigrationsConfiguration<ConApp.SiteDB>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }

 

Now, Build your app (probably necessary) and run the update database package command:

PM> Update-Database -Verbose

And you’ll get this nice output showing you the sql generated:

Using NuGet project ‘ConApp’.

Using StartUp project ‘ConApp’.

Target database is: ‘agelessemail’ (DataSource: ., Provider: System.Data.SqlClient, Origin: Configuration).

Applying explicit migrations: [201202171748276_AddingNewColumnsElectedYearAndCurrentPresident].

Applying explicit migration: 201202171748276_AddingNewColumnsElectedYearAndCurrentPresident.

ALTER TABLE [Presidents] ADD [YearElected] [int] NOT NULL DEFAULT 0

ALTER TABLE [Presidents] ADD [CurrentPresident] [bit] NOT NULL DEFAULT 0

UPDATE Presidents SET CurrentPresident = 0,YearElected = 1980 WHERE LastName=’ReaganX’

UPDATE Presidents SET CurrentPresident = 1,YearElected = 1992 WHERE LastName=’Bush’

UPDATE Presidents SET CurrentPresident = 1,YearElected = 2008 WHERE LastName=’ObamaX’

[Inserting migration history record]

And when you look at the data, you will of course see:

 

image

 

Conclusions

OK, this was a little more involved than I thought it would be.  Primarily because we were going from a project that did not have code migrations enabled to one that does (I am glad I went through the exercise though).  In Part 3 (which I was not planning on doing) I’ll look at what it takes to add YAC (yet another column) to the Presidents Table now that we have Automatic Code Migrations enabled.

Hope this helps!  Move on to part 3 now.

About Peter Kellner

Peter is a software professional specializing in mobile and web technologies. He has also been a Microsoft MVP for the past 7 years. To read more about Peter Kellner and his experience click here. For information about how Peter Kellner might be able to help you with your project click here.

Follow me:


Comments

  1. Hi Peter,

    I’m struggling with the whole migration/database story.

    I’ve got a databasecontext which is nice. I’m running my migrations by running Database.SetInitializer(new MigrateDatabaseToLatestVersionStrategy()) in my Application_Start();. But I’m not allowed to run the Initializer: DropCreateDatabaseAlways on my context. I’m getting this error as an exception when doing so: “The DropCreateDatabaseAlways initializer did not drop or create the database backing context ‘MydbContext’ because Migrations are enabled for the context. Use Migrations to manage the database for this context, for example by running the ‘Update-Database’ command from the Package Manager Console.”

    Yes, I have migrations enabled for my database, BUT I’ve also got a Continuous Delivery environment with DEV, TEST and RELEASE settings and sites + databases. So for DEV I just want to run my migrations and don’t look anymore, but for TEST I want to ‘DropCreateDatabaseAlways’ and run the Seed() method which holds all default data to make the Selenium integration tests run.

    Do you have any opinion on this?
    I’m using Visual Studio Online as source for the Windows Azure environment which, whenever I fire a build, the proper config gets selected and the whole process should start.

    Thanks in advance.

Your Comments

*

Protected with IP Blacklist CloudIP Blacklist Cloud

Follow

Get every new post delivered to your Inbox

Join other followers: