Migration Portability: Why One EF Model Does Not Guarantee One Portable Schema

Learning outcomes

1. The problem: the model snapshot is relational, but migration operations are provider-rendered

EF migrations scaffold differences between the current EF model and the previous model snapshot. The generated operations then become SQL through the active provider. PostgreSQL has schemas, sequences, arrays and jsonb; MySQL has different identity, charset/collation, JSON/generated-column and index syntax. A single migration source file can quickly accumulate provider checks and raw SQL that are difficult to review or safely roll back.

2. Generate provider DDL side by side before choosing a migration strategy

Start with the same logical WorkOrder model and produce scripts under each provider configuration. Compare what the database will actually receive.

public sealed class PostgresDesignFactory    : IDesignTimeDbContextFactory<ServiceHubContext>{    public ServiceHubContext CreateDbContext(string[] args)    {        var cs = Environment.GetEnvironmentVariable("SERVICEHUB_PG")!;        var options = new DbContextOptionsBuilder<ServiceHubContext>()            .UseNpgsql(cs, o => o.MigrationsAssembly("ServiceHub.Migrations.Postgres"))            .Options;        return new ServiceHubContext(options);    }}// MySQL project uses UseMySQL(...) and// MigrationsAssembly("ServiceHub.Migrations.MySql").

dotnet ef migrations add Pg_Initial   --project src/ServiceHub.Migrations.Postgres   --startup-project src/ServiceHub.Persistence.Postgresdotnet ef migrations add MySql_Initial   --project src/ServiceHub.Migrations.MySql   --startup-project src/ServiceHub.Persistence.MySql

3. The same ValueGeneratedOnAdd can yield different DDL

A generated integer key may become PostgreSQL identity backed by a sequence and MySQL AUTO_INCREMENT. Defaults, booleans, GUID/UUID representations, timestamp types, JSON, computed/generated expressions and identifier quoting differ too.

A reviewer should be able to see those differences in separate migration commits rather than decode a giant provider conditional.

4. Provider-specific annotations belong in provider-specific model construction or migrations

If the PostgreSQL provider adds a GIN index, extension, array type or range, that metadata cannot be expected to render correctly under MySQL. Likewise MySQL generated-column/index/charset details are not Npgsql metadata. Keep those extensions isolated.

public static void ConfigureCore(ModelBuilder modelBuilder){    // keys, relationships and truly shared constraints}public static void ConfigurePostgres(ModelBuilder modelBuilder){    // Npgsql arrays/jsonb/ranges/index methods, if the product uses them}public static void ConfigureMySql(ModelBuilder modelBuilder){    // MySQL charset/collation/generated-column/provider metadata}

The final runtime model can still be one ServiceHubContext type if model caching/configuration is designed correctly, but many teams prefer separate provider context subclasses or assemblies to make the boundary obvious.

5. Raw SQL is the strongest reason not to share blindly

Migration Sql(...) is not translated by EF. PostgreSQL CREATE EXTENSION, casts, JSON operators, partial indexes or data-move expressions are not MySQL SQL; MySQL generated-column/backfill syntax is not PostgreSQL SQL.

// PostgreSQL migration only:migrationBuilder.Sql("CREATE EXTENSION IF NOT EXISTS pg_trgm;");// MySQL migration only (example shape; review exact target version syntax):migrationBuilder.Sql("UPDATE work_orders SET normalized_code = UPPER(customer_code) WHERE normalized_code IS NULL;");

6. Renames and data preservation can diverge by provider/version

Even when EF exposes a relational RenameColumn operation, the provider decides how to render it for the target server. Some changes require rebuilds or multi-step transitions on one engine and a direct ALTER on another. Review generated SQL against the actual database version.

// Phase A: add new nullable columnmigrationBuilder.AddColumn<string>(    name: "customer_code_v2",    table: "work_orders",    nullable: true);// Phase B: provider-specific backfill SQL in each migration assembly.// Phase C: application dual-read/write or compatibility window.// Phase D: enforce constraints/drop old column in later migration.

This pattern costs more migrations but is often safer than assuming a provider can atomically rename/convert a hot column with identical lock behavior.

7. Failure case: one shared migration stream contains both providers’ assumptions

The migration was scaffolded while Npgsql was active, so the snapshot includes PostgreSQL annotations and the migration uses a PostgreSQL default expression. A MySQL deployment later reuses it and fails during schema creation—or worse, a “genericized” migration silently stores a different type/collation.

Repair: choose one explicit policy: (A) support only one authoritative provider per migration stream; or (B) maintain separate provider migration assemblies with independent snapshots and CI. For materially different engines in this chapter, policy B is the clearer teaching/production path.

8. Migration CI needs multiple starting states, not only empty databases

A migration can create a fresh database successfully and still fail on a real upgrade because production contains data, older indexes, constraints or provider-version differences. Each provider pipeline should test:

9. Mandatory lab: maintain two provider migration streams

1. Create ServiceHub.Migrations.Postgres and ServiceHub.Migrations.MySql with provider-specific design-time factories/options.
2. Scaffold an initial migration for the same portable ServiceHub core model under each provider; compare the snapshots and DDL.
3. Add a provider-specific extension: PostgreSQL array/jsonb/search artifact and a MySQL collation/generated/JSON artifact. Confirm it appears only in its owning migration stream.
4. Add a portable column rename/data evolution and inspect how both providers render it.
5. Generate bounded scripts from a known prior migration for each provider and review them for data-loss/locking risks.
6. CI-test empty→latest and prior→latest against PostgreSQL 18.6 and MySQL 8.4 LTS disposable databases.
7. Attempt to point the MySQL deployment at the PostgreSQL migration assembly in a disposable environment; assert failure/mismatch and add a startup/deployment guard.
8. Drop all lab databases.

10. Production judgment and bridge

Migration portability is usually the wrong goal once providers have materially different physical capabilities. Keep migration ownership explicit, review provider SQL, and test every supported upgrade path against the real engine. The shared contract is the application’s data semantics and compatibility window—not one identical migration file. Lesson 5 turns this into an architecture boundary that keeps native capabilities intentional without infecting every service with provider conditionals.

Authoritative references

Migration artifacts must be reviewed in the context of the provider that renders them.

• EF Core migrations overview — model snapshots and migration workflow
• Migrations with multiple providers — official strategies for multiple provider migration sets
• Applying migrations — scripts, bundles and deployment cautions
• Npgsql EF Core provider documentation — PostgreSQL provider model/migration features
• MySQL Connector/NET EF Core support — Oracle MySQL provider EF Core behavior
• PostgreSQL 18 SQL commands — PostgreSQL DDL semantics for provider review