NHibernate & MS Access: problems with autonumber fields

I was playing around with NHibernate and MS Access a bit the other day, and I ran into an annoying problem.

When I tried to save an object which maps to a table wich has an 'autonumber' field, I received this error:

a different object with the same identifier value was already associated with the session: 0

It looked like NHibernate was unable to retrieve the ID that has been given to the new record in the database.

So, I decided to set up log4net to see if I could find any clue in the logs about the reason for this problem.

As it turned out, it seems that NHibernate was closing the database-connection between the INSERT and the SELECT @@identity statements.

DEBUG NHibernate.SQL - INSERT INTO table ...
DEBUG NHibernate.Connection.DriverConnectionProvider - Obtaining 
IDbConnection from Driver
DEBUG NHibernate.Impl.BatcherImpl - Closed IDbCommand, open IDbCommands: 0
DEBUG NHibernate.Impl.ConnectionManager - aggressively releasing 
database connection
DEBUG NHibernate.Connection.ConnectionProvider - Closing connection
DEBUG NHibernate.Impl.BatcherImpl - Opened new IDbCommand, open IDbCommands: 1
DEBUG NHibernate.Impl.BatcherImpl - Building an IDbCommand object for 
the SqlString: select @@identity
DEBUG NHibernate.SQL - select @@identity

After some research in the Hibernate documentation and on the Internet, I discovered that this is caused by the 'connection release mode'.

The default connection release mode seems to release the connection after each SQL statement, so obviously, Access is not able to determine the correct ID of the last inserted record, since the select @@identity command must be executed on the same connection as its related INSERT statement.

Now that we know what caused this problem, it is easy to fix it: just make sure that you do not use the default connection release mode when using NHibernate with MS Access.

To do so, you should specify the connection release mode in your NHibernate configuration file like this:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="hibernate.connection.release_mode">on_close</property>
  </session-factory>
</hibernate-configuration>

More information regarding the different options / settings for the connection-release mode can be found here.