CODEWITHSUNDEEP
nhibernate
kubal5003
kubal5003

Reputation: 7254

NHibernate performing a delete with Cascade.All causes N+1 deletes

I have two entities Password and PasswordHash. Password has a collection of PasswordHashes (those are hashes with different masks).

When I delete a Password I want all associated PasswordHash to be deleted as well.

I did the mapping like this:

mapping.HasMany(x => x.PasswordHashes)
            .Cascade
            .All()
            .Inverse()
            .ReferencedBy(x => x.Password);

I expected the two queries to be generated:

DELETE FROM "PasswordHash" WHERE "IdPassword" = :p0

and obviously

DELETE FROM "Password" WHERE "IdPassword" = :p0

Instead NHibernate generated N+1 queries like this:

DELETE FROM "PasswordHash" WHERE "IdPasswordHash" = :p0 (N)

and

DELETE FROM "Password" WHERE "IdPassword" = :p0 (1)

How can I change this behaviour to be more optimal?

EDIT: Code that deletes:

session.Delete(password);
session.Flush();

Upvotes: 2

Views: 252

Answers (1)

mickfold
mickfold

Reputation: 2003

From my research, in the current release of NHibernate (3.3), it looks like it is not possible to delete a child collection with a single DELETE statement using actions such as password.hashes.Clear() or session.Delete(password) on your domain objects. The NHibernate documentation refers to one shot deletes but I haven't been able to get this work with any combination of inverse and cascade for either unidirectional or bidirectional one to many associations.

One alternative is to use on-delete="cascade" which disables the cascade functionality in NHibernate and leaves it up to the database to enforce the cascade deletion. Further information can be found in this nh users google group discussion.

The other option as mentioned by Rippo in the comment section is to use HQL. You would need two statements so it would be best to wrap them in a transaction, i.e.

using (var transaction = session.BeginTransaction()
{
   session.Query("delete from PasswordHash h where h.Password = :password")
                   .SetParameter("password", password)
                   .ExecuteUpdate();
   session.Delete(password);
   transaction.Commit();
}

Upvotes: 2

Related Questions