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

Updated: at 11:18 PM

 

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.

 

 

image_thumb4
I need practice with my cell phone camera (obviously) 

Part 1 Building a 1 File Console App That Creates a SqlServer Table and Populates it With Data
Part 2 Adding Two New Columns To the Customer Table and Populating Data Conditionally using New Migrations Feature (With Upgrade To Code Migrations from Project Without Code Migrations)
Part 3 Changing your CodeFirst DataModel with Migrations Enabled
 
For those wanting the real code, the final visual studio solution after part 3, includes parts 1 and 2 information is here: 
 
 

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();
}
}
}


<span class="kwrd">public</span> <span class="kwrd">class</span> SiteDB : DbContext
{
    <span class="kwrd">public</span> DbSet&lt;Presidents&gt; Presidents { get; set; }
}

<span class="kwrd">public</span> <span class="kwrd">class</span> Presidents
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    <span class="kwrd">public</span> <span class="kwrd">long</span> Id { get; set; }
    <span class="kwrd">public</span> <span class="kwrd">string</span> LastName { get; set; }

    <span class="rem">// New Columns:</span>
    <span class="kwrd">public</span> <span class="kwrd">int</span> YearElected { get; set; }
    <span class="kwrd">public</span> <span class="kwrd">bool</span> CurrentPresident { get; set; }
}

<span class="kwrd">public</span> <span class="kwrd">class</span> SiteDBInitialize :
    CreateDatabaseIfNotExists&lt;SiteDB&gt;
{
    <span class="kwrd">protected</span> <span class="kwrd">override</span> <span class="kwrd">void</span> Seed(SiteDB context)
    {
        context.Presidents.Add(<span class="kwrd">new</span> Presidents
                                   {
                                       LastName = <span class="str">&quot;Reagan&quot;</span>,
                                       CurrentPresident = <span class="kwrd">false</span>,
                                       YearElected = 1984
                                   });
        context.Presidents.Add(<span class="kwrd">new</span> Presidents
                                   {
                                       LastName = <span class="str">&quot;Bush&quot;</span>,
                                       CurrentPresident = <span class="kwrd">false</span>,
                                       YearElected = 1992
                                   });
        context.Presidents.Add(<span class="kwrd">new</span> Presidents
                                   {
                                       LastName = <span class="str">&quot;Obama&quot;</span>,
                                       CurrentPresident = <span class="kwrd">true</span>,
                                       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));
        }

    <span class="kwrd">public</span> <span class="kwrd">override</span> <span class="kwrd">void</span> Down()
    {
        DropColumn(<span class="str">&quot;Presidents&quot;</span>, <span class="str">&quot;CurrentPresident&quot;</span>);
        DropColumn(<span class="str">&quot;Presidents&quot;</span>, <span class="str">&quot;YearElected&quot;</span>);
    }
}</pre>



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(<span class="str">&quot;UPDATE Presidents SET CurrentPresident = 0,YearElected = 1980 WHERE LastName='ReaganX'&quot;</span>);
Sql(<span class="str">&quot;UPDATE Presidents SET CurrentPresident = 1,YearElected = 1992 WHERE LastName='BushX'&quot;</span>);
Sql(<span class="str">&quot;UPDATE Presidents SET CurrentPresident = 1,YearElected = 2008 WHERE LastName='ObamaX'&quot;</span>);


}


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.