Why NHibernate deletes referenced objects one by one, not using foreign key?

Question

I have simple Parent-Child relationship, Parent has many Child objects, the relation is unidirectional:

public class Parent
{
    public virtual int Id { get; protected set; }
    public virtual string Name { get; set; }
    public virtual IList<Child> Children { get; set; }
}

public class Child
{
    public virtual int Id { get; protected set; }
    public virtual string Name { get; set; }
}

Mapping for the relation sets cascade to AllDeleteOrphan to remove Child objects not referenced by Parent any more:

HasMany(x => x.Children).Cascade.AllDeleteOrphan();

And now I'm clearing the list of Child objects:

var parent = session.Get<Parent>(1);
parent.Children.Clear();
session.Update(parent);

NHibernate deletes the Child object as expected, but it do this by sending separate DELETE query for each Child from the collection: DELETE FROM Child WHERE Id = ... - that can mean really LOT of queries.

Anyway, it can be easily done using single query like DELETE FROM Child WHERE ParentId = 1. Why NHibernate is not using the parent foreign key to clear the collection? It seems that it knows everything to prepare such query (which foreign key, what value etc.)?

score 2 · Answer 1 · answered Jul 30 '11 at 03:52

2

NHibernate can and will do that in certain cases.

It's all explained here.

answered Jul 30 '11 at 03:52

Diego Mijelshon

52,548
16
116
154

"*NHibernate isn't completely stupid, so it knows not to do that in the case of an newly-empty collection (if you called list.Clear(), for example). In this case, NHibernate will issue a single DELETE and we are done!*" - well, this is the case in my question and I have one by one deletes. My relation isn't inverse though, but how could it be inverse when it's unidirectional? – NOtherDev Jul 30 '11 at 09:44
I'm trying to reproduce one-shot delete with Inverse, but can't make it work. I've added `Parent Parent { get; set; }` in `Child`, mapped as `References(x => x.Parent);` and added `.Inverse()` to `Children` collection in `Parent`. I'm setting `child.Parent` as well as adding `child` to `parent.Children` collection. Still getting one by one DELETE. – NOtherDev Jul 30 '11 at 16:30
@A.: that's interesting. I couldn't get it to work either. Wanna ask in the nhusers group? – Diego Mijelshon Jul 30 '11 at 17:05
http://groups.google.com/group/nhusers/browse_thread/thread/9bceef2a0e8b2797 - not a big response. – NOtherDev Aug 01 '11 at 20:17

score 0 · Answer 2 · answered May 15 '12 at 12:43

NHibernate issues a NHibernate: UPDATE "Child" SET Parent_id = null WHERE Parent_id = @p0;@p0 = 1 before deleting the childs. Since the has many is not inverse the parent has to unset the association and then cascades the delete to the child because the chuild may have other cascades set which have to be deleted.

However even with inverse one shot deletes do not work. NH only seems to delete at once with element/component collections.

One reason can be childs could have user defined sqldeletes, additional cascaded properties and so on. The easiest way for NH is to simply cascade the delete to each child seperatly.

Why NHibernate deletes referenced objects one by one, not using foreign key?

2 Answers2