Al Priest’s Blog

The system i’m working on at the moment recently went into systest and we found a couple of database errors relating which NHibernate was throwing an exception of

Unexpected row count: -1, expected 1

This wasn’t happening on the development system so we took the apparently faulty database from systest and ran it in dev. No errors. This all pointed to an environment issue and after a little checking we discovered that the NOCOUNT setting was different between each database server.

But hang on - when we create the ISession we were creating an IDbCommand and setting NOCOUNT OFF, surely this was enough? Running a SQL profiler I spotted sp_reset_connection commands being executed almost every other statement from NH. This was resetting the NOCOUNT setting and thus it inherited it from the server setting all the time.

Comparing another system’s NH provider versus the one in this system, and I discovered a subtle difference. System A creates an IDbConnection itself and passes it into NH, whereas system B lets NH generate its own connection. It seems that the ConnectionProvider in NH tracks where the connection was created and aggressively releases it if it was internally created.

So my advice is always pass your own connection to NH which allows for better testing and removes the possibility of this happening. Spring.net was a very quick candidate for doing this DI for me.

UPDATE: Since writing my post, i’ve found that providing your own connection prevents NHibernate using the second-level cache so the better solution is to write your own implementation of ConnectionProvider which calls the NOCOUNT OFF on new connections and wire that up in your .config file. Here’s my implementation.

UPDATE 2: I’ve been writing a Linq to SQL app this week, and it turns out that it also requires NOCOUNT OFF, otherwise you get a “Row not found or changed” exception of type ChangeConflictException with a single ObjectChangeConflict object, which has 0 MemberConflicts!