Full-history idempotent EF script (from empty DB) fails on SQL Server in pre-58 migrations #127

New Issue

2026-11-12T06:55:21-05:00

dohertj2 commented

2026-11-12 06:55:21 -05:00

Found while fixing #126. dotnet ef migrations script --idempotent from migration 0 (fresh install via script) does not run on SQL Server. Run against an empty local DB with sqlcmd -b, it stops at migration 4 (20260321210402_AddSiteIdToDataConnections): Msg 207 Invalid column name 'SiteId', the same add-column-then-reference-in-one-batch pattern as #126. Without -b, more batches fail:

Msg 207 ExecutionId / ParentExecutionId (raw-SQL references to columns added in the same migration)
Msg 1911 Column name 'Category' does not exist
Msg 156 Incorrect syntax near 'FUNCTION' and Msg 178 RETURN (CREATE FUNCTION inside an IF ... BEGIN block: it must be the first statement of its batch, so it needs EXEC(N'...'))
Msg 137 Must declare the scalar variable @legacy / @props / @inner / @legacyType (DECLARE inside a raw-SQL block that EF splits or guards per statement)

Impact: low. Every deployed environment is at >= 57, and the idempotent script from 57 to head runs clean (since #126; IdempotentMigrationScriptTests guards it). Fresh installs use database update / Migrate(), which run one command at a time and are unaffected. This only matters if a fresh install is ever done from a generated script.

Fix shape: the same as #126. Wrap the affected migrationBuilder.Sql(...) bodies in EXEC(N'...') (CREATE FUNCTION/VIEW/PROC must be wrapped), then extend IdempotentMigrationScriptTests with a from-empty case. This touches early migrations, so the end state needs a schema-diff check.

Found while fixing #126. `dotnet ef migrations script --idempotent` from **migration 0** (fresh install via script) does not run on SQL Server. Run against an empty local DB with `sqlcmd -b`, it stops at migration 4 (`20260321210402_AddSiteIdToDataConnections`): Msg 207 `Invalid column name 'SiteId'`, the same add-column-then-reference-in-one-batch pattern as #126. Without `-b`, more batches fail: - Msg 207 `ExecutionId` / `ParentExecutionId` (raw-SQL references to columns added in the same migration) - Msg 1911 `Column name 'Category' does not exist` - Msg 156 `Incorrect syntax near 'FUNCTION'` and Msg 178 `RETURN` (CREATE FUNCTION inside an IF ... BEGIN block: it must be the first statement of its batch, so it needs `EXEC(N'...')`) - Msg 137 `Must declare the scalar variable @legacy / @props / @inner / @legacyType` (DECLARE inside a raw-SQL block that EF splits or guards per statement) **Impact:** low. Every deployed environment is at >= 57, and the idempotent script from 57 to head runs clean (since #126; `IdempotentMigrationScriptTests` guards it). Fresh installs use `database update` / `Migrate()`, which run one command at a time and are unaffected. This only matters if a fresh install is ever done from a generated script. **Fix shape:** the same as #126. Wrap the affected `migrationBuilder.Sql(...)` bodies in `EXEC(N'...')` (CREATE FUNCTION/VIEW/PROC must be wrapped), then extend `IdempotentMigrationScriptTests` with a from-empty case. This touches early migrations, so the end state needs a schema-diff check.

dohertj2 added the type:debt priority:p3 labels 2026-11-12 06:55:21 -05:00

dohertj2 referenced this issue

2026-11-12 06:58:01 -05:00

Idempotent EF migration script for 58→64 fails: AddAuditLogHashChain adds ChainSeq and indexes it in one batch (Msg 207) #126

dohertj2 commented

2026-11-12 07:26:09 -05:00

Fixed on main 08ef94bb. New MigrationSql.Deferred(sql) wraps raw-SQL bodies in EXEC(N'...') (quotes doubled; Migrate() executes identical text). Touched Up() only in 4 migrations: 20260321210402_AddSiteIdToDataConnections (DECLARE/cursor block), 20260512211204_MigrateParametersToJsonSchema (3 CREATE FUNCTIONs + the UPDATE using them), 20260521184044_AddAuditLogExecutionId and 20260521210254_AddAuditLogParentExecutionId (filtered partition-aligned indexes). The 1911 'Category' / @props / ExecutionId errors were knock-ons and cleared. Full from-empty script now runs under sqlcmd -b (64 migrations), and re-runs as a no-op.

Schema equivalence: byte-identical schema snapshot (columns/types/defaults/indexes incl. filter+partition/FKs/checks/function+view body hashes/perms/partition objects/row checksums) for fixed vs unfixed Migrate(), and fixed from-empty script vs unfixed Migrate(); 57→head script still clean; seeded data through MigrateParametersToJsonSchema identical both ways. New test IdempotentScript_FromEmpty_RunsClean_AndMatchesMigrate (fails on unfixed code with Msg 207 SiteId; positive control proven). ConfigurationDatabase.Tests 537/0.

Not covered: Down() bodies — a generated rollback script may still hit the same pattern.

Fixed on main 08ef94bb. New MigrationSql.Deferred(sql) wraps raw-SQL bodies in EXEC(N'...') (quotes doubled; Migrate() executes identical text). Touched Up() only in 4 migrations: 20260321210402_AddSiteIdToDataConnections (DECLARE/cursor block), 20260512211204_MigrateParametersToJsonSchema (3 CREATE FUNCTIONs + the UPDATE using them), 20260521184044_AddAuditLogExecutionId and 20260521210254_AddAuditLogParentExecutionId (filtered partition-aligned indexes). The 1911 'Category' / @props / ExecutionId errors were knock-ons and cleared. Full from-empty script now runs under sqlcmd -b (64 migrations), and re-runs as a no-op. Schema equivalence: byte-identical schema snapshot (columns/types/defaults/indexes incl. filter+partition/FKs/checks/function+view body hashes/perms/partition objects/row checksums) for fixed vs unfixed Migrate(), and fixed from-empty script vs unfixed Migrate(); 57→head script still clean; seeded data through MigrateParametersToJsonSchema identical both ways. New test IdempotentScript_FromEmpty_RunsClean_AndMatchesMigrate (fails on unfixed code with Msg 207 SiteId; positive control proven). ConfigurationDatabase.Tests 537/0. Not covered: Down() bodies — a generated rollback script may still hit the same pattern.

dohertj2 closed this issue

2026-11-12 07:26:09 -05:00

dohertj2 referenced this issue from a commit

2026-11-12 07:26:16 -05:00

fix(db): full-history idempotent migration script runs clean from an empty DB (#127)

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: dohertj2/ScadaBridge#127