How do I create an NHibernate IUserType for a Time in Sql Server 2008/2012?

Question

I am trying to create an NHibernate IUserType for the Noda Time LocalTime type which would logically map to a time type in Sql Server 2008/2012. I am able to get values saving and loading from the database. However, I can't write queries involving comparison of local times like _session.Query<SchedulingTemplate>().Where(x => x.Start < end && x.End >= start) gives the error SqlException (0x80131904): The data types time and datetime are incompatible in the less than operator.

The relevant code from my user type is:

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

public override object NullSafeGet(IDataReader rs, string[] names, object owner)
{
    var dbValue = NHibernateUtil.Time.NullSafeGet(rs, names);
    if(dbValue == null)
        return null;

    return LocalDateTime.FromDateTime((DateTime)dbValue).TimeOfDay;
}

public override void NullSafeSet(IDbCommand cmd, object value, int index)
{
    if(value == null)
        NHibernateUtil.Time.NullSafeSet(cmd, null, index);
    else
        NHibernateUtil.Time.NullSafeSet(cmd, ((LocalTime)value).LocalDateTime.ToDateTimeUnspecified(), index);
}

public override SqlType[] SqlTypes
{
    get { return new[] { SqlTypeFactory.Time }; }
}

The problem is that despite the above code indicating the database type is a time, it generates the following query (per Sql Profiler):

exec sp_executesql N'select [...] from [SchedulingTemplate] scheduling0_ where scheduling0_.Start<@p0 and scheduling0_.[End]>=@p1',N'@p0 datetime,@p1 datetime',@p0='1753-01-01 20:00:00',@p1='1753-01-01 06:00:00'

(note I omitted the select list for brevity)

Notice that the type and value of the parameters is being treated as datetime.

This appears to be very similar to two NH bugs that have been closed https://nhibernate.jira.com/browse/NH-2661 and https://nhibernate.jira.com/browse/NH-2660.

I tried to use NHibernateUtil.TimeAsTimeSpan and that didn't seem to work either. It generated exactly the same query which surprised me. I am thinking maybe the issue described in NH-2661 also exists for user types and was not fixed for that?

I am using NHibernate v3.3.1.400 and Noda Time 1.0.0-beta2

Answer 1

Following @Firo's advice, I worked from the time SqlType and came up with this:

using NHibernate;
using NHibernate.Dialect;
using NHibernate.SqlTypes;
using NHibernate.Type;
using NodaTime;
using NodaTime.Text;
using System;
using System.Data;
using System.Data.SqlClient;

[Serializable]
public class LocalTimeType : PrimitiveType, IIdentifierType
{
    private readonly LocalTimePattern _timePattern = LocalTimePattern.CreateWithInvariantCulture("h:mm:ss tt");

    public LocalTimeType() : base(SqlTypeFactory.Time) { }

    public override string Name
    {
        get { return "LocalTime"; }
    }

    public override object Get(IDataReader rs, int index)
    {
        try
        {
            if (rs[index] is TimeSpan) //For those dialects where DbType.Time means TimeSpan.
            {
                var time = (TimeSpan)rs[index];
                return LocalTime.Midnight + Period.FromTicks(time.Ticks);
            }

            var dbValue = Convert.ToDateTime(rs[index]);
            return LocalDateTime.FromDateTime(dbValue).TimeOfDay;
        }
        catch (Exception ex)
        {
            throw new FormatException(string.Format("Input string '{0}' was not in the correct format.", rs[index]), ex);
        }
    }

    public override object Get(IDataReader rs, string name)
    {
        return Get(rs, rs.GetOrdinal(name));
    }

    public override Type ReturnedClass
    {
        get { return typeof(LocalTime); }
    }

    public override void Set(IDbCommand st, object value, int index)
    {
        var parameter = ((SqlParameter)st.Parameters[index]);
        parameter.SqlDbType = SqlDbType.Time; // HACK work around bad behavior, M$ says not ideal, but as intended, NH says this is a bug in MS may work around eventually
        parameter.Value = new TimeSpan(((LocalTime)value).TickOfDay);
    }

    public override bool IsEqual(object x, object y)
    {
        return Equals(x, y);
    }

    public override int GetHashCode(object x, EntityMode entityMode)
    {
        return x.GetHashCode();
    }

    public override string ToString(object val)
    {
        return _timePattern.Format((LocalTime)val);
    }

    public object StringToObject(string xml)
    {
        return string.IsNullOrEmpty(xml) ? null : FromStringValue(xml);
    }

    public override object FromStringValue(string xml)
    {
        return _timePattern.Parse(xml).Value;
    }

    public override Type PrimitiveClass
    {
        get { return typeof(LocalTime); }
    }

    public override object DefaultValue
    {
        get { return new LocalTime(); }
    }

    public override string ObjectToSQLString(object value, Dialect dialect)
    {
        return "'" + _timePattern.Format((LocalTime)value) + "'";
    }
}

The key code is in the Set method where is says:

var parameter = ((SqlParameter)st.Parameters[index]);
parameter.SqlDbType = SqlDbType.Time;

This is needed because the MS data provider takes setting the DbType to DbType.Time to mean the underlying type should be DateTime. You must set the SqlDbType to time for it to work.