How to bulk delete with nhibernate?

Question

Hi,

How can I delete items with nhibernate without first pulling all the objects in memory?

Is this possible or do I have to use raw sql?

Answer 1

You can use HQL:

session.ExecuteUpdate("delete from Foo foo where ...");

HTH,
Kent

Answer 2

Use the ExecuteUpdate method. The code below will commit bulk deletion in batches. This works in NHibernate 2.1.0. (Not sure about previous versions)

        foreach (List<int> batch in GetBatches(records, _batchSize))
        {
            using (ITransaction transaction = _session.BeginTransaction())
            {
                _session.CreateQuery(String.Format("DELETE  FROM {0} WHERE Id IN (:idsList)", _domainObject.Name))
                        .SetParameterList("idsList", batch.ToArray())
                        .ExecuteUpdate();

                transaction.Commit();
            }
        }