NHibernate and deleting a group of objects

If you do not mind to issue a delete for each object, you can delete multiple objects using an overload version of the ISession.Delete() Method, here is an example.

uow.Session.Delete("select A from ActionSpawned A where A.ParentAction = :act", ParentAction, NHibernateUtil.Entity(typeof(Action)));

The code is quite concise, you specify the query to select a series of object, and pass it to the Delete method of the ISession, along with the parameters of the query. The result is that NHibernate executes the query, loads all objects and deletes each one.

If you have to delete a really great amount of objects, you can resort to use direct SQL to obtain greater performance, since all objects are deleted in a single roundtrip

Object result = theSession.CreateSQLQuery("DELETE from SchedulerAction where acti_parent = :act") .AddScalar("count", NHibernateUtil.Int32) .SetParameter("act", act1.Id) .UniqueResult();

With this query you can issue a very efficient DELETE instruction, but remember that you have to specify the real table and columns names, so you lose the advantage to abstract the model from the database. The above query returns null, because the delete instruction does not select any data, if you want to know the number of deleted row you can use

Object result = theSession.CreateSQLQuery("DELETE from SchedulerAction where acti_parent = :act ; select @@ROWCOUNT count;") .AddScalar("count", NHibernateUtil.Int32) .SetParameter("act", act1.Id) .UniqueResult();



Published by

Ricci Gian Maria

.Net programmer, User group and community enthusiast, programmer - aspiring architect - and guitar player :). Visual Studio ALM MVP

4 thoughts on “NHibernate and deleting a group of objects”

  1. Great…it is just what I need.

    ISession.Delete will load the object first, whilst CreateSQLQuery will delete the data directly.

    We can use windbg to view the memeory to verify these 2 situations.

  2. I tried to execute a query that ends with “select @@ROWCOUNT count;” and I got the following exception: “System.Data.OracleClient.OracleException: ORA-00911″- Invalid character.

    I use NHibernate.Dialect.OracleDialect.

  3. This is because @@ROWCOUNT is specific of Sql Server, you should use whatever equivalent in oracle to return the number of record affected.

    Remember that when you use CreateSqlQuery you are actually losing many of the advantages of an orm, and you become “database dependant”


Comments are closed.