How can one delete NHibernate objects using a criteria?

Question

Dear ladies and sirs.

This must be a simple question. Given a criteria, how one deletes the entities satisfying the criteria?

The rationale:

HQL and NH criteria are NHibernate specific constructs and as such they are server side DAL implementation details. I do not want them to "leak" to the client side. So, our client side provides LINQ expressions for the server to process. Up until now the requests where select requests and LINQ to NHibernate dealed with them just fine.

However, there is a need to implement batch delete operation now. As usual, the client side provides a LINQ expression and the server is to delete entities satisfying the expression. Unfortunately, LINQ to NHibernate is of no help here. The most it can do is translate the given LINQ expression to NHibernate criteria.

Anyway, this is the story. I wish to stress that the client side is unaware of NHibernate at all and I like it to stay this way.

Thanks.

P.S.

I am using NH 2.1

Answer 1

In your repository/dao/persistencemanager/whatever class:

public IEnumerable<T> FindAll(DetachedCriteria criteria)

        {

            return criteria.GetExecutableCriteria(Session).List<T>();

        }

and then

public void Delete(DetachedCriteria criteria)

        {

            foreach (T entity in FindAll(criteria))

            {

                Delete(entity);

            }

        }

See Davy Brion's post Data Access with NHibernate.

Edit:

As far as I know, if you want to use Criteria you need to load the objects and iterate over them to delete them. Alternatively use HQL or pass in the SQL to the session.

Answer 2

You may use the criteria to select the IDs of your elements, join them in a string and use HQL to delete them?

Something like:

public void Delete(ICriteria criteria, string keyName, string tableName)
{
    criteria.setProjection(Projections.Attribute(keyName));
    IList<int> itemIds = criteria.List<int>();

    string collection = string.Join(",", Array.ConvertAll<int, string>(itemIds, Convert.ToString));

    Session.HQL(string.Format("delete from {0} where {1} in ({2})", tableName, keyName, collection);
}

This code was not tested or compiled (in particular I'm not sure of the HQL section), but I think that you got the idea: we don't fetch the whole objects thanks to the projection, but only the indices.

Answer 3

Simply put, up until 2.1.2 you cannot.

However, if you can translate the LINQ expression to HQL (or the ICriteria to HQL) then you can use the overloaded ISession.Delete() method which uses a passed HQL string.