Entity Framework Migrations with an existing database
If you read my post on Entity Framework migrations, you might be saying, “that’s amazing, but that’s all very well for new projects, but we’ve got eighty tables in place already and that code first migration ship has sailed”. But no, you can still make use of Entity Framework migrations, it takes a some extra work, but it is well worth the effort.
If you already have POCOs that represent your databases tables and they really match your tables, you might be able to skip this step.
Reversing the Database
If your POCOs don’t match the database you can use the database tables to generate POCOs for you.
There are plenty of tutorials on Entity Framework’s reverse engineering tool. So I won’t go into too much detail.
Create a new solution with a console application as the startup project.
Set the connection string in your
app.config to point to the your reference database (this is the one that has the all the tables you are currently using).
Grab the Entity Framework Power Tools Beta 4 (https://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d/). Use this to reverse engineer the tables to POCOs. Review the outputted classes, mappings and context to make sure that they were generated the way you expect.
Notice that there is nothing on the POCOs to suggest where there should be indexes. Inferred primary key and foreign keys will be added in by the migration by default. But others will not.
But, it is probably not complete, it’s going to be missing indexes as mentioned above, stored procs and more.
And has an indexes on
Name, but the migration did not pick up the Name index.
Testing the migration
Click next and then Advanced, scroll to the bottom of the list and turn on Script Indexes and any other feature you may need (eg, triggers).
Follow the same steps to generate a script based on the new database.
The index on the Name column is missing in the new database.
No problem. Go back to the migration and add it in.
Now you have two choices here, you can add a new migration to alter the existing table, or you can change the initial migration.
I suggest changing the
InitalCreate migration, because you should try to get the this first migration to be as close as possible to the existing database.
Adding an index to a
CreateTable call is as simple as adding one line at the end.
Drop the new database the run
Vehicle will now be created with the new index.
You could add the index to the POCOs, but you might want to consider how this would affect any move away from Entity Framework. See Code First Data Annotations for more.
For a full list of what you can do with migrations see DbMigration Methods.
- Entity Framework non null foreign key migration
- Adding ROWGUIDCOL to Entity Framework Code First using migrations
- Making a column sparse with Entity Framework Migrations
- Requesting Data from two Data Stores in Sequence - Cache and a Database
- Requesting Data from two Data Stores in Parallel - Cache and Database