NHibernate ExecuteUpdate is not involved in the current transaction?

Question

I have a 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 is executed before anObject . Apparently, the ExecuteUpdate command ExecuteUpdate not involved in the current transaction. Is there a way to get an update to complete a transaction?

Answer 1

NHibernate operations do not match DB transactions; the structure has no way of knowing what objects or what data affects your ExecuteUpdate , and therefore does not automatically clear the session between SaveOrUpdate (which may be delayed depending on the FlushMode session) and CreateSQLQuery (which is always immediate if you use ExecuteUpdate ) .

Generally speaking, if you combine NHibernate logic with lower-level SQL logic (stored procedures, etc.), then you will want to use TransactionScope 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 is not needed here, since you are not doing anything with the session after Flush , but it is good practice to use an explicit NH transaction anyway in the event of a logic change in the future.)

Note that I prefer to use instances of IStatelessSession for any insertions / updates to the / etc package. - if you use regular ISession instances and you really need to receive updates made using ExecuteUpdate , you may need to use the Evict or Clear methods to ensure that you receive updates.

Answer 2

I'm not sure which version of NHibernate you are using, but given that now after 2 years your answer applies to an earlier version. I am using 3.3.3.GA, and NHibernate transactions were able to manage SQL Query ExecuteUpdate without problems. I verified that I could not see the data received until I completed the transaction. Commit ().

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