How to delete multiple db entities with Nhibernate?

Question

What is the best practice for this problem? Is there any batching features built-in?

Sample code:

using (ITransaction transaction = _session.BeginTransaction())
{
   _session.Delete("FROM myObject o WHERE  o.Id = IN(1,2,...99999)");
   transaction.Commit();
}

Thanks in advance.

joshperry · Accepted Answer · 2019-07-04T16:22:28.847

20

HQL supports the IN clause, and if you use setParameterList you can even pass in a collection.

var idList = new List<int>() { 5,3,6,7 };

_session.CreateQuery("DELETE MyDataClass o WHERE o.Id IN (:idList)")
    .SetParameterList("idList", idList)
    .ExecuteUpdate();

Be aware, like mentioned by ddango in a comment, that relationship cascades specified in your objects will not be executed since running an HQL query simply translates to a DB query and does not actually load any entity objects.

edited Jul 04 '19 at 16:22

answered Dec 08 '09 at 19:17

joshperry

41,167
16
88
103

What if the idList is huge? How would you batch around it? or does nhibernate have some form of built-in batching? – Newbie Dec 08 '09 at 19:21
I'd have to setup a test using nHibernate profiler [nhprof.com] to see what actually gets emitted to the DB, but if I had to guess I would say that it would look explicitly like your hard-coded example. – joshperry Dec 08 '09 at 19:25
3

You can set a batch size in your : 100. However, based on your HQL, I would guess its just one query emitted (batches would apply if you were looping through a collection and calling Delete on them). – ddango Dec 08 '09 at 23:25

score 10 · Answer 2 · answered Jun 12 '11 at 17:38

10

I had problems getting the answer to work and I found the following query worked 100%

        Session.CreateQuery("delete Customer c where c.id in (:deleteIds)")
            .SetParameterList("deleteIds", deleteIds)
            .ExecuteUpdate();

Customer is the class name not the table name. id is lowercase and in HQL it is the Primary Key not a property name in the class (Property names are supported)

answered Jun 12 '11 at 17:38

Chris Kolenko

1,020
17
32

Yep, and the only caveat that might be worth mentioning is that cascading is not going to be applied since you're not loading the object. So if Customer had an Address row attached and it normally cascade-deletes, this isn't going to honor that – PandaWood Feb 03 '13 at 23:54

score 5 · Answer 3 · edited Jul 19 '16 at 14:19

you can Use HQL to delete multiple objects

Look for delete here - for session.delete example

HQL DELETE example (you can use IN with HQL):

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

String hqlDelete = "delete Customer c where c.name = :oldName";
// or String hqlDelete = "delete Customer where name = :oldName";
int deletedEntities = session.CreateQuery( hqlDelete )
        .SetString( "oldName", oldName )
        .ExecuteUpdate();
tx.Commit();
session.Close();

How to delete multiple db entities with Nhibernate?

3 Answers3

Linked