nhibernate - 如何在 Sql Server 2008/2012 中创建一个 NHibernate IUserType？

Question

我正在尝试创建一个 NHibernate IUserType野田时间LocalTime逻辑上映射到 time 的类型输入 Sql Server 2008/2012。我能够从数据库中保存和加载值。但是，我无法编写涉及本地时间比较的查询，例如 _session.Query<SchedulingTemplate>().Where(x => x.Start < end && x.End >= start)给出错误 SqlException (0x80131904): The data types time and datetime are incompatible in the less than operator.

我的用户类型的相关代码是:

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

问题是，尽管上面的代码指示数据库类型是时间，但它会生成以下查询(根据 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'

(请注意，为了简洁起见，我省略了选择列表)

请注意，参数的类型和值被视为 datetime .

这似乎与已关闭的两个 NH bug 非常相似 https://nhibernate.jira.com/browse/NH-2661和 https://nhibernate.jira.com/browse/NH-2660 .

我尝试使用NHibernateUtil.TimeAsTimeSpan这似乎也不起作用。它生成了完全相同的查询，这让我感到惊讶。我想也许 NH-2661 中描述的问题也存在于用户类型中并且没有得到修复？

我正在使用 NHibernate v3.3.1.400 和 Noda Time 1.0.0-beta2

Answer 1

按照@Firo的建议，我从SqlType开始就工作并想出了这个:

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) + "'";
    }
}

关键代码位于Set方法中，其中表示:

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

这是必需的，因为 MS 数据提供程序将 DbType 设置为 DbType.Time 表示基础类型应为 DateTime。您必须将SqlDbType设置为它起作用的时间。