4

I have an entity:

public class Section : SortableEntity
{
    private ICollection<Section> _sections;

    public ICollection<Section> Sections
    {
        get
        {
            return _sections ?? (_sections = new HashSet<Section>());
        }

        set
        {
            _sections = value;
        }
    }

    public string Title { get; set; }

    public string Description { get; set; }

    public Section ParentSection { get; set; }

    public int? ParentSectionId { get; set; }
}

And on model creating I have a configuration:

modelBuilder.Entity<Section>().HasOptional(x => x.ParentSection).WithMany(p => p.Sections).HasForeignKey(d => d.ParentSectionId);

I'm trying to make a cascade delete and I'm getting a following error: "The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_dbo.Section_dbo.Section_ParentSectionId".

How can I configure cascade delete on self-referencing entity?

Algirdas
  • 1,065
  • 1
  • 9
  • 21
  • 5
    I don't think you can do it in Entity Framework, as you can't use CASCADE DELETE on self referencing table in SQL SERVER... But I may be wrong. – Raphaël Althaus Mar 27 '14 at 07:59

1 Answers1

5

If you Google your question you'll see a gazillion other people have the same issue, the reason is because SQL Server can not handle cascade deletes on self referencing entities and I've found no solution within entity framework that does it simply by setting some property. The only way I know to emulate a cascade delete on self referencing entities using code first is to write a recursive method that iterates through the children as you collect primary keys, foreign keys, and level of recursion information. Once this list is built, loop through it ordered by level of recursion in descending order, in each iteration you get all records for that level of recursion, loop though that collection and delete them one at a time. I did this with a stored procedure that returned this list using a recursive common table expression. I hope this helps.

user1607685
  • 87
  • 1
  • 2