Deleting Entities in NHibernate

This post was motivated by a conversation with Salvador Gascon (@salvadorgascon). Might even go to the pitfalls section… Anyway, the problem is: how many ways there are to delete an entity in NHibernate? And what are the advantages/disadvantages of each?

First, we have the classic one, using ISession.Delete(), which all NHibernate developers should be familiar with:

   1: var product = session.Get<Product>(id);

   2:  

   3: session.Delete(product);

   4:  

   5: session.Flush();

Using ISession.Get<T>(), you force an entity – and all of its non-lazy associations and properties – to be loaded from the database. When you delete it, all associations marked for cascade delete will also be deleted.

If you don’t want to load the entity from the database in order to delete it, you can use ISession.Load<T>(), for entities marked as lazy, this will return a proxy instead:

   1: var product = session.Load<Product>(id);

   2:  

   3: session.Delete(product);

   4:  

   5: session.Flush();

Do note, however, that when Delete is called, the entity will still be loaded, as well as all its non-lazy associations. Flushing is required.

For better performance, we can use Executable HQL:

   1: session.CreateQuery("delete from Product p where p.ProductId = :id")

   2: .SetParameter("id", id)

   3: .ExecuteUpdate();

This will translate to a single plain old SQL DELETE command, so no entity will be loaded into the session. This has a big disadvantage, though: no cascade deletes will occur. This means that you may get exceptions due to foreign key violations. A good thing is that it returns the number of deleted records.

Another way to delete is through an overload of the Delete method:

   1: session.Delete(String.Format("from Product p where p.ProductId = {0}", id));

   2:  

   3: session.Flush();

In this case, we are providing the id in the query string, which is not good, mainly for performance reasons – with NHibernate HQL, SQL injections are much more difficult to do. A better alternative is to use positional parameters:

   1: session.Delete("from Product p where p.ProductId = ?", id, NHibernateUtil.Int32);

In this case, together with the id, we also need to supply its type. Does not cascade nor return the number of deleted records.

Or, if you want to delete by Id dynamically, check out my extension method here.

Finally, if you are brave enough, you can check my strongly-typed hackish solution here.