EF Core 9.0: Breaking Change in Migration Idempotent Scripts

In this post, let's have a look at a breaking change in EF Core 9.0 related to migration idempotent scripts.

Consider the following scenario.

Say we have a simple model like this.

public record Employee
{
    public int Id { get; set; }
 
    public string Name { get; set; }
}

And I have added a migration and have it applied in the database.

Now let's say I want update the model adding a new property, and need to update the value of existing rows by running a Custom SQL. Usually what we would do is after adding a migration, add code to execute the custom SQL, something like follows:

// Updated schema
public record Employee
{
    public int Id { get; set; }
 
    public string Name { get; set; }
 
    // New property
    public string Department { get; set; }
}

Modify migration to execute SQL statements to update existing rows: 

public partial class Secondary : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "Department",
            table: "Employees",
            type: "nvarchar(max)",
            nullable: false,
            defaultValue: "");
 
        // Update existing records
        migrationBuilder.Sql("UPDATE Employees SET Department = 'IT'");
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "Department",
            table: "Employees");
    }
}

With EF Core 9.0, if you create a idempotent script and execute it in SSMS (or use invoke-sqlcmd) , this is going to throw an error "Invalid column name 'Department'".

Invalid column name 'X'

The script is going to look like below:

--EF Core 9.0
 
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'20250107183107_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'20250107183107_Initial'
)
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20250107183107_Initial', N'9.0.0');
END;
 
IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107183141_Secondary'
)
BEGIN
    ALTER TABLE [Employees] ADD [Department] nvarchar(max) NOT NULL DEFAULT N'';
END;
 
IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107183141_Secondary'
)
BEGIN
    UPDATE Employees SET Department = 'IT'
END;
 
IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107183141_Secondary'
)
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20250107183141_Secondary', N'9.0.0');
END;
 
COMMIT;
GO

If we compare this to behavior of EF Core 8.x, the script EF Core 8.x generate will look like below:

--EF Core 8.x
 
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;
GO
 
IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107183107_Initial'
)
BEGIN
    CREATE TABLE [Employees] (
        [Id] int NOT NULL IDENTITY,
        [Name] nvarchar(max) NOT NULL,
        CONSTRAINT [PK_Employees] PRIMARY KEY ([Id])
    );
END;
GO
 
IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107183107_Initial'
)
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20250107183107_Initial', N'8.0.11');
END;
GO
 
COMMIT;
GO
 
BEGIN TRANSACTION;
GO
 
IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107183141_Secondary'
)
BEGIN
    ALTER TABLE [Employees] ADD [Department] nvarchar(max) NOT NULL DEFAULT N'';
END;
GO
 
IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107183141_Secondary'
)
BEGIN
    UPDATE Employees SET Department = 'IT'
END;
GO
 
IF NOT EXISTS (
    SELECT * FROM [__EFMigrationsHistory]
    WHERE [MigrationId] = N'20250107183141_Secondary'
)
BEGIN
    INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
    VALUES (N'20250107183141_Secondary', N'8.0.11');
END;
GO
 
COMMIT;
GO

You can see in the script generated by EF Core 9.0,  the GO statements after Control Statements (BEGIN...END) aren't no longer there and that is by design.

But then, because of that we are getting the compile error.

The work around is, in the migration, use EXEC as follows:

// Update existing records
migrationBuilder.Sql("EXEC('UPDATE Employees SET Department = ''IT''')");

Hope this helps.

Happy Coding.

Regards,

Jaliya