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());
    }
}

Answer 2

Another proposal which allow to keep track of the original timezone offset:

public class DateTimeOffsetUserType : ICompositeUserType
{
    public string[] PropertyNames
    {
        get { return new[] { "LocalTicks", "Offset" }; }
    }

    public IType[] PropertyTypes
    {
        get { return new[] { NHibernateUtil.Ticks, NHibernateUtil.TimeSpan }; }
    }

    public object GetPropertyValue(object component, int property)
    {
        var dto = (DateTimeOffset)component;

        switch (property)
        {
            case 0:
                return dto.UtcTicks;
            case 1:
                return dto.Offset;
            default:
                throw new NotImplementedException();
        }
    }

    public void SetPropertyValue(object component, int property, object value)
    {
        throw new NotImplementedException();
    }

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

    public new bool Equals(object x, object y)
    {
        if (ReferenceEquals(x, null) && ReferenceEquals(y, null))
            return true;

        if (ReferenceEquals(x, null) || ReferenceEquals(y, null))
            return false;

        return x.Equals(y);
    }

    public int GetHashCode(object x)
    {
        return x.GetHashCode();
    }

    public object NullSafeGet(IDataReader dr, string[] names, ISessionImplementor session, object owner)
    {
        if (dr.IsDBNull(dr.GetOrdinal(names[0])))
        {
            return null;
        }

        var dateTime = (DateTime)NHibernateUtil.Ticks.NullSafeGet(dr, names[0], session, owner);
        var offset = (TimeSpan)NHibernateUtil.TimeSpan.NullSafeGet(dr, names[1], session, owner);

        return new DateTimeOffset(dateTime, offset);
    }

    public void NullSafeSet(IDbCommand cmd, object value, int index, ISessionImplementor session)
    {
        object utcTicks = null;
        object offset = null;

        if (value != null)
        {
            utcTicks = ((DateTimeOffset)value).DateTime;
            offset = ((DateTimeOffset)value).Offset;
        }

        NHibernateUtil.Ticks.NullSafeSet(cmd, utcTicks, index++, session);
        NHibernateUtil.TimeSpan.NullSafeSet(cmd, offset, index, session);
    }

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

    public bool IsMutable
    {
        get { return false; }
    }

    public object Disassemble(object value, ISessionImplementor session)
    {
        return value;
    }

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

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

Fluent NNibernate convention from DateTimeOffset ICompositeUserType would be:

public class DateTimeOffsetTypeConvention : IPropertyConvention, IPropertyConventionAcceptance
{
    public void Accept(IAcceptanceCriteria<IPropertyInspector> criteria)
    {
        criteria.Expect(x => x.Type == typeof(DateTimeOffset));
    }

    public void Apply(IPropertyInstance instance)
    {
        instance.CustomType<DateTimeOffsetUserType>();
    }
}

Answer 3

As i'm short on rep I can not add this as a comment to the accepted answer, but wanted to add some additional information I found while implementing the solution in the accepted answer. I too was getting the error that the dialect doesn't support DateTimeOffset when calling schema export. After adding in log4net logging support, I was able to figure out that my properties that were of type DateTimeOffset? were not being handled by the convention. That is, the convention wasn't being applied to nullable DateTimeOffset properties.

To solve this I created a class which derrives from NormalizedDateTimeUserType and overrides the ReturnedType property (had to mark the original as virtual). Then I created a second UserTypeConvention for my derrived class, and finally added the second convention to my configuration.