CODEWITHSUNDEEP
c#nhibernate
fejesjoco
fejesjoco

Reputation: 11903

In NHibernate, how to delete all elements of an associated set?

Let's say I have a class named Parent, in my hbm.xml there is a one-to-many set of the Child class. I want to delete all children of a given parent as efficiently as possible. This is what I'm doing now:

foreach (var child in parent1.Children)
    session.Delete(child);
parent1.Children.Clear();

This results in 3+ different SQL statements: a SELECT that fetches the children entities, followed by an UPDATE that sets the foreign key in all children to NULL based on the parent foreign key, followed by multiple DELETE statements, one by one.

What I would like to see is one DELETE that deletes all children, based on their parent foreign key. Is there an NHibernate way to do that? Or do I need to bite the bullet and issue a raw SQL statement?

Upvotes: 2

Views: 703

Answers (1)

Radim Köhler
Radim Köhler

Reputation: 123861

NHibernate introduced a very cool feature: 13.3. DML-style operations.

This supports two NHibernate/SQL features at one:

  1. Using HQL on top of the mapped model (we are working with our Entity/POCO model)
  2. executing the WRITE operations directly on the server (NO data download to app server)

Adjust example from documentation mentioned above:

ISession session = sessionFactory.OpenSession();
ITransaction tx = session.BeginTransaction();

String hqlDelete = "delete Children c where c.ParentId = :parentId";

int deletedEntities = s.CreateQuery( hqlDelete )
        .SetString( "parentId", parentId )
        .ExecuteUpdate();
tx.Commit();
session.Close();

Nice reading: Ayende: NHibernate – Executable DML

Upvotes: 3

Related Questions