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.
