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.
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.