Migration dropping wrong objects on down

[RichardBurns1982]

We have two objects with the same name in different namespaces and schemas in the database:

namespace ConsoleAppEfSandbox.Entities.Dts;

public class DataSet
{
    public int Id { get; set; }
    public int? CreatedByUserId { get; set; }
    public User? CreatedByUser { get; set; }
}

namespace ConsoleAppEfSandbox.Entities.Organizations;

public class DataSet
{
    public int Id { get; set; }
    public int? CreatedByUserId { get; set; }
    public User? CreatedByUser { get; set; }
}

The ConsoleAppEfSandbox.Entities.Organizations.DataSet object was added after the ConsoleAppEfSandbox.Entities.Dts.DataSet object. Both objects have the same relationship to a user and so EF generates the same foreign key name, which is fine as they are in different schemas.

The first migration creates the users table and dts.data_sets objects:

public partial class Initial : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.EnsureSchema(
            name: "dts");

        migrationBuilder.CreateTable(
            name: "User",
            columns: table => new
            {
                Id = table.Column<int>(type: "integer", nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_User", x => x.Id);
            });

        migrationBuilder.CreateTable(
            name: "data_sets",
            schema: "dts",
            columns: table => new
            {
                Id = table.Column<int>(type: "integer", nullable: false)
                    .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                CreatedByUserId = table.Column<int>(type: "integer", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_data_sets", x => x.Id);
                table.ForeignKey(
                    name: "FK_data_sets_User_CreatedByUserId",
                    column: x => x.CreatedByUserId,
                    principalTable: "User",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Restrict);
            });

        migrationBuilder.CreateIndex(
            name: "IX_data_sets_CreatedByUserId",
            schema: "dts",
            table: "data_sets",
            column: "CreatedByUserId");
    }

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

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

The second migration, once the organizations.data_sets object is configured generates an incorrect migration. As the below shows the Up is fine, but the down tries to drop the foreign key from the wrong schema:

namespace ConsoleAppEfSandbox.Migrations
{
    /// <inheritdoc />
    public partial class AddOrgDataSets : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.EnsureSchema(
                name: "organizations");

            migrationBuilder.CreateTable(
                name: "data_sets",
                schema: "organizations",
                columns: table => new
                {
                    Id = table.Column<int>(type: "integer", nullable: false)
                        .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                    CreatedByUserId = table.Column<int>(type: "integer", nullable: true)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_data_sets", x => x.Id);
                    table.ForeignKey(
                        name: "FK_data_sets_User_CreatedByUserId",
                        column: x => x.CreatedByUserId,
                        principalTable: "User",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateIndex(
                name: "IX_data_sets_CreatedByUserId1",
                schema: "organizations",
                table: "data_sets",
                column: "CreatedByUserId");
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(
                name: "FK_data_sets_User_CreatedByUserId",
                schema: "dts",
                table: "data_sets");

            migrationBuilder.DropTable(
                name: "data_sets",
                schema: "organizations");
        }
    }
}

As you can see, on the down it tries to drop FK_data_sets_User_CreatedByUserId from dts schema and not organizations as the up has created.

I have uploaded a sample console app which demonstrates this: https://github.com/RichardBurns1982/npgsql-ef-migration-issues-1

[roji]

@RichardBurns1982 thanks for filing. This looks like it wouldn't be a PG-specific bug - if it's possible for you to try this out on SQL Server/SQLite, that would be really helpful (if so, then it belongs on the main EF repo and not here). Otherwise, it probably will take me a while before I can investigate.

[mitsakosgr]

@roji I just got the same behavior on SQL Server!

I have 3 Entities with the same name "Assessment" in different namespaces and schemas that have some common names for columns

• ISRA - isra
• TRA - tra
• VendorAssessment - vendor

I just created the migration for VendorAssessment and at the Up I got

migrationBuilder.CreateTable(
name: "Assessments",
schema: "vendor",
columns: table => new
{
    Id = table.Column<int>(type: "int", nullable: false)
        .Annotation("SqlServer:Identity", "1, 1"),
    AssessmentTypeId = table.Column<int>(type: "int", nullable: false),
    PartitionId = table.Column<int>(type: "int", nullable: true),
    Status = table.Column<int>(type: "int", nullable: false),
    IncludeProviderRiskAssessment = table.Column<bool>(type: "bit", nullable: false),
    AssociateId = table.Column<int>(type: "int", nullable: true),
    MasterPlanId = table.Column<int>(type: "int", nullable: false)
},
constraints: table =>
{
    table.PrimaryKey("PK_Assessments", x => x.Id);
    table.ForeignKey(
        name: "FK_Assessments_AssessmentTypes_AssessmentTypeId",
        column: x => x.AssessmentTypeId,
        principalSchema: "vendor",
        principalTable: "AssessmentTypes",
        principalColumn: "Id",
        onDelete: ReferentialAction.Restrict);
    table.ForeignKey(
        name: "FK_Assessments_Associates_AssociateId",
        column: x => x.AssociateId,
        principalSchema: "bc",
        principalTable: "Associates",
        principalColumn: "Id",
        onDelete: ReferentialAction.Restrict);
    table.ForeignKey(
        name: "FK_Assessments_MasterPlans_MasterPlanId",
        column: x => x.MasterPlanId,
        principalTable: "MasterPlans",
        principalColumn: "Id",
        onDelete: ReferentialAction.Restrict);
    table.ForeignKey(
        name: "FK_Assessments_Partitions_PartitionId",
        column: x => x.PartitionId,
        principalSchema: "bc",
        principalTable: "Partitions",
        principalColumn: "Id",
        onDelete: ReferentialAction.Restrict);
});

while at the Down I got

migrationBuilder.DropForeignKey(
    name: "FK_Assessments_MasterPlans_MasterPlanId",
    schema: "isra",
    table: "Assessments");

migrationBuilder.DropForeignKey(
    name: "FK_Assessments_Partitions_PartitionId",
    schema: "tra",
    table: "Assessments");

You can see that the schemas for the down are completely different.

The same happened for the migration of TRA.Assessment, just didn't notice because I didn't revert the migration.

If you want I can create a small project and try to replicate exactly that.

[mitsakosgr]

I opened an issue at the EF repo for this

dotnet/efcore#35358

[roji]

Closing here as this seems to be an EF-side thing.

[roji]

Duplicate of dotnet/efcore#35358