Tree deletion with NHibernate
- by Tigraine
Hi, I'm struggling with a little problem and starting to arrive at the conclusion it's simply not possible.
I have a Table called Group. As with most of these systems Group has a ParentGroup and a Children collection. So the Table Group looks like this:
Group
-ID (PK)
-Name
-ParentId (FK)
I did my mappings using FNH AutoMappings, but I had to override the defaults for this:
p.References(x => x.Parent)
.Column("ParentId")
.Cascade.All();
p.HasMany(x => x.Children)
.KeyColumn("ParentId")
.ForeignKeyCascadeOnDelete()
.Cascade.AllDeleteOrphan()
.Inverse();
Now, the general idea was to be able to delete a node and all of it's children to be deleted too by NH. So deleting the only root node should basically clear the whole table.
I tried first with Cascade.AllDeleteOrphan but that works only for deletion of items from the Children collection, not deletion of the parent.
Next I tried ForeignKeyCascadeOnDelete so the operation gets delegated to the Database through on delete cascade. But once I do that MSSql2008 does not allow me to create this constraint, failing with :
Introducing FOREIGN KEY constraint
'FKBA21C18E87B9D9F7' on table 'Group'
may cause cycles or multiple cascade
paths. Specify ON DELETE NO ACTION or
ON UPDATE NO ACTION, or modify other
FOREIGN KEY constraints.
Well, and that's it for me. I guess I'll just loop through the children and delete them one by one, thus doing a N+1. If anyone has a suggestion on how do that more elegantly I'd be eager to hear it.
