Entity Framework Cascade delete - FOREIGN KEY constraint

Question

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)?

ocuenca · Accepted Answer · 2015-02-27 18:25:52Z

5

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.

edited Feb 27, 2015 at 18:25

answered Feb 26, 2015 at 17:08

ocuenca

39.5k12 gold badges95 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrey Over a year ago

But when I remove cascading delete it shows me, that the row have dependecy and it can not be removed... I have optional Foreign Key.

Andrey Over a year ago

So before I remove Footer or header from table I need to make null Header or Footer in all dependencies by myself?

ocuenca Over a year ago

Hello @AndreyMykhaylov, I have updated my answer, I hope that helps you now.

Collectives™ on Stack Overflow

Entity Framework Cascade delete - FOREIGN KEY constraint

1 Answer 1

Update

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related