Invalid column name 'X', after alter table and running dotnet ef migrations script --idempotent

[jaliyaudagedara]

Consider the following simple Console Application that uses EF Core 9.0

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net9.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="9.0.0">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="9.0.0" />
  </ItemGroup>

</Project>

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

using var context = new MyDbContext();

Console.ReadLine();

// Initial schema
public record Employee
{
    public int Id { get; set; }

    public string Name { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer("<ConnectionString>")
            .LogTo(Console.WriteLine, LogLevel.Debug);
    }
}

• Create database with initial schema

dotnet ef migrations add Initial
dotnet ef database update

• Update schema adding a new property

// Updated schema
public record Employee
{
    public int Id { get; set; }

    public string Name { get; set; }

    // Added a new property
    public string Department { get; set; }
}

• Add a migration for the updated schema

dotnet ef migrations add Secondary

• Modify migration to execute custom SQL

public partial class Secondary : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "Department",
            table: "Employees",
            type: "nvarchar(max)",
            nullable: false,
            defaultValue: "");

        // Custom SQL
        migrationBuilder.Sql("UPDATE Employees SET Department = 'IT'");
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "Department",
            table: "Employees");
    }
}

• Generate migration Script

dotnet ef migrations script --idempotent

Generated Script

IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
    CREATE TABLE [__EFMigrationsHistory] (
        [MigrationId] nvarchar(150) NOT NULL,
        [ProductVersion] nvarchar(32) NOT NULL,
        CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
    );
END;
GO

BEGIN TRANSACTION;
IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107175345_Initial'
)
BEGIN
    CREATE TABLE [Employees] (
        [Id] int NOT NULL IDENTITY,
        [Name] nvarchar(max) NOT NULL,
        CONSTRAINT [PK_Employees] PRIMARY KEY ([Id])
    );
END;

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107175345_Initial'
)
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20250107175345_Initial', N'9.0.0');
END;

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107175433_Secondary'
)
BEGIN
    ALTER TABLE [Employees] ADD [Department] nvarchar(max) NOT NULL DEFAULT N'';
END;

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107175433_Secondary'
)
BEGIN
    UPDATE Employees SET Department = 'IT'
END;

IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107175433_Secondary'
)
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20250107175433_Secondary', N'9.0.0');
END;

COMMIT;
GO

Try to execute in SSMS (same error with invoke-sqlcmd)

Msg 207, Level 16, State 1, Line 46
Invalid column name 'Department'.

Include provider and version information

EF Core version: 9.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: 9.0.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.13.0 Preview 2.1

A video of steps to reproduce:
https://github.com/user-attachments/assets/7ac16d34-b9f8-4b3a-a53e-9d5f73a3abc3

Sample project:
EfCore9.zip

[cincuranet]

This is the same reason as explained in #12911. The solution for you is in #21933 (use EXEC).

[jaliyaudagedara]

@cincuranet, thanks for the reply.

Yes, that is the reason and EXEC() works.

This isn't happening with EF Core 8.0.0, because there is GO statements. Should we document this as a breaking change?

[cincuranet]

@maumar it looks like we don't have this documented. Can you have a look?