EF Core 7 AlterColumn generates an UPDATE in the SQL Server migration that can cause issues if the column is renamed in a subsequent migration

[ajmoskwa]

AlterColumn appears to have added an UPDATE clause. When using all migrations to generate one big .sql migration file (which we need to do for Devops Pipelines) the migration has a syntax error in it on the UPDATE clause if the column subject to the UPDATE is later renamed in a subsequent migration.

Here is the statement in the migration that causes the issue, (The "County" column was later renamed in a migration that happened after this one)

            migrationBuilder.AlterColumn<short>(
                name: "County",
                table: "Address",
                type: "smallint",
                nullable: false,
                defaultValue: (short)0,
                oldClrType: typeof(short),
                oldType: "smallint",
                oldNullable: true);

Here is the SQL code that EF Core 7 generates for this migration:

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20220525102303_ModelUpdates')
BEGIN
    DECLARE @var6 sysname;
    SELECT @var6 = [d].[name]
    FROM [sys].[default_constraints] [d]
    INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
    WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Address]') AND [c].[name] = N'County');
    IF @var6 IS NOT NULL EXEC(N'ALTER TABLE [Address] DROP CONSTRAINT [' + @var6 + '];');
    UPDATE [Address] SET [County] = CAST(0 AS smallint) WHERE [County] IS NULL;
    ALTER TABLE [Address] ALTER COLUMN [County] smallint NOT NULL;
    ALTER TABLE [Address] ADD DEFAULT CAST(0 AS smallint) FOR [County];
END;
GO

Here is the SQL code that EF Core 6 generates that lacks the UPDATE and therefore doesn't cause an error.

IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20220525102303_ModelUpdates')
BEGIN
    DECLARE @var6 sysname;
    SELECT @var6 = [d].[name]
    FROM [sys].[default_constraints] [d]
    INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
    WHERE ([d].[parent_object_id] = OBJECT_ID(N'[Address]') AND [c].[name] = N'County');
    IF @var6 IS NOT NULL EXEC(N'ALTER TABLE [Address] DROP CONSTRAINT [' + @var6 + '];');
    ALTER TABLE [Address] ALTER COLUMN [County] smallint NOT NULL;
    ALTER TABLE [Address] ADD DEFAULT CAST(0 AS smallint) FOR [County];
END;
GO

Below is the error shown in SQL studio for this migration. A similar error appears in the pipeline log when attempting to run the migration from Devops.

Msg 207, Level 16, State 1, Line 9
Invalid column name 'County'.

EF Core version: 7
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 7.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.4

[roji]

Duplicate of #29530