Before Entity Framework Code First Migration was introduced in version 4.3.  Developers had a few options if the domain model was different from the database schema.

• CreateDatabaseIfNotExists : Pretty self explanatory, if on application start Entity Framework cannot find the database create it.
• DropCreateDatabaseIfModelChanges : Every DBA I know fears this accidentally be set for production.  This will cause Entity Framework to drop the database and recreate if the model has changed.  This option alone has killed Entity Framework Code First from being a development option in many shops. Before I get hate mail yes I know that DBA’s can set permissions to deny this as an option in productions, I think it is the fear alone that it can be a possibility.
• DropCreateDatabaseAlways : Same as above, however the difference being that as the name implies that database is always recreated on startup.
• Set the initializer to null : Really kills most of the point of using Code First.

These options are far from ideal for production even for development they have little appeal when a database has a large set of live/sample data in it, and even less since the database for this demo is pre-existing.

Then along comes Code First Migration.  Here are some perks:

• Modify the Database without destroying it.
• Throws an exception if data loss would happen.  Data loss can be forced but a developer has to explicitly set it.

What are the Code First Migrations options:

• Automatic Migration : Entity Framework will  migrate(update) the database on startup.  Downside, no option to roll back if something goes wrong with a database change.
• Code-Based Migration : Grants flexibility in creating a check point style migration file.  It will contain a up and down method.  These methods will contain a listing of what is different from the previous check point (or database if previous check points do not exist).  The up method will upgrade, and the down method will allow for roll back.  These can be conducted either by NuGet PowerShell commands or from inside code.
• Script Migration : A PowerShell command is offered that will generate a SQL script for the code-based migration that can be handed to the DBA to review and run on production systems from SSMS.

Go to the ChlodnyWebApi application and enable the migration feature.

• Open up the package manager console (Tools –> Library Package Manager)
• Change the Default Project to DataAccess (because this holds our context file)
• Run Enable-Migrations (s is important)

When finished, a new folder labeled Migrations will be created and in it is a Configuration file.  In the constructor by default Automatic Migration is set to false.  If you want to utilize this feature set it to true.  For this demo we will leave it off..

With automatic migration set to false, the scaffolding commands will need to be run manually from the Package Manager Console.  The commands are:

• Add-Migration – Creates that check point style file with the Up\Down methods.
• Update-Database – Updates the database to the migration file targeted.  Depending on which file is targeted, it will execute the up method or the down method. To roll back target a past migration file.

Run Add-Migration Initial, if the name is left off it will prompt for a name.  When the file is opened it will contain create statements for the tables and views.

Note: if you get the following error

Unable to generate an explicit migration because the following explicit migrations are pending:

It is because the _MigrationHistory table has not been created. (I will discuss in detail below)  Open up the initial.cs file and comment out the code in the Up and Down Method and then run Update-Database.  It will create the table and give you an unable to update database message.  Just ignore that.

Open and modify the DataAccess.Entities.Customer.cs file and add a new Boolean property and name it Deleted.  It should look similar to this:

public bool Deleted { get; set; }

Run Add-Migration AddDeleted in package manager console.   A new file will be created that ends in AddDelete.cs file.  In side it there will be something in the Up/Down methods that either adds or removes the new column.

public override void Up()
{
AddColumn(“Customer”, “Deleted”, c => c.Boolean(nullable: false));
}

To have the changes take effect, Run Update-Database – Verbose

Even though the DataAccess project is the default, Entity Framework is smart enough to know that the database connection info is located in the ChlodnyWebApi Web.config.  With the verbose flag the output will display the exact SQL being sent to the database in the package manager console.

One of the new changes to Entity Framework 4.3.x is the removal of the EDMX table (three cheers for the EF team), now under the system tables folder there is a MigrationHistory table.  Any time the database is changed through the EF migrations a new row is added.

Open the customer table in the Chinook database the new Deleted row should appear.

To finish with the wire up and Entity Framework side of the application complete the following:

• Open Global.asax
• Add using DataAccess.Migrations;
• Modify the SetInitializer to use MigrateDatabase.  With a preprocessor MigrateDatabaseToLatestVersion will only happen in Debug mode.  Otherwise it will be disabled.

#if DEBUG // Only allow migration for debug mode

Database.SetInitializer(new MigrateDatabaseToLatestVersion<ChinookContext, DataAccess.Migrations.Configuration>()); #else // turn off migration migration for production

Database.SetInitializer<ChinookContext>(null); #endif

• Since the configuration file is located in another project.  The access modifier will need to be changed from internal to public in DataAccess.Migrations.Configuration.cs.