How to enlist ADO commands into an NHibernate transaction

Adoption of NHibernate in a legacy environment can be daunting for a number of reasons.  Aside from the overhead of becoming proficient with the framework itself, developers are also faced with thousands of lines of working (it's assumed) code that is already conversing with the system's data store(s).  If complete migration to NHibernate is a prerequisite, then such systems would never make the move.  That being said, NHibernate does afford a way to make calls using the plain old ADO.Net API.  To be more precise, NHibernate allows users to enlist IDbCommands into NHibernate transactions.

Personally, I've only used this feature to work around limitations of NHibernate (yes, although I believe NHibernate is the best thing since sliced bread, I do acknowledge its limitations...unlike <disparaging remark about other OS/Language/Methodology fanatics here>).  This feature was necessary for bulk delete operations…example follows:

HQL in NHibernate is handy, but can get you in trouble.  Say you fire off an HQL query something like this:

DELETE FROM User WHERE User.Clue = null   //not sure offhand of exact HQL syntax

This would delete pretty much all your users, so if you have a few million clueless users in the system NHibernate will load the Users into the first level cache and then delete them.  I had a similar situation with a gig I was on not too long ago.  Caching the objects before deleting them would not only have taken a long time, it would have brought the server to its knees after the page file was used up.  I couldn’t skip the caching stage using NHibernate directly, but I could do something like this:

ISession session = sessionFactory.GetSession();

using(ITransaction transaction = session.BeginTransaction())

{

            ...NHibernate stuff...

IDbCommand command = new SqlCommand();

command.Connection = session.Connection;

transaction.Enlist(command);

command.CommandText = "delete from User where clue is null";

command.ExecuteNonQuery();

...more NHibernate stuff... 

}

In the using block I could do all sorts of NHibernate stuff yet still do my bulk delete without the caching overhead.  ADO commands used in this way are safely tucked into your current NHibernate Transaction context.

 This feature can be handy, but keep in mind that NHibernate remains unaware of state changes in the repository.  If I had a User collection in memory, I’d have to ignore the previous state after the delete and recall the collection based on my previous find criteria.  Stored Procedures could also be used this way, but Ayende blogged not to long ago about an alternative approach (I’m not sure off hand of the version of NHibernate that supports this).