Update an Existing Database using Code First Migrations with ASP.NET and Entity Framework

By Lee Naylor

There is going to come a point where you will want to update your existing database model from your code. The typical way to do this is to use migrations; however, using Code First with an existing database requires a little extra care to work correctly.

First of all, you must enable migrations for the project. In Package Manager Console, type the following command (we need to specify the context to use because we included authentication in the project and this uses its own context by default):

          Enable-Migrations  -ContextTypeName  CodeFirstExistingDB.StoreContext

This will create a Migrations folder and add a Configuration.cs file to it. Next we want to create our migrations to run. This is where you need to add an extra step for an existing database. If we create a migration now, it will attempt to add all our entities to the database. This will not work because the products and categories tables already exist in the database, so we need to create an initial blank migration and then later we will be able to add a migration for any new changes. To create an initial blank migration for the database, type the following command into Package Manager Console:

          Add-Migration  InitialCreate  -IgnoreChanges

The key part of this command is the -IgnoreChanges flag, which ensures that a migration is created that effectively does nothing. Running it will add an entry to the migrations table in the database, thus creating a snapshot of its original schema.

Next, run the update-database command in order to update the existing database with the initial migration. A new migrations table will now have been created in the CodeFirstFromExistingDB database.

Following this, add a new property named Description to the Product class, with a maximum allowed length of 50 characters, as follows:

          namespace  CodeFirstExistingDB
          {
            using System;
            using System.Collections.Generic;
            using System.ComponentModel.DataAnnotations;
            using System.ComponentModel.DataAnnotations.Schema; using System.Data.Entity.Spatial;

            public partial class Product
            {
              public int Id { get; set; }

              [Required]
              [StringLength(50)]
              public string Name { get; set; }

              [StringLength(50)]
              public string Description { get; set; }

              public int? CategoryID { get; set; }
              public virtual Category Category { get; set; }
            }
          }

Now add a new migration for the product Description field so you can add it as a new column to the Products table. You do this by typing the following command in the Package Manager Console:

          Add-Migration add_product_description

A new code file will be produced in the Migrations folder that will include code to add a description column to the Products table as follows:

          namespace  CodeFirstExistingDB.Migrations
          {
            using System;
            using System.Data.Entity.Migrations;

            public partial class add_product_description : DbMigration
            {
              public override void Up()
              {
                AddColumn("dbo.Products", "Description", c => c.String(maxLength: 50));
              }

              public override void Down()
              {
                DropColumn("dbo.Products",  "Description");
              }
            }
          }

Now run the update-database command in Package Manager Console to update the database. The new description column will be added to the Products table, as shown in the figure below:

The new Description column in the Products table of the CodeFirstFromExistingDB database

Now add some test data to the database. View the data of the Products table via SQL Server Object Explorer and enter some descriptions, as shown below:

Adding test data to the Description column of the Products table

Next, modify the Views\Products\Index.cshtml file to add a Description field, as highlighted in the following code:

          @model    IEnumerable<CodeFirstExistingDB.Product>

          @{
                  ViewBag.Title  =  "Index";
          }

          <h2>Index</h2>

          <p>
                  @Html.ActionLink("Create New", "Create")
          </p>
          <table class="table">
                  <tr>
                          <th>
                                  @Html.DisplayNameFor(model => model.Category.Name)
                          </th>
                          <th>
                                  @Html.DisplayNameFor(model => model.Name)
                          </th>
                          <th>
                                  @Html.DisplayNameFor(model => model.Description)
                          </th>
                          <th></th>

                  </tr>

          @foreach (var item in Model) {
                  <tr>
                          <td>
                                  @Html.DisplayFor(modelItem => item.Category.Name)
                          </td>
                          <td>
                                  @Html.DisplayFor(modelItem => item.Name)
                          </td>
                          <td>
                                  @Html.DisplayFor(modelItem => item.Description)
                          </td>
                          <td>
                                  @Html.ActionLink("Edit", "Edit", new { id=item.Id {) |
                                  @Html.ActionLink("Details", "Details", new { id=item.Id {) |
                                  @Html.ActionLink("Delete", "Delete", new { id=item.Id {) |

                          </td>
                  </tr>
          }

          </table>

Now, right-click on the view and choose View in Browser. You should now see the new Description field with the test data, as shown here:

The Product index page containing the new Description column and data

You can now work with your existing database as if you had created it using Code First and continue to update it using Code First as required.

About the Author

Lee Naylor has fifteen years of experience as a software engineer and has worked in Finance (Banking and Accounting), Retail, Automotive and Gaming/Gambling. His current areas of expertise are ASP.NET MVC, Entity Framework, front end development with CSS, JavaScript and jQuery. He is also experienced in project and team management, mentoring/teaching others, and understands all aspects of software projects from proposals and budgeting through analysis, estimations, planning, development, testing, user application testing, and release management.

Want more? This article is excerpted from ASP.NET MVC with Entity Framework and CSS. Get your copy today to get up and running quickly to build complex web sites with ASP.NET MVC and Entity Framework.