sql - 如何使用 NHibernate 处理来自 SQL Server 2008 的 TIME 数据类型？

Question

我正在使用 TIME来自 SQL Server 2008 的数据类型，我在使用 NHibernate 时遇到了一些问题。

public TimeTableEventMap()
{
    Id(x => x.Id)
    Map(x => x.Day).NvarcharWithMaxSize().Not.Nullable();
    Map(x => x.StartTime).Length(4).TimeDataType().Not.Nullable();
    Map(x => x.Endtime).Length(4).TimeDataType().Not.Nullable();
    References(x => x.TimeTable).Not.Nullable().Cascade.All();
    References(x => x.RequiredSettings).Not.Nullable().Cascade.All();
}

/// <summary>
///  MS Sql 2008 date type.
/// </summary>
/// <param name="map"></param>
/// <returns></returns>
public static PropertyPart TimeDataType(this PropertyPart map)
{
   return map.CustomSqlType("time");
}

public class TimeTableEvent
{
    public virtual int Id { get; private set; }
    public virtual DayOfWeek Day { get; set; }
    public virtual DateTime StartTime { get; set; }
    public virtual DateTime Endtime { get; set; }
    public virtual TimeTable TimeTable { get; set; }
    public virtual RequiredSetting RequiredSettings { get; set; }
}   

我收到这个错误

NHibernate.Exceptions.GenericADOException was caught
Message=could not execute query
[ SELECT TOP (@p0) this_.TimeTableEventId as TimeTabl1_15_1_, this_.Day as Day15_1_, this_.StartTime as StartTime15_1_, this_.Endtime as Endtime15_1_, this_.TimeTableId as TimeTabl5_15_1_, this_.RequiredSettingsId as Required6_15_1_, requiredse2_.RequiredSettingsId as Required1_10_0_, requiredse2_.BackgroundColor as Backgrou2_10_0_, requiredse2_.Title as Title10_0_ FROM TimeTableEvents this_ inner join RequiredSettings requiredse2_ on this_.RequiredSettingsId=requiredse2_.RequiredSettingsId WHERE this_.TimeTableId in (@p1) ]
Positional parameters: #0>14
[SQL: SELECT TOP (@p0) this_.TimeTableEventId as TimeTabl1_15_1_, this_.Day as Day15_1_, this_.StartTime as StartTime15_1_, this_.Endtime as Endtime15_1_, this_.TimeTableId as TimeTabl5_15_1_, this_.RequiredSettingsId as Required6_15_1_, requiredse2_.RequiredSettingsId as Required1_10_0_, requiredse2_.BackgroundColor as Backgrou2_10_0_, requiredse2_.Title as Title10_0_ FROM TimeTableEvents this_ inner join RequiredSettings requiredse2_ on this_.RequiredSettingsId=requiredse2_.RequiredSettingsId WHERE this_.TimeTableId in (@p1)]
Source=NHibernate
SqlString=SELECT TOP (@p0) this_.TimeTableEventId as TimeTabl1_15_1_, this_.Day as Day15_1_, this_.StartTime as StartTime15_1_, this_.Endtime as Endtime15_1_, this_.TimeTableId as TimeTabl5_15_1_, this_.RequiredSettingsId as Required6_15_1_, requiredse2_.RequiredSettingsId as Required1_10_0_, requiredse2_.BackgroundColor as Backgrou2_10_0_, requiredse2_.Title as Title10_0_ FROM TimeTableEvents this_ inner join RequiredSettings requiredse2_ on this_.RequiredSettingsId=requiredse2_.RequiredSettingsId WHERE this_.TimeTableId in (@p1)

StackTrace:
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet1 querySpaces, IType[]
resultTypes)<br/>
at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor
session)<br/>
at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria,
IList results)<br/>
at NHibernate.Impl.CriteriaImpl.List(IList results)<br/>
at NHibernate.Impl.CriteriaImpl.List[T]()<br/>
at NHibernate.Criterion.QueryOver1.ListU
at NHibernate.Criterion.QueryOver`1.NHibernate.IQueryOver.ListU
at TimeTableRepo.cs:line 47
at TimeTableService.cs:line 43
InnerException: System.FormatException
Message=Input string '16:00:00' was not in the correct format.
Source=NHibernate
StackTrace:
at NHibernate.Type.DateTimeType.Get(IDataReader rs, Int32 index)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String[][] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session)
at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session)
at NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session)
at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session)
at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
InnerException: System.InvalidCastException
Message=Unable to cast object of type 'System.TimeSpan' to type 'System.IConvertible'.
Source=mscorlib

public List<TimeTableEvent> GetTimeTableEvents(Student student, List<int> timeTableIds)
{
    TimeTableEvent tAlias = null;

    List<TimeTableEvent> allEvents = session.QueryOver<TimeTableEvent>(() => tAlias)
        .Where(Restrictions.In(Projections.Property(() => tAlias.TimeTable.Id), timeTableIds))
        .Fetch(r => r.RequiredSettings).Eager
        .TransformUsing(Transformers.DistinctRootEntity)
        .Take(QueryLimits.TimeTableEvents)
        .List<TimeTableEvent>().ToList();

    return allEvents;
}

Answer 1

Date/Time Support in NHibernate