Why does my Required property not being cascade deleted in Entity Framework Core?

Question

Why does my Required property not being cascade deleted in Entity Framework Core?

c# entity-framework-core npgsql postgresql

Question

I have the following class with a required property, and am running into issues deleting dependent properties via cascade delete.

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

     [Required]
     public ZipCode ZipCode { get; private set; }
}


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

    [Required]
    public string Zip { get; private set; }
}

If I try and load the city to delete it using the following code:

City toDelete = db.CitySet.Where(c => c.Id == myCityReference.Id).FirstOrDefault();
db.CitySet.Remove(toDelete);
db.SaveChanges();

I get a an Microsoft.EntityFrameworkCore.DbUpdateException, whose inner exception is a Npgsql.PostgresException: 23503: Update or delete on table "..." violates foreign key constraint..

However, if I add an include statement to the above, that is:

City toDelete = db.CitySet.Include(c => c.ZipCode)
    .Where(c => c.Id == myCityReference.Id).FirstOrDefault();

Then the delete successfully removes both the city and the zip code. However, according to the EFCore docs, a Required relationship will be set to Cascade delete functionality. Why is this not happening? I am using Entity Framework Core v1.1.1 and Npgsql v3.2.2.

ArKi

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 3/28/2017 6:41:58 PM

Here is the generated migration from your model:

migrationBuilder.CreateTable(
    name: "ZipCodes",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
        Zip = table.Column<string>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_ZipCodes", x => x.Id);
    });

migrationBuilder.CreateTable(
    name: "Cities",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
        ZipCodeId = table.Column<int>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Cities", x => x.Id);
        table.ForeignKey(
            name: "FK_Cities_ZipCodes_ZipCodeId",
            column: x => x.ZipCodeId,
            principalTable: "ZipCodes",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    });

migrationBuilder.CreateIndex(
    name: "IX_Cities_ZipCodeId",
    table: "Cities",
    column: "ZipCodeId");

(it's for Sql Server, but except some specific identity column annotations, it should be the same for other database types)

As you can see, the FK is created with cascade delete turned on. However, the FK column is in City table and is referencing the ZipCode table. Which means the ZipCode is the principal and City is the dependent. So deleting a ZipCode will cascade delete the City, not the vice versa.

Also there is no unique constraint/index on ZipCodeId column, which indicates that EF Core assumes one-to-many relationship (one ZipCode to many City).

If you want it to cascade in the opposite direction, then you need to put the FK column in the ZipCode table by using the following fluent configuration:

modelBuilder.Entity<City>()
    .HasOne(e => e.ZipCode)
    .WithOne()
    .HasForeignKey<ZipCode>("CityId")
    .OnDelete(DeleteBehavior.Cascade);

Now the migration looks like this:

migrationBuilder.CreateTable(
    name: "Cities",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Cities", x => x.Id);
    });

migrationBuilder.CreateTable(
    name: "ZipCodes",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
        CityId = table.Column<int>(nullable: true),
        Zip = table.Column<string>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_ZipCodes", x => x.Id);
        table.ForeignKey(
            name: "FK_ZipCodes_Cities_CityId",
            column: x => x.CityId,
            principalTable: "Cities",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
    });

migrationBuilder.CreateIndex(
    name: "IX_ZipCodes_CityId",
    table: "ZipCodes",
    column: "CityId",
    unique: true);

i.e. the cascade is from City to ZipCode and the relationship is one-to-one (note the unique index).

Now, I'm not sure what is the real intent here. But for sure database cascade delete is directional - from referenced table to referencing table.

Why does my Required property not being cascade deleted in Entity Framework Core?

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related