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.
Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!
On my development db (sqlexpress) I effectivley accomplished the same. But ran into some trouble on the production copy.
From an existing ef cf 4.1 I did Update-Package (resulting in ef 4.3)
Enable-Migrations
Update-Database
Now I enhanced my model and ran
Add-Migration AddMyNewColum
Looking good.
Now I ran
Update-Database -Script -SourceMigration:$InitialDatabase -TargetMigration:"AddMyNewColum"
to generate an .sql script that I ran against the production db. At which point the newly web deployed application failed with the error:
”The model backing the ‘BlogContext’ context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).”
Fired up ms sql studio and noted that the EdmMetadata hash was the same on the development and production dbs. I also checked that the __MigrationHistory had been created on the production db. I deleted the app and redeployed. Still got that error. Finally just did a backup and restore of the dev to prod db.
Subsequent scripted migrations on the development db work resulting in 🙂 I’m still curious why the initial scripting failed.
I’m wondering if the generated .sql script can be included in a web deploy package to update the deployed db?
asp.net mvc with razor, ef code first 4.3 and nuget is way cool!
Hey Julie,
We are going to add a flag to Update-Database to allow you to more easily initialize the history table on existing databases.
Cheers,
Andrew.
Thank you for posting this!…I’d been beating my face on the keyboard for the past 10 minutes. Should have come here first 🙂
How do I get my model classes to match my database? Or, do I need to manually create them before doing this?
I just bought Julie’s Code First book and it looks like my answer will be in the book. Its a great book so far!
Hi Julie, I really like your videos and blog. I have a question. How do I create the domain class from an existing database. I tried using the edmx file, but migrations apparently does not like that. Sometimes that part is confusing. Could you please more about it?
@Richard: check out this tool thedatafarm.com/…/quick-look-at-r
Hey Julie, is there a way to turn off migration altogether?
I have an existing database with no metadata table. I also have code first POCO classes. But if I change a model class, say to make a field nullable, and run the project, EF 4.3 throws an exception saying something like "The model backing the ‘XContext’ context has changed since the database was created, use migration to update your database.."
How can I avoid this exception and let EF know that I don’t want it to check compatibility?
@Randhir, in code you can
Database.SetInitializer<mycontext>(null)
You can also do this in config. Look at disabledatabaseinitialization example in this blog post: blogs.msdn.com/…/ef-4-3-configur
Hi Julie,
I want to use migrations to get the AspNetMembership objects in my database. Any pointers for that?
Thanks!
Don’t bother. After reading this article from Craig Stuntz, I decided to place it in a separate database instead.
blogs.teamb.com/…/38558
Thanks anyway!
Very helpful blog!
how to create the table under the System Tables(sql 2008R2)
Hi Julie,
As a long time DB first developer, I’ve actually enjoyed re-wiring my brain to code first. However, I’m still struggling with my initialization code in my project, and I’m thinking through an application lifecycle management of the DB.
Say that I have an application in the field, where users are either installing the application for the first time, or the application is installing a new version of the application which may include one or more UP migrations.
I have found that if I test my application when it creates it with migrations, the creation fails. But if I use the CreateIfNotExist command, it initializes correctly!
Perhaps its me, but I think there is confusion out there between the various initializers, and perhaps a division between the initial initializers and the ones that are created after migrations.
That’s a long story to ask this…
I would love to see a short video that focused more on ALM phases with code first.
Thanks for all your work in this field
Richard
very good tip, it help me a lot
You suggest removing the code for the initial migration.
Would it be useful to instead comment out the migration code, do the initial migration, and then uncomment it?
My thought is that this way the migration will still be there if you ever need to create the DB from scratch, say in a new installation.
Am I on the right track here or is this asking for trouble?
Jacob
Thank you Julie for your fantastic work. Just wanted to mention that I followed your videos on Plural Site, especially about bounded contexts.
As I played with the code, I changed the contextkey in the DbMigrationsConfiguration class I made, to reflex the new context that I decided I would use with migrations. When I tried to make changes to the domain classes, I kept receiving errors that "this object already exists in the database", meaning that it was trying to create a whole new database instead of migrating. The solution was to open the _MigrationHistory table and change the contextkey column in the previous entries to reflect my new contextkey. I wouldn’t have thought to do that without this post. Thanks again!!
Awesome. I was facing the error “There is already an object named ‘[some tablename]’ in the database” while trying to seed the database using migrations. Then after reading the article i have removed the code in the Up and Down methods and run the ‘update-database’ command for the initial migration. And it worked. Thanks a lot.
Your tip helped. I wanted to say Thank You! 🙂
🙂
Can someone help me with this question. I am using a code first approach and after my InitialCreate migrations, I need to insert values into the Categories table in order for a user to see the dropDown menu.
Should I create a new Migration and add a Sql(“insert into Categories) there?
If yes, how can I write to query to add multiple values?
This query does not work, what am I doing wrong?
public override void Up()
{
Sql(“insert into Categories (Id, Name) values
(‘0, Item\’, ‘1, Item1\’, ‘2, ‘Item2\’, ‘3, \’Item3\’, ‘4, Item4’)”);
}
The INSERT statement should be an appropriate insert for whatever flavor of SQL your database uses (I’d check docs for your database). Also if you database is generating values, then you should be inserting the key values.
Alternatively, ef migrations does have a way to seed on migration. Here’s some documentation on how to use that from Microsoft: https://www.asp.net/web-api/overview/data/using-web-api-with-entity-framework/part-3