Currently, I am involved in developing a client-server multi-layered application that uses NHibernate library as its data access layer. It is designed to support some old legacy database schema. Database is SQL Server 2005. All tables in schema have got two fields which are called CREATE_TIME and UPDATE_TIME. They are both of type datetime nullable in DB schema (btw. our schema contains about 200 tables, which is, in my opinion, A LOT). Their purpose was to describe the time when a certain record was created and when it was updated.

To cope with these fields I created a base class for all mapped entities in our project – a class called BaseEntity:

    public abstract class BaseEntity
    {
        protected int id; // always mapped to primary key
        protected DateTime created;
        protected DateTime updated;

        public BaseEntity() { }

        public virtual int Id
        {
            get { return id; }
            set { this.id = value; }
        }
        public virtual DateTime Created
        {
            set { created = value; }
            get { return created; }
        }
        public virtual DateTime Updated
        {
            set { updated = value; }
            get { return updated; }
        }
    }

The three members of this class were mapped in every NHibernate mapping file (the code below shows a mapping for a Foo class whose base class is, of course, BaseEntity):

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Some.Assembly" namespace="ProjectNamespace.Types">
  <class name="Foo" table="Foo_table">
    
  <id name="Id" type="System.Int32" column="ID_FOO_TABLE" unsaved-value="0">
    <generator class="native" />
  </id>

   <!-- BaseEntity -->
  <property name="created" column="CREATE_TIME" access="field" not-null="false" type="System.DateTime"  insert="true" update="true"/>
  <property name="updated" column="UPDATE_TIME" access="field" not-null="false" type="System.DateTime"  insert="true" update="true"/>
  
  <!-- other properties here... -->
  </class>
</hibernate-mapping>

Everything seemed to work just fine until we populated our database with some real data taken from a production environment. I need to mention here that the tables were very very big (a lot of columns and records – besides the schema is an excellent example of a bad design).
Suddenly, it turned out during tests that a strange exception was caught when users tried to save certain entities. The exception looked like this:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM….
However, this exception didn’t appear too often, which made it difficult to debug. It happened only sometimes on NHibernate operations such us session.Merge() or session.Update().
We spent a lot of time trying to figure out what was going on. NHibernate traces were giving a clue that something is wrong with created/updated fields – Nhibernate was trying to persist ‘0001-01-01 00:00:00’ values, which are incorrect values for SQL Server datetime column type.

The explanation is really simple. The database we were given contained a few records whose CREATE_TIME and UPDATE_TIME fields were null. We actually did not care about them – there were more important things to do than storing last modification time in every entity. During merge or update operation NHibernate converted these fields into DateTime.MinValue (which is actually ‘0001-01-01 00:00:00’) and such a value causes SqlDateTime overflow exception when entity is being saved. So the solution is also very simple. It is enough to change created/updated members type to Nullable<DateTime>:

    public abstract class BaseEntity
    {
        // ...
        protected DateTime? created;
        protected DateTime? updated;
        // ...
     }