Entity Framework Migrations with an existing database

Introduction

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.

First Migration

Go to the package manager and Enable-Migrations.

Then add the first migration, Add-Migration InitalCreate, this is the migration that can be used to generate the tables.

But, it is probably not complete, it’s going to be missing indexes as mentioned above, stored procs and more.

For example the Vehicle table looks like this

And has an indexes on VehicleID, TyreTypeIDa and Name, but the migration did not pick up the Name index.

Testing the migration

Change the connection string to point to new database. And run Update-Database

You now have a new database based on the migration. Open your SQL Server Management Studio, right click on the reference database and click Tasks, then Generate Scripts…

Click next and the choose the tables to script out.

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.

Compare the files in a text comparison tool and you’ll see differences like this

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.

 1CreateTable( 
 2			"dbo.Vehicle", 
 3			c => new 
 4				{ 
 5					VehicleID = c.Int(nullable: false, identity: true), 
 6					Name = c.String(nullable: false, maxLength: 50), 
 7					Description = c.String(maxLength: 200), 
 8					MSRP = c.Double(nullable: false), 
 9					TyreTypeID = c.Int(nullable: false), 
10				}) 
11			.PrimaryKey(t => t.VehicleID) 
12			.ForeignKey("dbo.TyreType", t => t.TyreTypeID, cascadeDelete: true) 
13			.Index(t => t.TyreTypeID) 
14                        
15                         // this last line is the index I added by hand. 
16			.Index(t => t.Name, name: "IX_Name");

Drop the new database the run Update-Database.

The 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.

Remember that you can run arbitrary sql with the Sql method or even from the SqlFile method. This gives you full flexibility when using Entity Framework migrations.