Fully embrace provider-specific migrations

[ajcvickers]

Current situation

Scaffolded migrations target a specific database provider. This means that it is not in general possible to scaffold a migration against one provider and then use it with another provider.

However, we try to keep the migration "relatively agnostic to the provider" so that there should be only small differences between the migrations for different providers. The most obvious case of this (and perhaps the only one) is that we don't put store types obtained from a type mapping in the migration, because they are very provider-specific. But this has been a source of bugs such as #12212.

So if we drop the "relatively agnostic" part, then we can make provider-specific migrations more robust by removing a source of complexity and bugs.

What changed?

So why did we not take this path already? Well I, for one, believed that it was relatively easy to take two migrations and edit them together into a single migration with some conditional code. Indeed, we even documented this. But it turns out that this isn't easy, because if you want to maintain a full set of migrations that can go up/down for multiple providers, then you also need to merge the model snapshot and the code-behind, both of which are also provider specific. It's hard to believe that many people are doing this, let alone doing it right.

How to we make the experience good

The other way to handle migrations for multiple providers is to have multiple parallel sets of migrations, one for each provider. This is also documented.

However, I think we could make this easier (post-3.0) by annotating the migration with the provider it targets and then adding some support in tooling. For example, #15174 is about throwing if the migration was created for a different provider. But we could extend this to find the right migration from multiple annotated with different provider names.

What should we do now?

Proposal:

• Fix issues like Snapshot should preserve the property CLR type when value conversion is used #12212 by storing the database type in the model snapshot.
• Implement Consider throwing/warning by default if a migration is used against a different provider than it was generated for #15174 to more forcibly assert that migrations are provider-specific.
• Update the docs to stop recommending conditional code.

[ajcvickers]

/cc @bricelam @divega

[ajcvickers]

Notes from triage:

• We will take this approach; putting this on the backlog to improve the experience post 3.0
• Consider throwing/warning by default if a migration is used against a different provider than it was generated for #15174 has been moved to 3.0 to help cover the pit-of-failure for 3.0
• We will store the column type as needed in the snapshot.
• Docs change tracked by Update migrations-with-multiple-providers guidance EntityFramework.Docs#1554

[bricelam]

As part of this work, I'd like to investigate:

• Making IMigrationsModelDiffer a provider-specific service. This would give providers an opportunity to generate new operations (like Add support for renaming primary/foreign keys. #5662) We'd also need a language-agnostic way of defining the code for these operations.
• Codifying the dependencies between operations. This would simplify the topological sort of operations, and could also be reused to during rebuilds. This should also be extensible by providers so that, for example, MySQL could declare a dependency between foreign keys and indexes (see Changing 1:N to 1:1 relationship on foreign key leads foreign key constraint violation on executing migration #21362)
• Giving providers an opportunity to rewrite operations before generating code. This would burn things like rebuilds into the code instead of handling them behind the scenes during SQL generation. This would potentially break existing migrations, and we'd need something like Migrations: A command to upgrade snapshots and backing models #18557 to enable upgrading.