TicketDesk 3 Dev Diary – EF Migrations & TD2 Upgrades – Part 2

Fixing MigrationsIn part 1, I outlined TicketDesk’s database management requirements, and how I hoped to use EF Migrations to meet them.

In this part, I will describe how I setup EF Migrations for new databases or TicketDesk 2 upgrades.

Migrations for a new TD3 database:

Ignoring TD2 upgrades, my first mission was to enable migrations for fresh TicketDesk 3 installations.

Bizarrely, Visual Studio 2012 did not include any tooling to support EF 5 migrations –odd since it has tooling for other EF 5 features. Developers interact with EF Migrations through the Package Manager Console; poor man’s tooling, but at least the commands are straight forward and well documented.

The Enable-Migrations command successfully created a migrations folder, generated the initial migration files, and added a migrations configuration class. As I mentioned before, there are database objects you can’t define with data annotations or the fluent API, so the next step was to edit the initial migration to include those pesky default constraints.

Once I had the initial migration finished, I was able to execute it from the PM console to produce the new database. The command line tools are fine for developers, but I also want the web application to initialize the database and run the migrations for me.

EF includes a migrations aware database initializer (MigrateDatabaseToLatestVersion), so I switched my app_start to use it. This also worked fine and the web application will make a fresh database on startup when it needs to.

Great! I’d be done if it weren’t for the need to support TicketDesk 2 databases upgrades.

Migration internals and limitations:

Before I get into how I Migrations work for TicketDesk 2 upgrades, I need to expalin a little about how migrations work internally, and point out some of its limitations.

From the developer’s viewpoint, EF Migrations are pretty simple. The initial migration is just a regular class that inherits from DbMigration. It has two method overrides, one for migrating up (containing create commands), and one for migrating down (containing drop commands). There are two other files associated with the initial migration. These track the parent/child relationships between migrations, but their main purpose is to manage the all-important model hash.

Initial-Migration

The hash is a compressed version of the entire code model at the time the migration was created. When the migration is run, the hash lands in the _MigrationHistory table in the database. This is how EF knows which migration was last used on the DB, and what the model looked like at that time.

The important thing to understand is that EF Migrations never look at your physical database schema. When EF generates a new migration, it does so by combobulating a model from the old hash value. Then it combobulates another model based on the hash of the current code. With before and after models in memory, it uses a complicated diff routine to figure out what needs to be changed to make the models match.

Since the real DB schema isn’t involved in migrations, you can modify the physical database manually, at your own peril, without migrations needing to know about it. In addition, you can modify the statements in the migration class in any way you want; EF will assume that whatever you changed will still result in a database that supports the storage needs of your EF model. Your physical database can also contain other tables and objects that are not related to the model being managed by EF.

The most glaring oversight in the design of EF 5 Migrations is that the _MigrationHistory table can only handle migrations for one model. If you have two different models and DbContexts, you cannot enable migrations on both unless they target different physical databases. If you try to share the same database, the migrations will overwrite each other’s records in the _MigrationsHistory table.

EF 6, currently an Alpha release, will fix this issue with a feature called “multi-tenant migrations”, but I’m not keen on using alpha bits just now.

TicketDesk 2 – Legacy Migration

While I’ve been careful to make sure the initial TD3 database is the same as a TD2 database, there are still minor differences. EF should run a different initial migration against an existing TD2 database. I will call this the “legacy migration” from here out. The legacy migration will just make a few alterations to eliminate the remaining schema differences.

I’ve already talked about how model hashes work, so here is why it matters… Each migration keeps track of its parent migration, which is how EF knows the correct order in which to run them. It is vital that both initial migrations use identical hashes, and have the same migration ID; otherwise, EF will get confused when I add new migrations later, and the linear migration path will be broken.

It took some experimentation to get two initial migrations to co-exist peacefully. Since both must share the same ID, class name, and hash value, it was simplest to put them in different assemblies –TicketDesk.Legacy for the legacy migrations, with regular migrations remaining in the TicketDesk.Domain project.

The legacy migration is a complete forgery, so I didn’t generate it with the PM console commands. Instead, I copied the initial migration files from TicketDesk.Domain into the legacy project. I then changed their namespaces, and gutted the up/down methods. This results in a legacy migration that does nothing, but when executed will add a _MigrationHistory table, and set the migration ID and hash values.

In order to run the legacy migration, EF will need a DbContext and migration configuration class. So, I made a LegacyDbContext. There is no actual model for this project, and we will never generate additional migrations for it, so the LegacyDbContext is empty, except for the constructors. It exists only so EF can open a connection to the database and run this one migration. I made a copy of the standard configuration file too, modifying its namespace and pointing it at the LegacyDbContext.

Legacy-Migration

The last step was writing custom commands into the legacy migration class’s “up” method to take care of those last few schema differences. For completeness, I also added appropriate commands to the “down” method that restores the database to the stock TD2 schema.

At this point, I was able to use the PM console commands to run either the legacy migration, or the regular initial migration, and both work as expected. Looking at the databases with the schema compare tool, there were only cosmetic differences between a fresh DB and an upgraded one. Perfect!

In the next part of this series, I will setup an initializer to selectively run the appropriate initial migration at runtime, and explore a few more gotchas with the EF 5 migrations framework.

One Reply to “TicketDesk 3 Dev Diary – EF Migrations & TD2 Upgrades – Part 2”

Leave a Reply

Your email address will not be published. Required fields are marked *