Why NHibernate updates DB on commit of read-only transaction

http://www.zvolkov.com/clog/2009/07/09/why-nhibernate-updates-db-on-commit-of-read-only-transaction/

Always be careful with NULLable fields whenever you deal with NHibernate. If your field is NULLable in DB, make sure corresponding .NET class uses Nullable type too. Otherwise, all kinds of weird things will happen. The symptom is usually will be that NHibernate will try to update the record in DB, even though you have not changed any fields since you read the entity from the database.

The following sequence explains why this happens:

1. NHibernate retrieves raw entity's data from DB using ADO.NET
2. NHibernate constructs the entity and sets its properties
3. If DB field contained NULL the property will be set to the defaul value for its type:
   
   • properties of reference types will be set to null
   • properties of integer and floating point types will be set to 0
   • properties of boolean type will be set to false
   • properties of DateTime type will be set to DateTime.MinValue
   • etc.
4. Now, when transaction is committed, NHibernate compares the value of the property to the original field value it read form DB, and since the field contained NULL but the property contains a non-null value, NHibernate considers the property dirty, and forces an update of the enity.

Not only this hurts performance (you get extra round-trip to DB and extra update every time you retrieve the entity) but it also may cause hard to troubleshoot errors with DateTime columns. Indeed, when DateTime property is initialized to its default value it's set to 1/1/0001. When this value is saved to DB, ADO.NET's SqlClient can't convert it to a valid SqlDateTime value since the smallest possible SqlDateTime is 1/1/1753!!! The exception it throws looks like this:

NHibernate.Event.Default.AbstractFlushingEventListener - Could not synchronize database state with session 

NHibernate.HibernateException: An exception occurred when executing batch queries ---> System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. 

at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) 

   at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value)

   at System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb)

   at System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)

   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at System.Data.SqlClient.SqlCommandSet.ExecuteNonQuery()

   at NHibernate.AdoNet.SqlClientSqlCommandSet.ExecuteNonQuery()

   --- End of inner exception stack trace ---

   at NHibernate.AdoNet.SqlClientSqlCommandSet.ExecuteNonQuery()

   at NHibernate.AdoNet.SqlClientBatchingBatcher.DoExecuteBatch(IDbCommand ps)

   at NHibernate.AdoNet.AbstractBatcher.ExecuteBatch()

   at NHibernate.Engine.ActionQueue.ExecuteActions(IList list)

   at NHibernate.Engine.ActionQueue.ExecuteActions()

   at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)

   at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event)

   at NHibernate.Impl.SessionImpl.Flush()

   at NHibernate.Transaction.AdoTransaction.Commit()

The easiest fix is to make the class property use Nullable<T> type, in this case "DateTime?". Alternatively, you could implement a custom type mapper by implementing IUserType with its Equals method properly comparing DbNull.Value with whatever default value of your value type. In our case Equals would need to return true when comparing 1/1/0001 with DbNull.Value. Implementing a full-functional IUserType is not really that hard but it does require knowledge of NHibernate trivia so prepare to do some substantial googling if you choose to go that way.

Hope this helps somebody!