SqlServer Migrations: Rebuild foreign keys

[claudeHasler]

I have an asp.net core / ef core project where im writing to a Microsoft SQL database.
I have relationships between several of models via foreign key constraints. I am now trying to update some primary keys, from nvarchar(50) to nvarchar(200) via generated migrations SQL script, but am constantly running into errors of this kind:

The object 'FK_Plans_Plans_ParentUid' is dependent on column 'Uid'.
Msg 4922, Level 16, State 9, Line 54
ALTER TABLE ALTER COLUMN Uid failed because one or more objects access this column.

I assume this is because of the foreign keys referencing my altered primary key.
I would expect ef core to first remove any FK constraints, then alter table, then reapply FK constraints, to avoid this conflict,but this is not happening.

Is this expected behaviour? Can i trigger this behaviour somehow? I can of course manipulate the migrations manually, but this seems error prone and very time consuming, seeing as i have quite a few FK constraints

Steps to reproduce

If this is not expected behaviour I will create a sample project I can share. My current project is sensitive and too large to share

Further technical details

EF Core version:3.1.4
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: netcoreapp3.1
Operating system: Windows 10
IDE: Visual Studio 2019

[ajcvickers]

I would expect ef core to first remove any FK constraints, then alter table, then reapply FK constraints, to avoid this conflict,but this is not happening.

This is what should be happening, so there may be a bug or something else preventing it in your case. Sample project would be great!

[claudeHasler]

https://gofile.io/d/UOaXwT
here you will find the project. Althaus.Tracker.Server is the ASP.NET Core server. I am changing the PlanDM and SerialDM UIDs from [StringLength(200)] to [StringLength(300)], creating a new migration and then generating the sql script. Thanks!

[ajcvickers]

@bricelam I am able to reproduce this. The migration generates simple AlterColumn calls for the key length, but this fails.

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<string>(
        name: "PlanUid",
        table: "PlanSerialNumbers",
        nullable: true,
        oldClrType: typeof(string),
        oldType: "nvarchar(200)",
        oldNullable: true);

    migrationBuilder.AlterColumn<string>(
        name: "ParentUid",
        table: "Plans",
        nullable: true,
        oldClrType: typeof(string),
        oldType: "nvarchar(200)",
        oldNullable: true);

    migrationBuilder.AlterColumn<string>(
        name: "Uid",
        table: "Plans",
        maxLength: 300,
        nullable: false,
        oldClrType: typeof(string),
        oldType: "nvarchar(200)",
        oldMaxLength: 200);
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<string>(
        name: "PlanUid",
        table: "PlanSerialNumbers",
        type: "nvarchar(200)",
        nullable: true,
        oldClrType: typeof(string),
        oldNullable: true);

    migrationBuilder.AlterColumn<string>(
        name: "ParentUid",
        table: "Plans",
        type: "nvarchar(200)",
        nullable: true,
        oldClrType: typeof(string),
        oldNullable: true);

    migrationBuilder.AlterColumn<string>(
        name: "Uid",
        table: "Plans",
        type: "nvarchar(200)",
        maxLength: 200,
        nullable: false,
        oldClrType: typeof(string),
        oldMaxLength: 300);
}

[bricelam]

I don't think we rebuild foreign keys--only indexes the moment:

efcore/src/EFCore.SqlServer/Migrations/SqlServerMigrationsSqlGenerator.cs

Lines 292 to 293 in 6268c39

	indexesToRebuild = GetIndexesToRebuild(column, operation).ToList();
	DropIndexes(indexesToRebuild, builder);

But yes, we should do this.

I vaguely remember talking about it before (possibly in EF6) and it was kind of dangerous at the time because we didn't always have the ON DELETE behavior, but I think we can handle this a lot better now. (e.g. only rebuild FKs if they're in the backing model) Note however, that we still don't have the ON UPDATE behavior in the model, so this would be lost.

[bricelam]

Duplicate of #12586