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);
}

Answer 1

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