NHibernate ExecuteUpdate does not participate in current transaction?

nhibernate, transactions

Solution

NHibernate transactions are not the same as DB transactions; the framework has no way to know which entities or what data your `ExecuteUpdate` is affecting, and therefore does not automatically flush the session between the `SaveOrUpdate` (which may be deferred depending on the session's `FlushMode`) and the `CreateSQLQuery` (which is always immediate if you use `ExecuteUpdate`).

Generally speaking, if you're combining NHibernate logic with lower-level SQL logic (stored procedures etc.) then you'll want to use a `TransactionScope` in order to guarantee atomicity:

using (var tsc = new TransactionScope())
using (var session = sessionFactory.OpenStatelessSession())
using (var transaction = session.BeginTransaction())
{
    session.SaveOrUpdate(entity);
    session.Flush();
    session.CreateSQLQuery("EXEC foo").ExecuteUpdate();
    transaction.Commit();
    tsc.Complete();
}

(FWIW - strictly speaking, the NHibernate transaction should be unnecessary here, since you aren't really doing anything with the session after the `Flush` - but it's good practice to use an explicit NH transaction anyway in case the logic changes in the future.)

Note that I prefer to use `IStatelessSession` instances for any batch inserts/updates/etc. - if you are using regular `ISession` instances and need to actually retrieve the updates made by `ExecuteUpdate`, then you might need to use the session's `Evict` or `Clear` methods to guarantee getting the updates.

Problem

I have code that looks like this: ``` using (var session = this.sessionCreator.OpenSession()) using (var transaction = session.BeginTransaction()) { session.SaveOrUpdate(anObject); session.CreateSQLQuery(sql) .ExecuteUpdate(); transaction.Commit(); } ``` Surprisingly, the SQL query executes before `anObject` is saved. Apparently, the `ExecuteUpdate` command does not participate in the current transaction. Is there any way to get the update to enlist in the transaction?

Original source