如何使用NHibernate处理SQL Server 2008中的时间数据类型?

I am using the TIME datatype from SQL Server 2008 and I am having some problems getting it to work with NHibernate.

我正在使用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; }
}

I get this error

我得到这个错误

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)

NHibernate.Exceptions。捕获的GenericADOException消息=无法执行查询[SELECT TOP (@p0) this_。TimeTableEventId TimeTabl1_15_1_,this_。天Day15_1_ this_。开始时间为StartTime15_1_ this_。Endtime Endtime15_1_,this_。TimeTableId TimeTabl5_15_1_,this_。RequiredSettingsId Required6_15_1_,requiredse2_。RequiredSettingsId Required1_10_0_,requiredse2_。写成BackgroundColor Backgrou2_10_0_,requiredse2_。标题为Title10_0_ FROM TimeTableEvents this_ inner join RequiredSettings requiredse2_ on this_.RequiredSettingsId=requiredse2_。RequiredSettingsId this_的地方。位置参数:#0>14 [SQL:选择TOP (@p0) this_。TimeTableEventId TimeTabl1_15_1_,this_。天Day15_1_ this_。开始时间为StartTime15_1_ this_。Endtime Endtime15_1_,this_。TimeTableId TimeTabl5_15_1_,this_。RequiredSettingsId Required6_15_1_,requiredse2_。RequiredSettingsId Required1_10_0_,requiredse2_。写成BackgroundColor Backgrou2_10_0_,requiredse2_。标题为Title10_0_ FROM TimeTableEvents this_ inner join RequiredSettings requiredse2_ on this_.RequiredSettingsId=requiredse2_。RequiredSettingsId this_的地方。Source=NHibernate SqlString=SELECT TOP (@p0) this_。TimeTableEventId TimeTabl1_15_1_,this_。天Day15_1_ this_。开始时间为StartTime15_1_ this_。Endtime Endtime15_1_,this_。TimeTableId TimeTabl5_15_1_,this_。RequiredSettingsId Required6_15_1_,requiredse2_。RequiredSettingsId Required1_10_0_,requiredse2_。写成BackgroundColor Backgrou2_10_0_,requiredse2_。标题为Title10_0_ FROM TimeTableEvents this_ inner join RequiredSettings requiredse2_ on this_.RequiredSettingsId=requiredse2_。RequiredSettingsId this_的地方。TimeTableId(@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) at NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session) at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) at NHibernate.Impl.CriteriaImpl.List(IList results) at NHibernate.Impl.CriteriaImpl.List[T]() 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

加:NHibernate.Loader.Loader。在NHibernate.Loader.Loader中DoList(ISessionImplementor会话，QueryParameters QueryParameters)。在NHibernate.Loader.Loader中，listarnorequerycache (ISessionImplementor会话，QueryParameters QueryParameters)。List(ISessionImplementor session, QueryParameters QueryParameters, ISet1 queryspace, IType[] resultTypes)在nhibernate.loader . criterialoader。在NHibernate.Impl.SessionImpl列表(ISessionImplementor会话)。列表(标准，IList结果)在NHibernate.Impl.CriteriaImpl。在nhibernate中列出(结果)。ListU nhibernate.iqueryover 1. NHibernate.Criterion.QueryOver”。ListU TimeTableRepo。cs:线在TimeTableService 47。cs:43 InnerException行:系统。FormatException消息=输入字符串'16:00:00'格式不正确。在NHibernate.Type.DateTimeType = NHibernate加:来源。获取(IDataReader rs, Int32索引)在NHibernate.Type.NullableType。nhibernate . type . NullSafeGet(IDataReader rs, String name)。NullSafeGet(IDataReader rs, String[]名称，ISessionImplementor会话，对象所有者)在NHibernate.Type.AbstractType。水合物(IDataReader rs, String[] names, ISessionImplementor session，对象所有者)。在nhibernate . load . loader . loader . loader上的水合物(IDataReader rs、对象id、对象obj、ILoadable rootLoadable、字符串[][]后缀edpropertycolumns、Boolean allProperties、ISessionImplementor会话)。LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key key key key key key, String rowIdAlias, LockMode, ILoadable rootPersister, ISessionImplementor session)。InstanceNotYetLoaded(IDataReader dr、int32i、ILoadable persister、EntityKey、LockMode、String rowIdAlias、EntityKey optionalObjectKey、Object optionalObject、IList hydratedObjects、ISessionImplementor session)位于NHibernate.Loader.Loader. loader . loader . loader . loader . loader。GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] LockMode[]， IList hydratedObjects, ISessionImplementor session)在NHibernate.Loader.Loader. loader . loader . loader。GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters QueryParameters, LockMode[] LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[]， Boolean returnproxy)位于NHibernate.Loader.Loader. loader . loader。DoQuery(ISessionImplementor会话，QueryParameters QueryParameters, Boolean returnproxy)位于NHibernate.Loader.Loader. loader。doqueryandinitializenonlazycollection (ISessionImplementor会话，QueryParameters QueryParameters, Boolean returnproxy)在NHibernate.Loader.Loader。DoList(ISessionImplementor会话，QueryParameters QueryParameters) InnerException: System。InvalidCastException消息=无法释放类型系统的对象。时间间隔”输入“System.IConvertible”。源= 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;
}

4 个解决方案

#1

Date/Time Support in NHibernate

日期/时间NHibernate的支持

#2

You should use the TimeSpan type on the .NET side of things instead of DateTime (since there is no Date) like Dotjoe said in his comment.

您应该在. net方面使用TimeSpan类型，而不是DateTime(因为没有日期)，就像Dotjoe在他的评论中说的那样。

#3

To put VahidN's answer explicitly, when you have an error message that says

当你有一个错误信息时，要显式地给出VahidN的答案。

Unable to cast object of type 'System.TimeSpan' to type 'System.IConvertible'.

It means the property you're trying to map to should be a TimeSpan instead of a DateTime. Same deal with DateTimeOffset.

这意味着要映射的属性应该是TimeSpan而不是DateTime。处理DateTimeOffset相同。

#4

A partial solution which I found useful is to use formula=CONVERT(DateTime, <ColumnName>) in the mapping.

我发现一个有用的部分解决方案是在映射中使用公式=CONVERT(DateTime， )。

The significant downside is that this is only useful for read-only access.

重要的缺点是，这只对只读访问有用。

#1