EF Core idempotent migration script fails despite check on applied migration

Question

I am using EF Core (3.1.15). In a previous migration (also created in 3.1.15), a column was referenced that was dropped later on. The idempotent script does check if the migration was performed on the database (which it is, and the reference still shows in the __EFMigrationsHistory table). However the check doesn't have the expected result and the script due to the inexistent column.

Q: why is the inexistent column tripping the execution of the SQL script?

Script was created with

dotnet-ef migrations script -i -o migrations.sql

Relevant part of the automated script that fails, where ReferenceToLedgerId is the column dropped in later migration:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210612052003_CLedger')
BEGIN
    UPDATE LedgerTable SET LedgerId = ReferenceToLedgerId
END;

Error:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'ReferenceToLedgerId'

When running the following SQL query, the result comes back as expected:

SELECT * 
FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210612052003_CLedger'

MigrationId	ProductVersion
20210612052003_CLedger	3.1.15

The database is Azure SQL Database. Script doesn't fail on local SQL dev database. A dozen migrations have been applied since then, and only now the script fails.

Below was the call that created the specific script:

migrationBuilder.Sql("UPDATE LedgerTable set LedgerId = ReferenceToLedgerId", true);

I tried to place the table and column names in square brackets, but that made no difference (eg. [ReferenceToLedgerId]. The script fails in Azure DevOps release when using SQLCMD and also fails when using Azure Data Studio, both accessing the Azure SQL Database.

Additional check

I changed the script to do a quick check:

PRINT '#Before IF'
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210612052003_CLedger')
BEGIN
    PRINT '#Within IF'
    --UPDATE LedgerTable SET LedgerId = ReferenceToLedgerId
END;

PRINT '#After IF'

To which I get the following result:

Started executing query at Line 1 #Before IF #After IF Total execution time: 00:00:00.010

If I uncomment the UPDATE statement it fails again. So I can only conclude that the code path works as intended, but that the server still checks for the existence of the column. I am not familiar with SQL to understand why this would be, or why it only fails for this one line while the column itself is referenced in other lines of the SQL script without it failing.

user_7297700 · Answer

That batch will fail on every version of SQL Server. eg

use tempdb
go
create table __EFMigrationsHistory(MigrationId nvarchar(200))
create table LedgerTable(LedgerId int)
go
insert into __EFMigrationsHistory(MigrationId) values (N'20210612052003_CLedger')
go
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20210612052003_CLedger')
BEGIN
    UPDATE LedgerTable SET LedgerId = ReferenceToLedgerId
END;

Fails with

Msg 207, Level 16, State 1, Line 8
Invalid column name 'ReferenceToLedgerId'.

Because the batch cannot be parsed and compiled. It's simply not legal to reference a non-existent table or column in a TSQL batch.

You can work around this by using dynamic SQL, so that the batch referencing the non-existent column is not parsed and compiled unless the migration is being applied.

migrationBuilder.Sql("exec('UPDATE LedgerTable set LedgerId = ReferenceToLedgerId')", true);

This is documented here:

Tip

Use the EXEC function when a statement must be the first or only one in a SQL batch. It might also be needed to work around parser errors in idempotent migration scripts that can occur when referenced columns don't currently exist on a table.

https://docs.microsoft.com/en-us/ef/core/managing-schemas/migrations/operations

EF Core idempotent migration script fails despite check on applied migration

1 Answers