CODEWITHSUNDEEP
c#entity-frameworkef-code-first
Algirdas
Algirdas

Reputation: 1115

Entity Framework 6 Code-First cascade delete on self referencing entity

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?

Upvotes: 4

Views: 1549

Answers (1)

user1607685
user1607685

Reputation: 87

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.

Upvotes: 5

Related Questions