Entity Framework Cascade delete - FOREIGN KEY constraint

c#, entity-framework, entity-framework-6

Solution

That exception is caused when you have multiple paths of cascade deletes that could end trying to delete the same row in DB. Imagine if you have `ProjectPage` with the same `Header` and `Footer`. When you try to delete that `ProjectPage`, due to the configuration of your relationships, there will be two paths trying to delete the same row in DB (one for the `Header` and another for the `Footer`).

You can avoid such ambiguous delete paths by either disabling cascading delete in one of two relationship using Fluent API or by defining some of the relationships as optional (with a nullable foreign key, but you can not configure the relationship with cascade delete).

Update

Is true, you have both FK as optionals but both relationships have been configured with cascade delete, that's why EF is throwing that exception. My recommendation is set only one relationship with cascade delete. Regarding the other relationship, I'm afraid you you have to do it manually. If you, for example, choose `Footer` to be deleted manually, then when you are going to remove a `ProjectPage` ,you have to set the `Footer` property as `null`. The problem here is you could have orphans in your DB. To avoid that, you can override the `SaveChanges` on your Context to find and delete orphans:

public override int SaveChanges()
{
  ProjectPages
    .Local
    .Where(r => r.Footer== null && r.FooterId!=default(Guid)).Select(r=>r.FooterId)
    .ToList()
    .ForEach(id => ProjectPages.Remove(ProjectPages.Find(id)));

  return base.SaveChanges();
}

Another way could be setting the `FooterId` with the `default(Guid)` value. Due to the type of your PK property (`Id`) is `Guid` and it is not Identity, you have to set that property before add a `ProjectPage` to the DB. So, setting the `FooterId` with `default(Guid)` is another way to mark that entity that you want to delete. If you choose this variant, your `SaveChanges` method could be as I show below:

 public override int SaveChanges()
 {
  ProjectPages
    .Local
    .Where(r => r.Footer!= null && r.FooterId!=default(Guid)).Select(r=>r.Footer)
    .ToList()
    .ForEach(pp=> ProjectPages.Remove(pp));

  return base.SaveChanges();
 }

Or:

 public override int SaveChanges()
 {
  ProjectPages
    .Local
    .Where(r => r.Footer!= null && r.FooterId!=default(Guid)).Select(r=>r.Footer)
    .ToList()
    .ForEach(pp=> Entry(pp).State=EntityState.Deleted);

  return base.SaveChanges();
 }

This way you can avoid call the `Find` method.

Problem

I have a problem with the following model: ``` public class ProjectPage { [Key] public Guid Id { get; set; } public Guid? HeaderId { get; set; } public ProjectPage Header { get; set; } public Guid? FooterId { get; set; } public ProjectPage Footer { get; set; } } ``` On model Creating I have this: ``` modelBuilder.Entity<ProjectPage>().HasOptional(p => p.Header).WithMany().HasForeignKey(p => p.HeaderId).WillCascadeOnDelete(true); modelBuilder.Entity<ProjectPage>().HasOptional(p => p.Footer).WithMany().HasForeignKey(p => p.FooterId).WillCascadeOnDelete(true); ``` But I can't update database. I've got the following error in the Package Manager console: Introducing FOREIGN KEY constraint 'FK_dbo.ProjectPages_dbo.ProjectPages_FooterId' on table 'ProjectPages' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Can somebody explain how to remove the Project Page (which can be Footer or Header in another Project Page)?

Original source