Unit testing NHibernate w/ SQLite and DateTimeOffset mappings

Question

Porting over an application to use NHibernate from a different ORM.

I've started to put in place the ability to run our unit tests against an in memory SQLite database. This works on the first few batches of tests, but I just hit a snag. Our app would in the real world be talking to a SQL 2008 server, and as such, several models currently have a DateTimeOffset property. When mapping to/from SQL 2008 in non-test applications, this all works fine.

Is there some mechanism either in configuring the database or some other facility so that when I use a session from my SQLite test fixture that the DateTimeOffset stuff is "auto-magically" handled as the more platform agnostic DateTime?

Answer 1

Coincidentally, I just hit this problem myself today :) I haven't tested this solution thoroughly, and I'm new to NHibernate, but it seems to work in the trivial case that I've tried.

First you need to create an IUserType implementation that will convert from DateTimeOffset to DateTime. There's a full example of how to create a user type on the Ayende blog but the relevant method implementations for our purposes are:

public class NormalizedDateTimeUserType : IUserType
{
    private readonly TimeZoneInfo databaseTimeZone = TimeZoneInfo.Local;

    // Other standard interface  implementations omitted ...

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

    public SqlType[] SqlTypes
    {
        get { return new[] { new SqlType(DbType.DateTime) }; }
    }

    public object NullSafeGet(IDataReader dr, string[] names, object owner)
    {
        object r = dr[names[0]];
        if (r == DBNull.Value)
        {
            return null;
        }

        DateTime storedTime = (DateTime)r;
        return new DateTimeOffset(storedTime, this.databaseTimeZone.BaseUtcOffset);
    }

    public void NullSafeSet(IDbCommand cmd, object value, int index)
    {
        if (value == null)
        {
            NHibernateUtil.DateTime.NullSafeSet(cmd, null, index);
        }
        else
        {
            DateTimeOffset dateTimeOffset = (DateTimeOffset)value;
            DateTime paramVal = dateTimeOffset.ToOffset(this.databaseTimeZone.BaseUtcOffset).DateTime;

            IDataParameter parameter = (IDataParameter)cmd.Parameters[index];
            parameter.Value = paramVal;
        }
    }
}

The databaseTimeZone field holds a TimeZone which describes the time zone that is used to store values in the database. All DateTimeOffset value are converted to this time zone before storage. In my current implementation it is hard-coded to the local time zone, but you could always define an ITimeZoneProvider interface and have it injected into a constructor.

To use this user type without modifying all my class maps, I created a Convention in Fluent NH:

public class NormalizedDateTimeUserTypeConvention : UserTypeConvention<NormalizedDateTimeUserType>
{
}

and I applied this convention in my mappings, as in this example (the new NormalizedDateTimeUserTypeConvention() is the important part):

mappingConfiguration.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly())
                .Conventions.Add(
                PrimaryKey.Name.Is(x => x.EntityType.Name + "Id"), 
                new NormalizedDateTimeUserTypeConvention(),
                ForeignKey.EndsWith("Id"));

Like I said, this isn't tested thoroughly, so be careful! But now, all I need to do is to alter one line of code (the fluent mappings specification) and I can switch between DateTime and DateTimeOffset in the database.

---

Edit

As requested, the Fluent NHibernate configuration:

To build a session factory for SQL Server:

private static ISessionFactory CreateSessionFactory(string connectionString)
{
    return Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2008.ConnectionString(connectionString))
            .Mappings(m => MappingHelper.SetupMappingConfiguration(m, false))
            .BuildSessionFactory();
}

For SQLite:

return Fluently.Configure()
            .Database(SQLiteConfiguration.Standard.InMemory)
            .Mappings(m => MappingHelper.SetupMappingConfiguration(m, true))
            .ExposeConfiguration(cfg => configuration = cfg)
            .BuildSessionFactory();

Implementation of SetupMappingConfiguration:

public static void SetupMappingConfiguration(MappingConfiguration mappingConfiguration, bool useNormalizedDates)
{
    mappingConfiguration.FluentMappings
        .AddFromAssembly(Assembly.GetExecutingAssembly())
        .Conventions.Add(
            PrimaryKey.Name.Is(x => x.EntityType.Name + "Id"), 
            ForeignKey.EndsWith("Id"));

    if (useNormalizedDates)
    {
        mappingConfiguration.FluentMappings.Conventions.Add(new NormalizedDateTimeUserTypeConvention());
    }
}