Migration dropping wrong Foreign Key on entities with same name but differemt schemas

[mitsakosgr]

When two Entities with the same name but different schemas reference a shared entity and they are applied in separate migrations, the Down method of the second migration deletes the Foreign Key Constraint set by the first.

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropForeignKey(
        name: "FK_OtherEntity_BaseReference_BaseReferenceId",
        schema: "first",
        table: "OtherEntity");

    migrationBuilder.DropTable(
        name: "OtherEntity",
        schema: "second");
}

EF Core version: 9.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer, Microsoft.EntityFrameworkCore.Sqlite
Target framework: .NET 9.0
Operating system: Windows 11
IDE: Visual Studio 2022 and VSCode

The same happens with npgsql: npgsql/efcore.pg#3057

Full example https://github.com/mitsakosgr/efcorewrongmigration

For example the following Entities and DbContext:

public class BaseReference
{
    [Key]
    public int Id { get; set; }

    public string Something { get; set; } = "";
}

namespace First
{
    public class OtherEntity
    {
        [Key]
        public int Id { get; set; }

        [ForeignKey(nameof(BaseReference))]
        public int BaseReferenceId { get; set; }
        public virtual BaseReference? BaseReference { get; set; }
    }
}

public class BloggingContext : DbContext
{
    public string DbPath { get; }

    public BloggingContext()
    {
        var folder = Environment.SpecialFolder.LocalApplicationData;
        var path = Environment.GetFolderPath(folder);
        DbPath = System.IO.Path.Join(path, "blogging.db");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlite($"Data Source={DbPath}");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<First.OtherEntity>()
            .ToTable("OtherEntity", "first")
            .HasOne(i => i.BaseReference)
            .WithMany()
            .IsRequired()
            .OnDelete(DeleteBehavior.Restrict);
    }
}

generates the following migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.EnsureSchema(
        name: "first");

    migrationBuilder.CreateTable(
        name: "BaseReference",
        columns: table => new
        {
            Id = table.Column<int>(type: "INTEGER", nullable: false)
                .Annotation("Sqlite:Autoincrement", true),
            Something = table.Column<string>(type: "TEXT", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_BaseReference", x => x.Id);
        });

    migrationBuilder.CreateTable(
        name: "OtherEntity",
        schema: "first",
        columns: table => new
        {
            Id = table.Column<int>(type: "INTEGER", nullable: false)
                .Annotation("Sqlite:Autoincrement", true),
            BaseReferenceId = table.Column<int>(type: "INTEGER", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_OtherEntity", x => x.Id);
            table.ForeignKey(
                name: "FK_OtherEntity_BaseReference_BaseReferenceId",
                column: x => x.BaseReferenceId,
                principalTable: "BaseReference",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
        });

    migrationBuilder.CreateIndex(
        name: "IX_OtherEntity_BaseReferenceId",
        schema: "first",
        table: "OtherEntity",
        column: "BaseReferenceId");
}

/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(
        name: "OtherEntity",
        schema: "first");

    migrationBuilder.DropTable(
        name: "BaseReference");
}

Adding a second entity with the same name:

namespace Second
{
    public class OtherEntity
    {
        [Key]
        public int Id { get; set; }

        [ForeignKey(nameof(BaseReference))]
        public int BaseReferenceId { get; set; }
        public virtual BaseReference? BaseReference { get; set; }
    }
}

modelBuilder.Entity<Second.OtherEntity>()
      .ToTable("OtherEntity", "second")
      .HasOne(i => i.BaseReference)
      .WithMany()
      .IsRequired()
      .OnDelete(DeleteBehavior.Restrict);

generates the following migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.EnsureSchema(
        name: "second");

    migrationBuilder.CreateTable(
        name: "OtherEntity",
        schema: "second",
        columns: table => new
        {
            Id = table.Column<int>(type: "INTEGER", nullable: false)
                .Annotation("Sqlite:Autoincrement", true),
            BaseReferenceId = table.Column<int>(type: "INTEGER", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_OtherEntity", x => x.Id);
            table.ForeignKey(
                name: "FK_OtherEntity_BaseReference_BaseReferenceId",
                column: x => x.BaseReferenceId,
                principalTable: "BaseReference",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);
        });

    migrationBuilder.CreateIndex(
        name: "IX_OtherEntity_BaseReferenceId1",
        schema: "second",
        table: "OtherEntity",
        column: "BaseReferenceId");
}

/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropForeignKey(
        name: "FK_OtherEntity_BaseReference_BaseReferenceId",
        schema: "first",
        table: "OtherEntity");

    migrationBuilder.DropTable(
        name: "OtherEntity",
        schema: "second");
}

You can see at the second Down it drops the FK from the first schema!

[maumar]

I'm able to repro on main. Not a regression, same thing happens on 8