In working on my upcoming EF 4.3 Migrations video for Pluralsight, I wanted to work out how to use this with an existing database where I plan to add new types and therefore want migrations to not just use this database, but migrate it as well. Problem solved*, but then I tried to use it in a production application and found an easy-to-fix problem. So…I thought I’d share the process in a blog post while it’s on my mind.

The Purpose of the Migration-History Table

When you let EF 4.3 code first create a database for you, it inserts a Migration-History table into the new database. It’s hidden in System Tables. This is equivalent (in a way) to the EdmMetadata table you would have gotten with EF 4.1 & 4.2.

The table is used by two processes (maybe more that I haven’t encountered yet).

When you run the app using automatic migrations, if it’s doing any type of data initialization, it will check that Migration-History table to see if the database needs to be updated.

If you are using code-based migrations, Update-Database will also look at the Migration-History table.

If there’s no migration-history table (which there won’t be in an existing database), the model won’t be verified against the database with automatic migrations.

So you need to get that table into your existing database.

That table is created using the most recent migration file in your data layer.

Getting a Migration-History table into an existing database

If you’re starting with a new app, you won’t have that either.

So

Step 1) In Package Manager Console, execute “add-migration initial”.

This will force code first to create a migration class (which I chose to name “initial”) based on the code first model. It will have migration code to create all of the necessary database tables (based on what it’s found in the model) with their columns, keys and constraints.

But your database already exists! If you were to try to execute that migration and you have entities that map to those existing tables, EF will try to create the tables and tell you that the tables already exist.

Step 2) remove all code that duplicates existing tables from inside the Up and Down override methods in the initial migration. But, leave the methods there.

This is a little tricky. I find that it’s safer to do this when there’s nothing in my model yet that would required a database modification. That way it’s all clean. I’ve done this when I had one new class and then I had to start over again to get the true “initial database” and then start making any mods I want to my model.

Now, code first has a migration that contains no database modification code. That represents how things are at the beginning of EF 4.3 migrations involvement in your app.

Step 3) run Update-Database

This will do two things. It will execute the Up method in the initial migration, which has no effect on the database. And it will create the Migration-History table in your database based on the current state of the model. That way, if you are using automatic migrations, next time Code First does db initialization, it will compare the current model to the one stored in the migration-history to determine if the database needs to be migrated/updated.

Now you can evolve your app and let code first fix up your development database as needed.

The problem I encountered? A SQL Server 2000 Database

I was doing this with an existing client database (not production, but a copy for development … just sayin’  )

When running Update-Database I got the following error:

Conversion failed when converting date and/or time from character string.

Remember I’m not really updating my database. This was when code first was trying to create the Migration-History table.

It is an old database that has been recently updated to SS2008. Even though I’m using SQL Server 2008R2, that database was still set as a SQL Server 2000 database and was not happy with the format of the date field in the INSERT command:

INSERT INTO [__MigrationHistory] 
 ([MigrationId], [CreatedOn], [Model], [ProductVersion])
 VALUES ('201202161546124_initial', '2012-02-16T15:55:56.252Z', 
          [big-ass hash you don’t need to see], '4.3.0')

Luckily, I was able to just update my database version to 2008  and the insert command succeeded with no problem.

*still finding some more interesting tasks I have to do. For example, as I ensure that my model mappings reflect the database, I have to rewrite my initial migration and delete the migration-history table (there might be a scaffold command for that but I’m doing it in SSMS) and call update-database again.