0

Here's my problem: I have a set of ids. These are the ids of a collection of root entities. Now I want to delete all these root entities, efficiently.

I can't do a WHERE Id IN (1, 2, 3) type of clause, as I'm deleting root entities with children.

I'm wondering if it's possible to avoid retrieving all root entities and deleting them one by one. The problem with that approach isn't so much the SELECT, it's have lots of separate DELETE statement.

Is it possible for NHibernate to batch this, including the delete of all the children? Extra complexity: children can have their own children.

So I'd want NHibernate to first delete the 'bottom-most' children with an IN-clause (maybe multiples ones), then the children with an IN-clause, and then finally the root entities with an IN-clause.

If this isn't possible, what's a good approach to delete multiple root entities efficiently with NHibernate?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Peter
  • 13,733
  • 11
  • 75
  • 122
  • how should NHibernate know which are the Grandchildren without loading the children into memory? Also NHibernate supports hooks which are also used for auditing, it would render them useless if some deletes would take different code paths. The easiest way would be to run a custom sql with the deletes you want – Firo May 28 '13 at 12:53

2 Answers2

2

UPDATE This is not valid in your case where you had a list of ids (didn't read your question properly).

First, batch deletes of the "parents" can be made setting adonet.batch_size, http://nhibernate.info/doc/nh/en/index.html#performance-batch-updates.

END UPDATE

Secondly, to avoid reading the children (and generate deletes for these), you can set the cascading delete to happen on db level by setting ON CASCADE DELETE as a foreign key constraints.

Eg (note the on-delete attr)

<set name="TheCollection" inverse="true" cascade="save-update">
 <key name="theKey" on-delete="cascade"/>
 <one-to-many class="TheType"/>
</set>

Note that

  • this only works on inverse (bidirectional) relationships.
  • your db schema needs to be updated with this fk constraint
  • don't use cascade="all" or similar where deletes are included
Owen Pauling
  • 11,349
  • 20
  • 53
  • 64
Roger
  • 1,944
  • 1
  • 11
  • 17
0

Might help using the Session.Delete statement passing an array of IDs? I believe this will delete the whole map for those objects matching the IDs in the array.

Session.Delete("from MyTable t where t.ID in :IDs", IDs, NHibernate.NHibernateUtil.Guid);

where IDs is an array of Guids in this case, but could be any object.

Sergio A.
  • 403
  • 2
  • 9
  • I'm under the impression it doesn't delete the whole map, giving me an error on a foreign key constraint. – Peter May 29 '13 at 06:41
  • Have you tried adding cascade="save-update,delete"? That should get rid of the linked entities.. – Sergio A. May 29 '13 at 14:52