Code Focused

Entity Framework Code-First Migrations

Code First Migrations allow for database changes to be implemented all through code. Through the use of Package Manager Console (PMC), commands can be used to scaffold database changes.

• By Sam Nasr
• 04/10/2012

Code First Migrations allow for database changes to be implemented all through code. Through the use of Package Manager Console (PMC), commands can be used to scaffold database changes. In this article, I'll use three powerful PMC commands for generating code:

1. Enable-Migrations: creates a configuration class used for migrations
2. Update-Database: updates the current database with the migration plan
3. Add-Migration: creates a class representing the new migration plan

Automatic Migrations is one of the new features in Entity Framework 4.3, released on Feb. 9, 2012, with an update (EF 4.3.1) released on March 22, 2012. Since this article deals with Code First migration changes, it's assumed you already have a project with a Code First database structure. If not, please reference the previous article, which discusses this in more detail. That article uses separate projects for data access and UI as a best practice.

Install EF 4.3.1
The NuGet package for EF 4.3.1 is here. To install it, execute Install-Package EntityFramework -Version 4.3.1 in PMC. After installation, it will automatically update the reference of  "EntityFramework" to 4.3.1 in the "Default project" setting of the PMC. If you have more than one project in your solution referencing "EntityFramework", you should update those references as well. More information is available on the PMC.

Enable Migrations
The next step is to enable migrations by executing Enable-Migrations in the PMC for the DataAccess Project. This will generate a "Migration" folder with a "Configuration.vb", as seen in Figure 1. This file will seek out classes deriving from DbContext in the current project and scaffold a NotInheritable class called Configuration. To enable automatic migrations, AutomaticMigrationsEnabled must be set to True on line 12.

[Click on image for larger view.]

Figure 1. Configuration.vb

First Migration
At this stage, there is no corresponding database on the database server. The next step is to create the first migration. To do so, execute the command Update-Database in PMC. Since this our first migration, the database is created as specified in the DataAccess project. The results can be seen in Figure 2.

[Click on image for larger view.]

Figure 2. Initial Database Deployment

Second Migration
After the initial migration, most databases will get populated with data. To simulate this, I'll use the code written previously in the application to populate the database with records. This is the code in the btnCreateDatabase_Click() event handler of frmMain.vb, in the CodeFirstApp project.

Also, as in most real-world cases, the database will require some changes after the initial deployment. Therefore, the DataBaseStruct.vb in the DataAccess project was modified to have two new fields in the "VehicleTable" table and one new field in the "VehicleType" table. The modified code is shown in Listing 1.

After making the changes to the project, the Update-Database command was executed again in the PMC. This generated an automatic migration (201204010138577_AutomaticMigration) that was executed on the database. A '–Verbose' flag could've been used to view the SQL statements applied on the database. The results are displayed in Figure 3.

[Click on image for larger view.]

Figure 3. The Package Manger Console

This modified the database with the addition of the new fields without causing a problem with existing data. The new fields were set to 0 and Null, accordingly.  The implemented database changes can be seen in Figure 4.

[Click on image for larger view.]

Figure 4. The modified VehicleTable

In addition, query results, before and after the database changes were migrated, can be seen in Figure 5 and Figure 6, respectively.

[Click on image for larger view.]

Figure 5. Pre-migration database records

[Click on image for larger view.]

Figure 6. Post-migration database records

Code-Based Migrations
Suppose a constraint of  DefaultValue="TBD" was to be set on the VehilceTable.VIN Field. This could be implemented by using code-based migrations. To implement code-based migrations, the command Add-Migration must be executed in PWC. The Add-Migration command also requires a command-line parameter specifying the name of the class for the new migration plan.

This will create a class that allows for custom migrations. This command will generate a new file in the Migrations folder called "201204010309589_CustomCodeMigration.vb", where the numeric portion of the file name represents the date/time stamp when file was created. This file can be seen in Figure 7. This will help with the ordering of the database operations performed at a later time. The class generated has two default methods: Up() and Down(), used for database upgrades and downgrades, respectively.

[Click on image for larger view.]

Figure 7. 201204010309589_CustomCodeMigration.vb

Next, I added code to the Up() method for adding a constraint to column VIN. Note the table mentioned on line 8 in the figure below represents the name of the table in the database, not the name of the class object specified in DataBaseStruct.vb. The difference in the names is due to the <Table("VehicleTable")> attribute used on the Vehicle class.

In addition, I added a statement in Down() to revert back to a null default value in the case when downgrading the database:

Imports System.Data.Entity.Migrations

Namespace Migrations
    Public Partial Class CustomCodeMigration
        Inherits DbMigration
    
        Public Overrides Sub Up()
            AlterColumn("VehicleTable", "VIN", Function(v) v.String(defaultValue:="TBD"))
        End Sub
        
        Public Overrides Sub Down()
            AlterColumn("VehicleTable", "VIN", Function(v) v.String(defaultValue:=vbNull))
        End Sub
    End Class
End Namespace

To implement the newly-added constraint, I executed Update-Database command in PMC. This command then compared the current database with the code specified in the CustomCodeMigration class, generated the necessary SQL statements to update the database, and immediately updated the database. Looking at SQL Server Management Studio in Figure 8, the constraint was automatically added to the database.

[Click on image for larger view.]

Figure 8. Configuration.vb

 

I could have used code add, drop or alter columns and tables as needed in a similar manner.

Code First Migration Advantages
With the release Entity Framework 4.3.1, developers now have better control of database migrations. With classes generated, developers only need to make minor adjustments to the code. Using PMC, commands can be used to enable migrations, create migration plans and implements those migrations from within Visual Studio.

• Get Code Download