Tree deletion with NHibernate

Question

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.

Answer 1

I did it like this and it worked.

    public class Node_Map : ClassMap<Node>
    {
        public Node_Map()
        {
            References(x => x.Parent, "IdCmsNodeParent");
            HasMany(x => x.Childs).AsBag()
                                  .Inverse()
                                  .Cascade.Delete()
                                  .KeyColumn("IdNodeParent");
        }
    }

But still, if NHibernate deletes it, you will get N+1. Only a right database constraint can delete it in one go.

If you want to do it efficiently in Sql server 2008 you should use recursive CTE.

Check:

http://msdn.microsoft.com/en-us/library/ms186243.aspx
http://explainextended.com/2010/03/03/sql-server-deleting-with-self-referential-foreign-key/