Migrations and Schema changes

Question

Hi, 

I'm looking at the Migrations support in 4.3 and have a question.

I see how this is useful for some changes, eg. adding a new property. It's pretty straightforward and the documentation explains this well.

But what about something more sophisticated, eg, where I want add a new table and many to many relationship? Currently I do this with code first straight in the DbContext setup and migrate changes with a third party tool, but how would such a major change be handled by Migrations.

I'm guessing I have still have to make the changes to the DbContext (otherwise the changes won't be visible in the model) AND a migration for older db versions. Is this right?

Confused,

Paul.

Answer 1

Hi Paul,

The general Migrations workflow goes something like this.

1. Update your code-first model classes and/or DbContext configuration
2. Run Add-Migration to scaffold a new migration. This will detect what has changed in your model since your last migration and write out the steps needed to go from the old version to the current version.
3. Review the scaffolded migration and add any additional changes you want performed to your database. (This typically includes things not represented in the model like indexes, etc.)
4. Run Update-Database to apply the migration to the database.

All of the changes that you could possibly make to a code-first model are handled very well by Migrations. Some advanced changes, like adding an IDENTITY column to a table, require what is called a table rebuild by SQL Server. Table rebuilds are not directly handled by the first version (4.3) of Migrations, but they can still be achieved by writing your own custom SQL.

Other changes aren't always detected as you would expect by Migrations. For example, if you rename a propertey on your class, Migrations only sees that a column with the old name is gone a new column has been added; it scaffolds this as an Add and a Drop. During the review step, you can tell Migrations what your real intentions were by replacing the Add and Drop calls and with a Rename call.

Databases are all about tables and columns. When you add a many-to-many relationship to your model, Code First knows that this maps to a join-table and some foreign key columns at each table involved in the relationship. Migrations will see this new table and each new foreign key column and add them to your database.

Hopefully this clears things up a bit. Let me know if you have any further questions.

---

Brice Lambson

Answer 2

Hi Brice,

That makes things a lot clearer now.

My main concern is making some model changes and forgetting to run add-migration. I was wondering if I could setup VS to run a-m as part of the build process, but I guess thats dangerous for the add/drop/rename reason you specified. 

Thanks,

Paul.

Answer 3

Automatic migrations are a streamlined way of applying model changes without having to call Add-Migration. The MigrateDatabaseToLatestVersion database initializer allows you to skip calling Update-Database and instead will apply the migrations when your app starts.

With automatic migrations, a safeguard is provided -- if any operation would result in data loss, we throw an exception, and you have to run Update-Database -Force to actually apply the change to the database.

You can use the MigrateDatabaseToLatestVersion database initializer like so. It can also be set in your App/Web.config file.

Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Migrations.Configuration>());

---

Brice Lambson

Answer 4

Oh, it's also worth mentioning, that with the default database initaizer, if your model and database are ever out of sync, an exception is thrown letting you know. The MigrateDatabaseToLatestVersion is basically the same except instead of throwing an exception, it goes ahead and just calls Update-Database for you.

---

Brice Lambson

Answer 5

My only thoughts with automatic migrations is that they're not scripted. I would've preferred if they just created a timestamped file in the Migrations folder rather than monkey about with metadata:

The migration also has a code-behind file that captures some metadata. This metadata will allow Code First Migrations to replicate the automatic migrations we performed before this code-based migration. This is important if another developer wants to run our migrations or when it’s time to deploy our application. 

The reason for this being source control. Try merging two conflicting files of metadata! Also they're human-readable.