Ayende @ Rahien

Refunds available at head office

NHibernate: Nullable DateTime Issues

System.DateTime is a value type in .Net, which means that it can never be null. But what happens when you have a nullable date time in the database, and you load it into a DateTime type?

Consider this simple example. Mapping:

<property name="UpdatedDate" nullable="True"  column="updated_date" type="DateTime" />

Property:

public DateTime UpdatedDate 
{
      
get { return m_dateTime; }
     
set { m_DateTime = value; }
}

When NHibernate loads a null value from the database, it cannot put a null in the UpdatedDate property, the CLR doesn't allow it. What happens is that the UpdatedDate property is set to the default DateTime value, in this case: 01/01/0001.

This can cause two major issues down the road. The first is that 01/01/0001 is not a valid date in SQL Server, so when you try to save the value, it will throw an exception. The second is that because of this issue, when NHibernate needs to track changes, it will check if null != 01/01/0001, and it will turn out that yes, this entity (which we never touched) has changed.

This can cause an extra update (and thus an exception) which can cause some fairly significant head scratching. The solution is simple, you need to let NHibernate know what to do with null values. This can be done by simply using a nullable type, such as:

public DateTime? UpdatedDate 
{
      
get { return m_dateTime; }
     
set { m_DateTime = value; }
}

Or by using the Nullables.dll library for 1.1, and specifying the correct type in the mapping:

<property name="UpdatedDate" column="updated_date" type="Nullables.NHibernate.NullableDateTimeType, Nullables.NHibernate" />

With this property:

public Nullables.NullableDateTime UpdatedDate 
{
   
get { return m_dateTime; }
   set { m_DateTime = value; }
}

Thanks for Sheraz Khan, for finding out and brining this to my attention, since then, I have run into the issue a couple of times, and I hope that if I blog about it, I will remember to match nullabilities.

Comments

Frans Bouma
03/26/2007 09:53 AM by
Frans Bouma

No offence, but isn't this a simple bug? I mean, if the field doesn't get changed/set to a value, it shouldn't be seen as a dirty field by NHibernate. It's one of the downsides of comparing values of an entity to save with the 'original' values.

Another gem is a nullable int, which likely defaults to 0. When I set it to a value, 0, it's a dirty field and the entity should be saved as the entity is dirty. However, if I DON'T set the field to a value, it's also 0, though it SHOULDN'T be saved, as the field isn't changed and thus the entity isn't dirty.

Still saving the entity is a bug, as it will update the field to 0, though that's not the problem. The problem is that a timestamp in the row will get changed as well, marking the field as 'changed' while it's not. These things can have big consequences.

Working with non-native Nullable types (thus not the .NET 2.0 ones) can have complications in other tiers, for example in the mud called Winforms (which also has problems with Nullable sometimes. ).

Ayende Rahien
03/26/2007 10:46 AM by
Ayende Rahien

A bug is such a harsh term :-)

You summarized the problem with nullable value types masterly, From the point of view of the ORM, it cannot know if the value was changed by the user action or because of the nullable mess.

It is possible to track changes in the property level, so the ORM would know whatever you set the property or it was set by the ORM itself, but that forces the user to always use the property, which isn't always done.

In this case, I would say that the issue is with the code, not with the ORM, using a value type is an indication that all values are valid, so there is no place for a null.

Nullable types are an issue, especially the non-native ones, but I don't really see another option.

Ken Tong
03/27/2007 01:36 AM by
Ken Tong

AFAIK, default value of nullable int (int?) is null. There should be no born-to-be-dirty problem.

Tomer Gabel
03/27/2007 04:22 PM by
Tomer Gabel

Frans, I don't see your point - a proper (C# 2.0) nullable type should be properly handled. If val.HasValue is false, then a null is emitted to the database. Otherwise, the value is emitted. Where is the problem?

Ayende Rahien
03/27/2007 04:25 PM by
Ayende Rahien

Not all tools support it, unfortunately.

Specifically, not all the desingers/ databinding tools

Tomer Gabel
03/27/2007 04:28 PM by
Tomer Gabel

I'd imagine that, unless you have a specific requirement to accommodate external dependencies in advance, this shouldn't really be a problem.

Is that an actually common scenario? I have relatively little experience developing CRUD applications, so I honestly don't know sort of requirements what to expect.

Just For Fun
03/31/2007 12:40 AM by
Just For Fun

A good blog entry by Oren (Ayende) about Nullable types and how NHibernate handles them. I am starting

Comments have been closed on this topic.