James Kovacs' Weblog

[Code for this article is available on GitHub here.]

Nothing gets an OO zealot hot under the collar the way the term polymorphism does. You probably have three questions right now… What does polymorphism have to do with object-relational mapping? How does it relate to NHibernate? And why should I care?

An ORM that supports polymorphic loading allows us to request one type of object, but potentially get an object of a derived type back. As an example, let’s say we have the following simple inheritance hierarchy in our application:

We can query for an Animal, but receive back an instance of Dog or Cat instead.

var dog = session.Get<Animal>(dogId);

NHibernate has supported this type of polymorphic loading behaviour for awhile, but the base class (or interface) had to be mapped. If it wasn’t, polymorphic loading would only work when querying with Criteria or LINQ. The following works for both NH 2.1.2 and NH3 regardless of whether the Animal base class is mapped or not.

var animal = session.CreateCriteria<Animal>()
                    .Add(Restrictions.IdEq(dogId))
                    .UniqueResult<Animal>();

// N.B. Use session.Linq<Animal>() in NH2.1.2
var query = from a in session.Query<Animal>()
            where a.Id == dogId
            select a;
var animal = query.Single();

In NHibernate 2.1.2 and earlier, ISession.Get<T>(id) or ISession.Load<T>(id) would fail if T was an unmapped base class or interface. With NHibernate 3, these methods now work regardless of whether T is mapped or not.*

// Works in NH3; works in NH2.1.2 only if Animal is mapped
// In the sample code, works in NH3 for both Animal and UnmappedAnimal base classes
// In NH2.1.2 and before, works for Animal (mapped), but not UnmappedAnimal
var dog = session.Get<Animal>(dogId);
var cat = session.Load<Animal>(catId);

ASIDE: ISession.Get(id) returns null when the entity doesn’t exist in the database, whereas ISession.Load(id) throws an exception. Generally ISession.Load(id) is preferred if you know the entity should exist as NHibernate can return a proxy object that delays hitting the database until the last possible moment. ISession.Get(id) requires querying the database immediately because there is no way to return an object (e.g. a proxy), but later change it to null when accessed.

In NHibernate 3, polymorphic loading works for Criteria, LINQ, and Get/Load. It has not been implemented for HQL. (If you want/need this feature, the NHibernate team is always willing to accept a feature request with patch.) HQL in NH3 supports polymorphic loading if the queried class is imported via <import class=”UnmappedClass”/> in a hbm.xml file.

// Criteria works in NH2.1.2 and NH3
var animal = session.CreateCriteria<UnmappedAnimal>()
                    .Add(Restrictions.IdEq(dogId))
                    .UniqueResult<UnmappedAnimal>());

// LINQ works in NH2.1.2 and NH3 (NH2.1.2 uses session.Linq<T>())
var query = from a in session.Query<UnmappedAnimal>()
            where a.Id == dogId
            select a;
var animal = query.Single();

// Get/Load works in NH3, but fails in NH2.1.2 and earlier
var animal = session.Get<UnmappedAnimal>(dogId);

// HQL works for NH3 if UnmappedAnimal is imported, but fails for NH2.1.2
var animal = session.CreateQuery("from a in UnmappedAnimal where a.id = :id")
                    .SetParameter("id", dogId)
                    .UniqueResult<UnmappedAnimal>());

* I should note one restriction on the generic parameter T when calling ISession.Get<T>(id) and ISession.Load<T>(). Polymorphic loading only works if there is a unique persister for T. Otherwise NHibernate throws a HibernateException, “Ambiguous persister for [T] implemented by more than one hierarchy”. What does this mean? Let’s say you have an unmapped abstract base class, such as Entity. (Entity is a class defined in our application, which includes properties common across all persistent entities, such as primary key, audit fields, and similar. It is not required by NHibernate, but often useful for extracting common domain code.) Consider the following contrived example:

Note that the Animal inheritance hierarchy is mapped and so is Customer. If we try to execute the following code:

var id = 42;
var entity = session.Get<Entity>(id);

We will get a HibernateException as mentioned above. We are asking NHibernate to load an Entity with an id of 42. But primary keys are only unique within a mapped inheritance hierarchy. So there could be a Cat (or Dog) with id of 42 and a Customer with id of 42! So NHibernate fails with a HibernateException since it has no way of returning a list of objects from Get/Load. If you really want to query across inheritance hierarchies, you can do so with Critera or LINQ where you return a list of objects. The following code will work:

var id = 42;
var entities = session.CreateCriteria<Entity>()
                      .Add(Restrictions.IdEq(id))
                      .List<Entity>();

Here’s a NHibernate trick that makes for a good demo, but isn’t terribly practical in real applications… Retrieve a list of all entities in the database:

var allEntities = session.CreateCriteria<object>()
                         .List<object>();

Happy coding!

UPDATE: Fabio Maulo, NH project lead, pointed out to me that HQL in NHibernate 3 can load unmapped classes so long as you make NHibernate aware of the classes via an <import class=”UnmappedAnimal”/> directive in a hbm.xml file. Thanks, Fabio.

[Code for this article is available on GitHub here.]

In this post, we’ll examine the ways that NHibernate supports the DateTime-related data types, including some new features introduced in NHibernate 2 and 3. Here is a quick summary for the impatient.

DbType	.NET	SQL Type
DateTime	System.DateTime	datetime
LocalDateTime	System.DateTime	datetime
UtcDateTime	System.DateTime	datetime
DateTimeOffset	System.DateTimeOffset	datetimeoffset
DateTime2	System.DateTime	datetime2
Date	System.DateTime	date
Time	System.DateTime	time
TimeAsTimeSpan	System.TimeSpan	time
TimeSpan	System.TimeSpan	bigint (int64)

Local/UTC

Let’s take a look at a few DateTime-related problems that developers have run into in the past…

public class DateTimeEntity {
    public DateTimeEntity() {
        CreationTime = DateTime.Now;
    }

    public virtual Guid Id { get; private set; }
    public virtual DateTime CreationTime { get; set; }
}

Note that CreationTime is initialized using DateTime.Now. The corresponding mapping file would be:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   namespace="Nh3Hacking"
                   assembly="Nh3Hacking">
  <class name="DateTimeEntity">
    <id name="Id">
      <generator class="guid.comb" />
    </id>
    <property name="CreationTime"/>
  </class>
</hibernate-mapping>

If we create an instance of our DateTimeEntity and reload it, we get:

Original entity:
Id: 09bead07-5a05-4459-a108-9e7501204918
        CreationTime: 2011-01-24 5:29:36 PM (Local)
Reloaded entity:
Id: 09bead07-5a05-4459-a108-9e7501204918
        CreationTime: 2011-01-24 5:29:36 PM (Unspecified)

Note that I am outputting both CreationTime.ToString() and CreationTime.Kind. DateTime.Kind returns a DateTimeKind (surprising, huh?), which indicates whether this DateTime represents Local time or UTC time. We initialized the value with DateTime.Now, which is the local time. (If we wanted UTC time, we would use DateTime.UtcNow.) When the object is reloaded, the DateTimeKind is Unspecified. This is because the database does not store whether the DateTime value is Local or UTC. NHibernate has no way of knowing which one it is, hence Unspecified.

NHibernate 3 includes two new DbTypes that allow us to resolve this ambiguity. In our mapping file, we can write:

<property name="CreationTimeAsLocalDateTime" type="LocalDateTime"/>
<property name="CreationTimeAsUtcDateTime" type="UtcDateTime"/>

We are explicitly telling NHibernate whether the database stores Local or UTC times.

Original entity:
Id: 09bead07-5a05-4459-a108-9e7501204918
        CreationTimeAsDateTime: 2011-01-24 5:29:36 PM (Local)
        CreationTimeAsLocalDateTime: 2011-01-24 5:29:36 PM (Local)
        CreationTimeAsUtcDateTime: 2011-01-25 12:29:36 AM (Utc)
Reloaded entity:
Id: 09bead07-5a05-4459-a108-9e7501204918
        CreationTimeAsDateTime: 2011-01-24 5:29:36 PM (Unspecified)
        CreationTimeAsLocalDateTime: 2011-01-24 5:29:36 PM (Local)
        CreationTimeAsUtcDateTime: 2011-01-25 12:29:36 AM (Utc)

This is strictly metadata and it is up to the developer to ensure that the proper DateTime is present in the property/field. For instance, if I initialize the entity as follows:

public DateTimeEntity() {
    CreationTimeAsDateTime = DateTime.Now;
    CreationTimeAsLocalDateTime = DateTime.UtcNow;
    CreationTimeAsUtcDateTime = DateTime.Now;
}

Note that the LocalDateTime property contains a UTC DateTime and the UTC property contains a Local DateTime. The results are:

Original entity:
Id: 4579d245-46f3-4c3f-893b-9e750124a90b
        CreationTimeAsDateTime: 2011-01-24 5:45:32 PM (Local)
        CreationTimeAsLocalDateTime: 2011-01-25 12:45:32 AM (Utc)
        CreationTimeAsUtcDateTime: 2011-01-24 5:45:32 PM (Local)
Reloaded entity:
Id: 4579d245-46f3-4c3f-893b-9e750124a90b
        CreationTimeAsDateTime: 2011-01-24 5:45:32 PM (Unspecified)
        CreationTimeAsLocalDateTime: 2011-01-25 12:45:32 AM (Local)
        CreationTimeAsUtcDateTime: 2011-01-24 5:45:32 PM (Utc)

Notice that NHibernate did not perform any conversions or throw an exception when saving/loading a DateTime value with the wrong DateTimeKind. (It could be argued that NHibernate should throw an exception when asked to save a Local DateTime and the property is mapped as a UtcDateTime.) It is up to the developer to ensure that the proper kind of DateTime is in the appropriate field/property.

System.DateTimeOffset

One problem that LocalDateTime and UtcDateTime does not solve is the offset problem. If you have a DateTime and its Kind is Local, all you know is that it is a Local DateTime. You do not know if that Local DateTime is Mountain (MST), Eastern (EST), Pacific (PST), etc. You do not know whether it has been corrected for daylight savings time. All you know is that it is a Local DateTime. You have to assume that the local time is based on the time zone of the current computer. Although this is often a reasonable assumption, it’s not always. (Consider for example that you’re collecting log files from a distributed system and servers reside in multiple time zones.) The problem is that System.DateTime class does not contain a place to record the timezone offset. Microsoft solved this problem starting in .NET 3.5 by introducing the System.DateTimeOffset class. It looks a lot like System.DateTime, but does include the timezone offset rather than the DateTimeKind. So we can just use System.DateTimeOffset in our applications rather than System.DateTime.

Except… Date/time types in SQL databases do not have anywhere to store the timezone offset. The notable exception is SQL Server 2008, which introduced the datetimeoffset type. NHibernate 2 introduced support for System.DateTimeOffset, but only for SQL Server 2008 onwards. (If you’re using SQL Server 2005 or earlier or another database server, you’ll have to implement your own IUserType to store System.DateTimeOffset in two separate columns – one for the DateTime and the other for the timezone offset.) The additional code in DateTimeEntity.cs looks like this:

public virtual DateTimeOffset CreationTimeAsDateTimeOffset { get; set; }

The mapping file just needs the new property added:

<property name="CreationTimeAsDateTimeOffset"/>

Note that I don’t need to specify the type in the mapping as NHibernate can infer it from the property type in DateTimeEntity. The resulting output is:

Original entity:
Id: 95aa6c15-86f5-4398-aa9e-9e7600ae4580
        CreationTimeAsDateTime: 2011-01-25 10:34:30 AM (Local)
        CreationTimeAsLocalDateTime: 2011-01-25 10:34:30 AM (Local)
        CreationTimeAsUtcDateTime: 2011-01-25 5:34:30 PM (Utc)
        CreationTimeAsDateTimeOffset: 2011-01-25 10:34:30 AM -07:00
Reloaded entity:
Id: 95aa6c15-86f5-4398-aa9e-9e7600ae4580
        CreationTimeAsDateTime: 2011-01-25 10:34:30 AM (Unspecified)
        CreationTimeAsLocalDateTime: 2011-01-25 10:34:30 AM (Local)
        CreationTimeAsUtcDateTime: 2011-01-25 5:34:30 PM (Utc)
        CreationTimeAsDateTimeOffset: 2011-01-25 10:34:30 AM -07:00

Support for DateTime2, Date, and Time

Let’s look at some C# and the corresponding mapping file for these types:

public virtual DateTime CreationTimeAsDateTime2 { get; set; }
public virtual DateTime CreationTimeAsDate { get; set; }
public virtual DateTime CreationTimeAsTime { get; set; }
public virtual TimeSpan CreationTimeAsTimeAsTimeSpan { get; set; }
public virtual TimeSpan CreationTimeAsTimeSpan { get; set; }

Modifications to the hbm.xml:

<property name="CreationTimeAsDateTime2" type="DateTime2"/>
<property name="CreationTimeAsDate" type="Date"/>
<property name="CreationTimeAsTime" type="Time"/>
<property name="CreationTimeAsTimeAsTimeSpan" type="TimeAsTimeSpan"/>
<property name="CreationTimeAsTimeSpan"/>

We’ll examine each of these in turn…

DbType.DateTime2 is a higher precision, wider range version of DbType.DateTime. DbType.DateTime maps to the datetime (or smalldatetime) SQL type, which has a range of 1753-01-01 to 9999-12-31. DbType.DateTime2 maps to the datetime2 SQL type, which has a range of 0001-01-01 to 9999-12-31. (Precision can be as high as 1/10 of a microsecond with a datetime2(7).) One of the niceties of DateTime2 is that an uninitialized DateTime struct (which has a value of 0001-01-01 12:00:00 AM (Unspecified)) does not cause a SqlTypeException with a SqlDateTime underflow.

DbType.Date does just what it advertises. It represents a Date without a Time component. It is stored in the database as only a date. .NET does not have a Date type and NHibernate represents it via a DateTime with the time portion set to 12:00:00 AM. I personally prefer to define my own Date class, which has no time component, and create an IUserType to handle the mapping. My custom Date class can handle the time truncation and provide a more natural programing model for my domain, but that’s a post for another day.

Time-related DbTypes stores just the time, but no date. In .NET, there is no Time class and so NHibernate uses a DateTime with the date component set to 1753-01-01, the minimum value for a SQL datetime or a System.TimeSpan – depending on the DbType that we choose. DbType.Time stores a System.DateTime in a time SQL type. DbType.TimeAsTimeSpan stores a System.TimeSpan as a time SQL type. DbType.TimeSpan stores a Syste.TimeSpan as a 64-bit integer (bigint) SQL type. As I mentioned for DbType.Date, I am more inclined to write my own Time class and custom IUserType to achieve a better programming model than relying on the .NET constructs of System.DateTime and System.TimeSpan. (I typically use System.DateTime or System.TimeSpan as a field in my custom Date or Time class for storing the data, but provide my own API for consistently working with the data.)

WARNING: Not all databases support all date/time SQL types. So before choosing .NET and SQL types for your entities, make sure that they’re available in all databases that you plan to support.

Now we’ll take a look at these date/time types in action:

Original entity:
Id: 6b2fb9ff-8036-4c17-b9ef-9e7600bf37e3
        CreationTimeAsDateTime: 2011-01-25 11:36:12 AM (Local)
        CreationTimeAsLocalDateTime: 2011-01-25 11:36:12 AM (Local)
        CreationTimeAsUtcDateTime: 2011-01-25 6:36:12 PM (Utc)
        CreationTimeAsDateTimeOffset: 2011-01-25 11:36:12 AM -07:00
        CreationTimeAsDateTime2: 2011-01-25 11:36:12 AM (Local)
        CreationTimeAsDate: 2011-01-25 11:36:12 AM (Local)
        CreationTimeAsTime: 2011-01-25 11:36:12 AM (Local)
        CreationTimeAsTimeAsTimeSpan: 11:36:12.2688265
        CreationTimeAsTimeSpan: 11:36:12.2688265
Reloaded entity:
Id: 6b2fb9ff-8036-4c17-b9ef-9e7600bf37e3
        CreationTimeAsDateTime: 2011-01-25 11:36:12 AM (Unspecified)
        CreationTimeAsLocalDateTime: 2011-01-25 11:36:12 AM (Local)
        CreationTimeAsUtcDateTime: 2011-01-25 6:36:12 PM (Utc)
        CreationTimeAsDateTimeOffset: 2011-01-25 11:36:12 AM -07:00
        CreationTimeAsDateTime2: 2011-01-25 11:36:12 AM (Unspecified)
        CreationTimeAsDate: 2011-01-25 12:00:00 AM (Unspecified)
        CreationTimeAsTime: 1753-01-01 11:36:12 AM (Unspecified)
        CreationTimeAsTimeAsTimeSpan: 11:36:12.2700000
        CreationTimeAsTimeSpan: 11:36:12.2688265

Summary

As you have seen, NHibernate has a wide variety of options for mapping date/time-related types to and from the database. The right choice is highly dependent on your application and database server. I hope that this post has given you a few more tricks up your sleeve for effectively mapping date/time-related types using NHibernate.