Search code examples
castle-activerecord

Optimize delete query generated by Castle ActiveRecord


Lets say I have Id (primary key) list that I want to delete (e.g 1, 2, 3, 4). Using this query :

Console.WriteLine ("DELETE DATA :");
ActiveRecordMediator<PostgrePerson>.DeleteAll ("Id IN (1, 2, 3, 4)");

I expect the console output is :

DELETE DATA :
NHibernate: DELETE FROM Person WHERE Id IN (1, 2, 3, 4)

but, the actual console output is (I use showsql option) :

DELETE DATA :
NHibernate: select postgreper0_.Id as Id5_, postgreper0_.Name as Name5_, postgreper0_.Age as Age5_, postgreper0_.Address as Address5_ from Person postgreper0_ w
here postgreper0_.Id in (1 , 2 , 3 , 4)
NHibernate: DELETE FROM Person WHERE Id = :p0;:p0 = 1
NHibernate: DELETE FROM Person WHERE Id = :p0;:p0 = 2
NHibernate: DELETE FROM Person WHERE Id = :p0;:p0 = 3
NHibernate: DELETE FROM Person WHERE Id = :p0;:p0 = 4

What should I do to make Castle ActiveRecord generate the expected (optimized) query?

Update This is my implementation based on accepted answer.

int[] idList = GetIdList ();
ActiveRecordMediator<PostgrePerson>.Execute ((session, obj) => {
    string hql = "DELETE PostgrePerson WHERE Id IN (:idList)";
    return session.CreateQuery (hql)
        .SetParameterList ("idList", idList)
        .ExecuteUpdate ();
}, null); 

Solution

  • Use the Execute callback method and run a DML-style HQL DELETE on the NHibernate ISession.