Suppose that your model requires selecting over a date range. It is frequently more convenient to store DateTime.MinValue than it is to store a NULL as this allows for easy selecting without doing a bunch of field < value or null or field > value or not null monkey business. The only problem is that DateTime.MinValue doesn’t fit into SQL server, and DateTime.MaxValue won’t store correctly either. You can even end up where you get a situation where you are storing DateTime.MaxValue then checking a freshly loaded entity to see if it equals DateTime.MaxValue and it doesn’t!
One solution is to create an extension method to trim down the DateTime to only include valid values in a deterministic way as follows:
public static class DateTimeExtensions
{
private static readonly DateTime minSqlDateTime = DateTime.Parse("1/1/1753 12:00:00 AM");
public static DateTime ToSqlDateTime(this DateTime dt)
{
if (dt < minSqlDateTime)
dt = minSqlDateTime;
return new DateTime(dt.Ticks - (dt.Ticks % 10000000));
}
}
Note that this is designed for SQL Server. Other database types may need different levels of precision.
Instead you could implement a custom IUserType for NHibernate which tells NHibernate how to translate a DateTime that is out of bounds into one that is in bounds such as:
public class SqlDateTimeUserType : IEnhancedUserType
{
private static readonly DateTime minSqlDateTime = DateTime.Parse("1/1/1753 12:00:00 AM");
public new bool Equals(object x, object y)
{
return x == null ? y == null : x.Equals(y);
}
public Type ReturnedType
{
get { return typeof(DateTime); }
}
public SqlType[] SqlTypes
{
get { return new[] { NHibernateUtil.DateTime.SqlType }; }
}
public int GetHashCode(object x)
{
return x.GetHashCode();
}
public object NullSafeGet(IDataReader dr, string[] names, object owner)
{
//no need to alter values coming out of the database
DateTime? obj = NHibernateUtil.DateTime.NullSafeGet(dr, names[0]) as DateTime?;
return obj == null ? ToSqlDateTime(DateTime.MinValue) : obj.Value;
}
public void NullSafeSet(IDbCommand cmd, object obj, int index)
{
//set it to a safe value going into the database
((IDataParameter)cmd.Parameters[index]).Value = ToSqlDateTime((DateTime)obj);
}
public object DeepCopy(object value)
{
return value;
}
public object Replace(object original, object target, object owner)
{
return original;
}
public object Assemble(object cached, object owner)
{
return cached;
}
public object Disassemble(object value)
{
return value;
}
public bool IsMutable
{
get { return false; }
}
public object FromXMLString(string xml)
{
return DateTime.Parse(xml);
}
public string ObjectToSQLString(object value)
{
return value as string;
}
public string ToXMLString(object value)
{
return value as string;
}
private static DateTime ToSqlDateTime(DateTime dt)
{
if (dt < minSqlDateTime)
dt = minSqlDateTime;
return new DateTime(dt.Ticks - (dt.Ticks % 10000000));
}
}
These user types are mapped as follows:
<property name="ImportDate" type="Framework.SqlDateTimeUserType, Framework"/>
The only problem with this is that a simple test such as the following will fail:
[Test]
public void CanSaveBadValuesToImportDate()
{
With.AutoRollbackTransaction(() =>
{
Product p = new Product("Apples");
p.ImportDate = DateTime.MinValue;
Session.Save(p);
Session.Flush();
Session.Clear();
Product p2 = Session.Load<Product>(p.Id);
Assert.AreEqual(p.Name,p2.Name);
Assert.AreEqual(p.ImportDate, p2.ImportDate); //fails here
});
}
We could try and create a bunch of edge conditions on our converter such as:
public object NullSafeGet(IDataReader dr, string[] names, object owner)
{
DateTime? obj = NHibernateUtil.DateTime.NullSafeGet(dr, names[0]) as DateTime?;
if (obj != null && obj == minSqlDateTime)
return DateTime.MinValue;
return obj == null ? ToSqlDateTime(DateTime.MinValue) : obj.Value;
}
But that could get out of hand in a hurry. It also would cause errors if you stored DateTime.MinValue.AddSeconds(1). If we wanted to go really over the top, we could create our own class that implements operator DateTime (explicit and implicit) which would do the conversion to a SQL range when we first assign a value. This would solve both problems, but you’d end up with your domain needing a special DateTime class for all of your entities instead of using the built in. Your call…
