I am generating an idempotent SQL Script out of EF Core migrations. The script first runs custom SQL Statements and then drops a column. When I re-run the script, it fails because it says the column does not exist. This would be great, but our migrations have the standard EF Core guard that makes sure the code is not executed twice if the migration ran through. I have validated that the code is in fact not run and the migration is guarded correctly.
Somehow the SQL Statement fails before running. Error: [S0001][207] Line 6: Invalid column name 'MyId'.
Am I missing something or is this a known behavior of SQL Server?
IF NOT EXISTS(SELECT *
FROM [__EFMigrationsHistory]
WHERE [MigrationId] = N'Migration1')
BEGIN
SELECT MyId FROM MyTable;
END;
GO
IF NOT EXISTS(SELECT *
FROM [__EFMigrationsHistory]
WHERE [MigrationId] = N'Migration1')
BEGIN
ALTER TABLE [MyTable]
DROP COLUMN [MyId];
END;
GO
IF NOT EXISTS(SELECT *
FROM [__EFMigrationsHistory]
WHERE [MigrationId] = N'Migration1')
BEGIN
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'Migration1', N'6.0.9');
END;
GO