How Migrations Diff Models: Snapshot Semantics, Operations, and Migration History

Learning outcomes

1. The practical problem: “the database already has the column—why does EF still want a migration?”

A ServiceHub developer adds a nullable dispatch code to WorkOrder. A teammate manually adds a similarly named column to a local SQLite file and expects dotnet ef migrations add to notice that the database is already correct. It does not. This is the first migration mental model to make explicit: scaffolding normally compares the current EF model with the last model snapshot checked into source control. The live database is not the general source of truth for the scaffold diff.

The current model is the finalized IModel built from conventions, attributes, and Fluent configuration. The snapshot is generated C# representing what EF believed the model looked like after the last migration. The migration class records operations needed to move from that snapshot toward the new model. The target database enters the story later, when EF asks which migration identifiers appear in __EFMigrationsHistory and executes missing migrations.

2. Four artifacts answer four different questions

A migration can therefore be perfectly “applied” according to history while the database also contains manual objects EF never modeled. Conversely, a developer can change the EF model without adding a migration; the database history remains unchanged because no migration exists to apply.

3. Design-time context creation comes before the diff

The EF tools must first construct the correct DbContext at design time. They may use application services, a parameterless/configurable path, or an IDesignTimeDbContextFactory<ServiceHubContext>. The course already established a design-time factory for the disposable SQLite lab. That factory must resolve the same provider/model configuration you intend to scaffold; it must not embed production credentials merely because tooling needs a connection path.

dotnet tool run dotnet-ef --versiondotnet ef dbcontext info --project src/ServiceHub.EfLabdotnet ef migrations list --project src/ServiceHub.EfLab

dbcontext info proves which context/provider the tool constructed. It does not prove that a production database is at the expected migration. Keep design-time construction, model-diff evidence, and deployment-state evidence separate.

4. Make one additive model change and scaffold it

For the disposable Chapter 15 branch, add an optional routing precursor named DispatchCode. Nullable-first is intentional: it lets old rows remain valid while the application and deployment evolve.

public string? DispatchCode { get; private set; }

builder.Property(x => x.DispatchCode)    .HasColumnName("dispatch_code")    .HasMaxLength(32);

dotnet ef migrations add AddDispatchCode   --project src/ServiceHub.EfLab   --output-dir Data/Migrations

At this point stop. A successful scaffold is not approval to deploy. Review the generated C# and the changed snapshot in version control.

5. Read Up, Down, and the snapshot as executable design intent

protected override void Up(MigrationBuilder migrationBuilder){    migrationBuilder.AddColumn<string>(        name: "dispatch_code",        table: "work_orders",        type: "TEXT",        maxLength: 32,        nullable: true);}protected override void Down(MigrationBuilder migrationBuilder){    migrationBuilder.DropColumn(        name: "dispatch_code",        table: "work_orders");}

The provider later turns these operations into database-specific DDL. On SQLite, AddColumn is directly supported; many other operations may require a table rebuild. The snapshot should now contain the dispatch_code mapping. If the migration file says one thing and a hand-edited snapshot says another, future diffs become unreliable.

6. Generate SQL, apply, and verify the history table

dotnet ef migrations script   --project src/ServiceHub.EfLab   --output artifacts/ch15-add-dispatch-code.sql

dotnet ef database update   --project src/ServiceHub.EfLab   --connection "Data Source=servicehub-lab.db"

PRAGMA table_info('work_orders');SELECT "MigrationId", "ProductVersion"FROM "__EFMigrationsHistory"ORDER BY "MigrationId";

The expected evidence is a dispatch_code column plus a new migration-history row. That proves the target file recorded and physically reflects this migration. It still does not prove there are no out-of-band schema changes elsewhere.

7. Deliberately wrong: use the database as the migration authoring surface

ALTER TABLE work_orders ADD COLUMN dispatch_code TEXT;

If you manually alter a database and then scaffold from the unchanged model/snapshot pair, EF's migration authoring process does not magically infer that manual action. Applying the generated migration may then fail because the column already exists. The repair depends on ownership: for a disposable local DB, recreate/reset it from migrations; for a shared database, investigate the exact drift and create/review a deliberate corrective migration or operational repair. Never “fix” production by deleting history rows until the migration plan looks convenient.

dotnet ef migrations has-pending-model-changes   --project src/ServiceHub.EfLab

This command compares the current model with the latest snapshot. It is useful in CI, but it does not inspect arbitrary live-database drift.

8. Hands-on lab: prove each layer independently

1. Copy or recreate the disposable servicehub-lab.db; do not use shared data.
2. Add the optional DispatchCode property and mapping.
3. Run migrations has-pending-model-changes; it should report a difference before scaffolding.
4. Scaffold AddDispatchCode, then inspect the migration and snapshot diff.
5. Generate the SQL script and identify the DDL before execution.
6. Apply the migration and query PRAGMA table_info plus __EFMigrationsHistory.
7. For cleanup on the disposable DB, update back to the previous migration, then remove the unapplied latest migration from source if you do not intend to carry it forward.

dotnet ef database update PreviousMigration --project src/ServiceHub.EfLab   --connection "Data Source=servicehub-lab.db"dotnet ef migrations remove --project src/ServiceHub.EfLab

9. Production judgment and bridge to safe migration editing

Treat migration files and snapshots as reviewed source artifacts. CI should build the context, fail on pending model changes, and generate deployable evidence without production credentials. Deployment should verify the target database’s known migration state and use a controlled schema-change identity. Do not rely on startup migration as a default, and do not assume the history table detects manual schema drift. The next lesson addresses the point where scaffolded code itself is dangerous: renames, data moves, ordering, and repairs.

Authoritative references

Use primary documentation when regenerating or adapting these deployment steps; migration behavior is provider- and version-sensitive.

• EF Core migrations overview
• Managing migrations
• Applying migrations
• EF Core .NET CLI tools
• SQLite provider limitations