How to set cascade on self-referencing relations?

Question

I have default scenario where you have Category itself, RootCategory and ChildCategories. How can I specify my fluent model builder to cascade all child-categories on delete?

Model

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual Category RootCategory { get; set; }
    public virtual ICollection<Category> ChildCategories { get; set; }
    public virtual ICollection<Item> Items { get; set; }
}

What I have tried

I have tried to use fluent model builder but this one gives error when I try to update database.

Introducing FOREIGN KEY constraint 'FK_dbo.Categories_dbo.Categories_RootCategory_Id' on table 'Categories' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Category>().HasOptional(x => x.RootCategory).WithMany(x => x.ChildCategories).WillCascadeOnDelete(true);
}

score 4 · Answer 1 · answered Jan 09 '14 at 05:26

I had the same problem. Regarding fluent API configuraion, I don't know if you can do it there. What you can do is set the WillCascadeOnDelete to false and just delete the ChildCategories yourself.

private void DeleteChildCategories(Category category) 
{
    foreach (Category subCategory in category.ChildCategories.ToList())
        {
            if (subCategory.SubCategories.Count() > 0)
            {
                DeleteChildCategories(subCategory);
            }
            else
            {
                _db.Category.Remove(subCategory);
            }
        }
    _db.Category.Remove(category);
}

You can then call DeleteChildCategories when deleting a Category inside your controller action.

DeleteChildCategories(Category);
_db.SaveChanges();

Hope this helps.

Mark

I'm having a similar problem as OP, and this is likely the solution I will end up using. — Charles W, Apr 02 '14 at 16:19
According to [this](http://stackoverflow.com/questions/528529/self-referencing-constraint-in-ms-sql) you can't use cascade delete on a self referencing table, neither with EF nor pure T-SQL. I had the same problem and also ended up with an inefficient recursive delete in code. — marce, Apr 18 '14 at 18:14

Andrey Gubal · Answer 2 · 2013-09-09T17:27:15.557

0

It seems you are using the same model for categories and subcategories. That is ok, but when you're trying to activate cascade deleting you receives endless loop, becase it doesn't understand what to delete.

I think, you need parentCategoryID property in your Model. In that case you will have strong relations and cascade deleting should work.

EDITED: I mean that if you have parentCategoryID you may try to modify your code as follows:

modelBuilder.Entity<Category>().HasOptional(x => x.RootCategory).WithMany(x => x.ChildCategories).HasForeignKey(x => x.parentCategoryID).WillCascadeOnDelete(true);

edited Sep 09 '13 at 17:27

answered Sep 09 '13 at 16:22

Andrey Gubal

3,481
2
18
21

How is having Parent_Id exposed is going to solve the problem? It already has Parent_Id in database that EF uses. – Stan Sep 09 '13 at 16:55

score 0 · Answer 3 · answered Sep 13 '13 at 09:05

I remember I had a similar problem. I hope this helps:

I had a Comment entity which could have replies (other Comments), creating an "unlimited tree" of Comments.

Each Comment had its own CommentID (Primary Key) as well as a ParentID (Foreign Key, no CASCADE). Then I had a DB trigger on my Comment Table:

CREATE TRIGGER [dbo].[Trigger_DeleteChildComments]
ON [dbo].[Comment]
FOR DELETE
AS
BEGIN
    SET NoCount ON
    DELETE FROM Comment WHERE ParentID IN (SELECT CommentID FROM DELETED)
END

(Or: "Upon deleting a Comment c, delete all other comments which have c as a parent.)

Note that this only works because my business logic doesn't allow "cycles" on this Comment graph, making sure that the graph stays as a tree. Thus, I believe that my trigger does not cause endless loops.

How to set cascade on self-referencing relations?

Model

What I have tried

3 Answers3