When it comes to NHibernate, dates are no fun!

I just stumbled on some nasty behavior. While preparing some presenation on mapping existing schemas with NHibernate, I was using a mapped version of Northwind to do some queries.

I queried all the Customers with all their orders. As usual I was doing this in a transaction and expected that nothing would have changed, so nothing would be written back to the db. Nevertheless an exception of the type Data.SqlTypes.SqlTypeException with the message "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.".

What had happend was this:

The Order contains a nullable column ShippingDate. I didn't map that column as a Nullable<DateTime> but just as DateTime. When NHibernate reads the row from the db, the value is null, and that's what the session remembers. When the Order object is rehydrated by NHibernate the ShippingDate is set to the value DateTime.MinValue. When the Session is synchronized with the db, NHibernate assumes that something has changed, because the currentState and the previousState are different and tries to update the row. Wich in turn fails, because DateTime.MinValue won't fit into a SqlServer datetime column.

I was lucky I could just inject my "StopHereForDebuggingInterceptor" into the session to look at what was happening. Mapping the Field as DateTime? solved the issue. I wonder if the descicion to map DBNull to DateTime.MinValue was a sound one.

1 comment:

Ben Barnett said...

Thanks that was a great help. Once I changed the DateTime fields to Nullable<DateTime> types it worked like a charm.


whos online

Ralf"s shared items