Published by Marco on 13. Jun 2014 11:37:10
------------------------------------------------------------------------

"The version of EF Migrations discussed in this article is 5.0.20627. The
version of Quino is less relevant: the features discussed have been supported
for years. For those in a hurry, there is a "tl;dr near the end of the article."
<#tldr>"

We use Microsoft Entity Framework (EF) Migrations in one of our projects where
we are unable to use Quino. We were initially happy to be able to automate
database-schema changes. After using it for a while, we have decidedly mixed
feelings.

As developers of our own schema migration for the Quino ORM, we're always on the
lookout for new and better ideas to improve our own product. If we can't use
Quino, we try to optimize our development process in each project to cause as
little pain as possible.

[EF Migrations and branches]

We ran into problems in integrating EF Migrations into a development process
that uses feature branches. As long as a developer stays on a given branch,
there are no problems and EF functions relatively smoothly. [1]

However, if a developer switches to a different branch -- with different
migrations -- EF Migrations is decidedly less helpful. It is, in fact, quite
cryptic and blocks progress until you figure out what's going on.

Assume the following not-uncommon situation:

  * The project is created in the master branch
  * The project has an initial migration BASE
  * Developers A and B migrate their databases to BASE
  * Developer A starts branch feature/A and includes migration A in her database
  * Developer B starts branch feature/B and includes migration B in his database

We now have the situation in which two branches have different code and each has
its own database schema. Switching from one branch to another with Git quickly
and easily addresses the code differences. The database is, unfortunately, a
different story.

Let's assume that developer A switches to branch feature/B to continue working
there. The natural thing for A to do is to call "update-database" from the
Package Manager Console [2]. This yields the following message --
all-too-familiar to EF Migrations developers.

[image]

"Unable to update database to match the current model because there are pending
changes and automatic migration is disabled. Either write the pending changes to
a code-based migration or enable automatic migration. [...]"

This situation happens regularly when working with multiple branches. It's even
possible to screw up a commit within a single branch, as illustrated in the
following real-world example.

  * Add two fields to an existing class
  * Generate a migration with code that adds two fields
  * Migrate the database
  * Realize that you don't need one of the two fields
  * Remove the C# code from the migration for that field
  * Tests run green
  * Commit everything and push it

As far as you're concerned, you committed a single field to the model. When your
co-worker runs that migration, it will be applied, but EF Migrations immediately
thereafter complains that there are pending model changes to make. How can that
be?

[Out-of-sync migrations != outdated database]

Just to focus, we're actually trying to get real work done, not necessarily
debug EF Migrations. We want to answer the following questions:

   1. Why is EF Migrations having a problem updating the schema?
   2. How do I quickly and reliably update my database to use the current schema
      if EF Migrations refuses to do it?

The underlying reason why EF Migrations has problems is that it does not
actually know what the schema of the database is. It doesn't read the schema
from the database itself, but relies instead on a copy of the EF model that it
stored in the database when it last performed a successful migration.

That copy of the model is also stored in the resource file generated for the
migration. EF Migrations does this so that the migration includes information
about which changes it needs to apply and about the model to which the change
can be applied.

If the model stored in the database does not match the model stored with the
migration that you're trying to apply, EF Migrations will not update the
database. This is probably for the best, but leads us to the second question
above: what do we have to do to get the database updated?

[Generate a migration for those "pending changes"]

The answer has already been hinted at above: we need to fix the model stored in
the database for the last migration.

Let's take a look at the situation above in which your colleague downloaded what
you thought was a clean commit.

From the Package Manager Console, run add-migration foo to scaffold a migration
for the so-called "pending changes" that EF Migrations detected. That's
interesting: EF Migrations thinks that your colleague should generate a
migration to drop the column that you'd only temporarily added but never checked
in.

That is, the column isn't in his database, it's not in your database, but EF
Migrations is convinced that it was once in the model and must be dropped.

How does EF Migrations even know about a column that you added to your own
database but that you removed from the code before committing? What dark magic
is this?

The answer is probably obvious: you did check in the change. The part that you
can easily remove (the C# code) is only half of the migration. As mentioned
above, the other part is a binary chunk stored in the resource file associated
with each migration. These BLOBS are stored in the table _MigrationHistory table
in the database. 

[image][image]

[How to fix this problem and get back to work]

Here's the tl;dr: generate a "fake" migration, remove all of the C# code that
would apply changes to the database (shown below) and execute update-database
from the Package Manager Console.

[image]

This may look like it does exactly nothing. What actually happens is that it
includes the current state of the EF model in the binary data for the last
migration applied to the database (because you just applied it).

Once you've applied the migration, delete the files and remove them from the
project. This migration was only generated to fix your local database; do not
commit it.

[Everything's cool now, right?]

Applying the fix above doesn't mean that you won't get database errors. If your
database schema does not actually match the application model, EF will crash
when it assumes fields or tables are available which do not exist in your
database.

Sometimes, the only way to really clean up a damaged database -- especially if
you don't have the code for the migrations that were applied there [3] -- is to
remove the misapplied migrations from your database, undo all of the changes to
the schema (manually, of course) and then generate a new migration that starts
from a known good schema.

[Conclusions and comparison to Quino]

The obvious answer to the complaint "it hurts when I do this" is "stop doing
that". We would dearly love to avoid these EF Migrations-related issues but
developing without any schema-migration support is even more unthinkable. 

We'd have to create upgrade scripts manually or would have to maintain scripts
to generate a working development database and this in each branch. When
branches are merged, the database-upgrade scripts have to be merged and tested
as well. This would be a significant addition to our development process, has
maintainability and quality issues and would probably slow us down even more.

And we're certainly not going to stop developing with branches, either.

We were hoping to avoid all of this pain by using EF Migrations. That EF
Migrations makes us think of going back to manual schema migration is proof that
it's not nearly as elegant a solution as our own Quino schema migration, which
never gave us these problems.

Quino actually reads the schema in the database and compares that model directly
against the current application model. The schema migrator generates a custom
list of differences that map from the current schema to the desired schema and
applies them. There is user intervention but it's hardly ever really required.
This is an absolute godsend during development where we can freely switch
between branches without any hassle. [4]

Quino doesn't recognize "upgrade" versus "downgrade" but instead applies
"changes". This paradigm has proven to be a much better fit for our agile,
multi-branch style of development and lets us focus on our actual work rather
than fighting with tools and libraries.

--------------------------------------------------------------------------------


[1] EF Migrations as we use it is tightly bound to SQL Server. Just as one
    example, the inability of SQL Server to resolve cyclic cascade dependencies
    is in no way shielded by EF Migrations. Though the drawback originates in
    SQL Server, EF Migrations simply propagates it to the developer, even though
    it purports to provide an abstraction layer. Quino, on the other hand, does
    the heavy lifting of managing triggers to circumvent this limitation.


[1] As an aside, this is a spectacularly misleading name for a program feature.
    It should just be called "Console".


[1] I haven't ever been able to use the Downgrade method that is generated with
    each migration, but perhaps someone with more experience could explain how
    to properly apply such a thing. If that doesn't work, the method outlined
    above is your only fallback.


[1] The aforementioned database-script maintenance or having only very discrete
    schema-update points or maintaining a database per branch and switching with
    configuration files or using database backups or any other schemes that end
    up distracting you from working.