如何处理来自SQL Server 2008与NHibernate的TIME数据类型？ [英] How to deal with TIME datatype from SQL Server 2008 with NHibernate?

问题描述

我使用SQL Server 2008中的 TIME 数据类型，并且在使用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（）; 
引用（x => x.RequiredSettings）.Not.Nullable（）。Cascade.All（）; 
} 
 
 ///< summary> 
 /// MS Sql 2008日期类型。 
 ///< / summary> 
 ///< param name =map>< / param> 
 ///<返回>< /返回> 
 public static PropertyPart TimeDataType（this PropertyPart map）
 {
 return map.CustomSqlType（time）; 
} 
 
 public class TimeTableEvent 
 {
 public virtual int Id {get;私人设置} 
 public virtual DayOfWeek Day {get;组; } 
 public virtual DateTime StartTime {get;组; } 
 public virtual DateTime Endtime {get;组; } 
 public virtual TimeTable TimeTable {get;组; } 
 public virtual RequiredSetting RequiredSettings {get;组; } 
} 
  

我得到这个错误

NHibernate.Exceptions.GenericADOException被捕获

Message =无法执行查询

[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）]

位置参数：＃0> 14

[SQL：SELECT TOP（@ p0）this_.TimeTableEventId as TimeTabl1_15_1_，
this_ .Day as Day15_1_，this_.StartTime as StartTime15_1_，
this_.En dtime 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_。需要的设置ID = requiredse2_.RequiredSettingsId WHERE
this_.TimeTableId（@ p1）

$ p $在NHibernate.Loader.Loader.DoList的
（ISessionImplementor
会话，QueryParameters queryParameters）

在NHibernate.Loader.Loader.ListIgnoreQueryCache（ISessionImplementor
会话，QueryParameters queryParameters）
$ b $在NHibernate.Loader.Loader .list（ISessionImplementor
session，QueryParameters queryParameters，ISet 1 querySpaces，IType []
resultTypes）
$ b $在NHibernate.Loader.Criteria.CriteriaLoader.List ISessionImplementor
会话）

在NHibernate.Impl.SessionImpl.List（CriteriaImpl标准，
IList结果）

在NHibernate.Impl.CriteriaImpl.List（IList结果）
NHibernate.Impl.CriteriaImpl.List [T]（）

在NHibernate.Criterion.QueryOver 1.ListU

在NHibernate.Criterion.QueryOver`1.NHibernate.IQueryOver.ListU

在TimeTableRep o.cs：第47行

at TimeTableService.cs：第43行

InnerException：System.FormatException

Message =输入字符串'16：00：00'不是正确的
格式。

Source = NHibernate

NHibernate.Type.DateTimeType.Get（IDataReader rs，$在$ NHibernate.Type.NullableType.NullSafeGet（IDataReader rs，字符串名称）
在NHibernate.Type.NullableType.NullSafeGet（IDataReader rs，String [] b $ b Int32索引）
名称，ISessionImplementor会话，对象所有者）

在NHibernate.Type.AbstractType.Hydrate（IDataReader
rs，String []名称，ISessionImplementor会话，对象所有者）
at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate（IDataReader
rs，Object id，Object obj，ILoadable rootLoadable，String [] []
suffixedPropertyColumns，Boolean allProperties，ISessionImplementor
session ）

在NHibernate.Loader.Loader.LoadFromResultSet（IDataReader rs，In t32 i，
NHibernate.Loader.Loader中的
Object obj，String instanceClass，EntityKey key，String rowIdAlias，
LockMode lockMode，ILoadable rootPersister，ISessionImplementor
session） InstanceNotYetLoaded（IDataReader dr，Int32 i，
ILoadable persister，EntityKey key，LockMode lockMode，String
rowIdAlias，EntityKey optionalObjectKey，Object optionalObject，IList
hydratedObjects，ISessionImplementor会话）

在NHibernate.Loader.Loader.GetRow（IDataReader rs，
ILoadable [] persisters，EntityKey [] keys，Object optionalObject，
EntityKey optionalObjectKey，LockMode [] lockModes，IList
hydratedObjects，ISessionImplementor会话）
在NHibernate.Loader.Loader.GetRowFromResultSet（IDataReader resultSet，
ISessionImplementor会话，QueryParameters queryParameters，
LockMode [] lockModeArray，EntityKey optionalObjectKey，IList
hydratedObjects， EntityKey []键，布尔returnProxies）

a t NHibernate.Loader.Loader.Dojo（ISessionImplementor会话，
QueryParameters queryParameters，布尔returnProxies）
$ b $在NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections（ISessionImplementor
会话，QueryParameters queryParameters，布尔returnProxies ）

在NHibernate.Loader.Loader.DoList（ISessionImplementor
会话，QueryParameters queryParameters）

InnerException：System.InvalidCastException

Message = Unable to
System.TimeSpan类型的类型转换为类型System.IConvertible。
Source = mscorlib

  public List< TimeTableEvent> GetTimeTableEvents（Student student，List< int> timeTableIds）
 {
 TimeTableEvent tAlias = null; 
 
 List< TimeTableEvent> （限制.In（Projections.Property（（）=> tAlias.TimeTable.Id），timeTableIds））
（all_accounts = session.QueryOver< TimeTableEvent>（（）=> tAlias） .Fetch（r => r.RequiredSettings）.Eager 
 .TransformUsing（Transformers.DistinctRootEntity）
 .Take（QueryLimits.TimeTableEvents）
 .List< TimeTableEvent>（）ToList ; 
 
返回allEvents; 
 
  

解决方案

NHibernate中的日期/时间支持

I am using the TIME datatype from SQL Server 2008 and I am having some problems getting it to work with 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)

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

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

解决方案

Date/Time Support in NHibernate

这篇关于如何处理来自SQL Server 2008与NHibernate的TIME数据类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！