2010-01-07 Calendar sizes: DateTime vs Nullable SqlDateTime in NHibernate

Does size matter?

.NET's DateTime class supports a range between 01/01/0001 00:00:00 and 12/31/9999 23:59:59.9999999.  Sql Server's calendar is not quite as big – it starts at 1/1/1753.  Therefore, if you have an object with a DateTime that's set to DateTime.MinValue (or any other date before 1/1/1753) and you try to save that to a database, you'll get the following exception:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

This recently got in my way.  I'm building an application on top of a legacy database.  There's a table with a CreatedDate column that's nullable.  However, in my domain model I didn't want CreatedDate to be a Nullable because everything should have a CreatedDate – is there anything that wasn't created at some point in time?

My object looks like this:

public class MyObject
{
    public MyObject()
    {
        CreatedDate = DateTime.Now;
    }

    public virtual string Name { get; set; }
    public virtual DateTime CreatedDate { get; protected set; }
}

Existing data in legacy database

The problem is that many of the existing entries in the database have a NULL entry for CreatedDate.  Because of this, CreatedDate will be set to DateTime.MinValue.  When I tried to save the object back to the database I got the above mentioned SqlDateTime overflow exception.

Going forward I don't want any more NULL entries, but I don't have the ability to update all existing NULLs to some arbitrary date (without having a whole back and forth with the DBAs).  What I decided would be the best idea would be to support NULL entries in the DB transparently – NULL entries will map to DateTime.MinValue on the GET side, and DateTime.MinValue will map to NULL on the UPDATE/SAVE side.  Since a new object always has its CreatedDate property set to DateTime.Now on instantiation, only really old objects will show 1/1/0001 as their created date in the system.

Implementation of IUserType

NHibernate has an IUserType interface which allows you to specify your own type for saving and retrieving data.  Here's what I wrote to handle my requirements:

public class NullDateTimeAsMinDateType : IUserType
{
    public new bool Equals(object x, object y)
    {
        if (ReferenceEquals(x, y))
            return true;
        if (x == null || y == null)
            return false;
        return x.Equals(y);
    }
    #region IUserType Members

    public object Assemble(object cached, object owner)
    {
        return cached;
    }

    public object DeepCopy(object value)
    {
        return value;
    }

    public object Disassemble(object value)
    {
        return value;
    }

    public int GetHashCode(object x)
    {
        return x == null ? typeof(DateTime).GetHashCode() : x.GetHashCode();
    }

    public bool IsMutable
    {
        get { return false; }
    }

    public object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner)
    {
        var obj = NHibernateUtil.DateTime.NullSafeGet(rs, names[0]);
        if (obj == null)
            return DateTime.MinValue;

        DateTime dateTime = (DateTime) obj;
        return dateTime;
    }

    public void NullSafeSet(System.Data.IDbCommand cmd, object value, int index)
    {
        if (value == null)
        {
            ((IDataParameter)cmd.Parameters[index]).Value = DBNull.Value;
            return;
        }
        DateTime dateTime = (DateTime)value;
        if (dateTime.CompareTo(System.Data.SqlTypes.SqlDateTime.MinValue.Value) < 0)
            ((IDataParameter)cmd.Parameters[index]).Value = DBNull.Value;
        else
            ((IDataParameter)cmd.Parameters[index]).Value = dateTime;
    }

    public object Replace(object original, object target, object owner)
    {
        return original;
    }

    public Type ReturnedType
    {
        get { return typeof(DateTime); }
    }

    public NHibernate.SqlTypes.SqlType[] SqlTypes
    {
        get { return new[] { NHibernateUtil.DateTime.SqlType }; }
    }

    #endregion
}

I'm using FluentNHibernate, so my mapping looks like:

Map(x => x.CreateDDate)
    .Column("create_date")
    .CustomType()
    .Nullable();

That's it.  Now the CreatedDate column in the DB doesn't have to get touched and SQL Server's date fields play nice with .NET DateTime.