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 being 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 would happen. The symptom is usually would be NHibernate would try to update the record in DB, even though you has not changed any F Ields 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
2. NHibernate constructs the entity and sets Its properties
3. If DB field contained NULL the property would be set to the Defaul value for its Type:
   
   • properties of reference types would be set to null
   • Properties of Intege R and floating point types would is set to 0
   • Properties of Boolean type would be set to False
   • properties of DateTime type would be set to Datetime.minvalue
   • etc.
4. Now, when transaction was 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 consider s the property dirty, and forces an update of the enity.

Hurts performance (you get extra round-trip to DB and extra update every time, retrieve the entity) but I T also may cause hard-troubleshoot errors with DateTime columns. Indeed, when the DateTime property was initialized to its default value it's set to 1/1/0001. When this value was saved to DB, ADO ' s SqlClient can ' t convert it to a valid SqlDateTime value since the smallest Possi BLE 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 do the class property with nullable<t> type, in this case "DateTime?". Alternatively, could implement a custom type mapper by implementing Iusertype with its Equals method properly Comparin G DBNull.Value with whatever default value of your value type. In we case Equals would need to return true when comparing 1/1/0001 with DBNull.Value. Implementing a full-functional Iusertype is isn't really that hard but it does require knowledge of NHibernate trivia so pre Pare to does some substantial Googling if you choose to go.

Hope this helps somebody!

Why NHibernate updates DB on commit of read-only transaction