All geek questions in one place

How to work with TIME data type with SQL Server 2008 using NHibernate?

I am using the TIME data type from SQL Server 2008, and I am having some problems with it working 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 = request failed
[SELECT TOP (@ p0) this_.TimeTableEventId like TimeTabl1_15_1_, this_.Day as Day15_1_, this_.StartTime like StartTime15_1_, this_.Endtime like Endtime15_1_, this_.TimeTableId like TimeTabl5_15_1_, this_.RequiredSettingsId like Required6_15_1_, requiredse2_.RequiredSettingsId as Required1_10_0_, requiredse2_ .BackgroundColor as Backgrou2_10_0_, requiredse2_.Title as Title10_0_ FROM TimeTableEvents this_ internal join Required settings requiredse2_ on this_.RequiredSettingsId = requiredse2_.RequiredSettingsId WHERE this_.TimeTableId in (@ p1)]
Positional Parameters: # 0> 14
[SQL: SELECT TOP (@ p0) this_.TimeTableEventId how TimeTabl1_15_1_, this_.Day as Day15_1_, this_.StartTime how StartTime15_1_, this_.Endtime how Endtime15_1_, this_.TimeTableId how TimeTabl5_15_1_, this_.RequiredSettingsId how Required6_15_1_, requiredse2_.RequiredSettingsId as Required1_10_0_ , requiredse2_.BackgroundColor as Backgrou2_10_0_, requiredse2_.Title as Title10_0_ FROM TimeTableEvents this_ internal join Required settings requiredse2_ on this_.RequiredSettingsId = requiredse2_.RequiredSettingsId WHERE this_.TimeTableId in (p
Source = NHibernate
SqlString = SELECT TOP (@ p0) this_.TimeTableEventId as TimeTabl1_15_1_, this_.Day as Day15_1_, this_.StartTime as StartTime15_1_, this_.Endtime like Endtime15_1_, this_.TimeTableId as TimeTabl5_15_1_, this_.RequiredSettingsId like Required6_15_1_, requiredse2_.RequiredSettingsId as Required1_10_0_, requiredse2_.BackgroundColor as Backgrou2_10_0_, requiredse2_.Title as Title10_0_ FROM TimeTableEvents this_ internal join Required settings requiredse2_ on this_.RequiredSettingsId = requiredse2_.RequiredSettingsId WHERE this_.TimeTableId in (@ p1

Stacktrace:
in NHibernate.Loader.Loader.DoList (ISessionImplementor session, QueryParameters queryParameters)
in NHibernate.Loader.Loader.ListIgnoreQueryCache (ISessionImplementor session, QueryParameters queryParameters)
in NHibernate.Loader.Loader.List (ISessionImplementor session, QueryParameters queryParameters, ISet 1 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.QueryOver 1 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.QueryOver 1 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.QueryOver 1.ListU
at NHibernate.Criterion.QueryOver`1.NHibernate.IQueryOver.ListU
in TimeTableRepo.cs: line 47
in TimeTableService.cs: line 43
InnerException: System.FormatException
Message = The input line '16: 00: 00 'does not match the format rule.
Source = NHibernate
Stacktrace:
at NHibernate.Type.DateTimeType.Get (IDataReader rs, Int32 Index)
in NHibernate.Type.NullableType.NullSafeGet (IDataReader rs, String name)
in NHibernate.Type.NullableType.NullSafeGet (IDataReader rs, String [] names, ISessionImplementor session, object owner)
in NHibernate.Type.AbstractType.Hydrate (IDataReader rs, String [], ISessionImplementor session, object owner)
in NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate (IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String [] [] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session)
in NHibernate.Loader.Loader.LoadFromResultSet (IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session)
in NHibernate.Loader.Loader.InstanceNotYetLoaded (IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session)
in NHibernate.Loader.Loader.GetRow (IDataReader rs, ILoadable [] persisters, EntityKey [], Object optionObject, EntityKey optionalObjectKey, LockMode [] lockModes, IList hydratedObjects, ISessionImplementor session)
in NHibernate.Loader.Loader.GetRowFromResultSet (IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode [] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey [], Boolean returnProxies)
on NHibernate.Loader.Loader.DoQuery (ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
in NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections (ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
in NHibernate.Loader.Loader.DoList (ISessionImplementor session, QueryParameters queryParameters)
InnerException: System.InvalidCastException
Message = Unable to create an object of type "System.TimeSpan" to enter "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; }
+3
source share
4 answers

You should use the TimeSpan type on the .NET side and not DateTime (since there is no date), as Dotjoe said in his comment.

+1
source

To send VahidN a response explicitly when you have an error message that says

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

This means that the property you are trying to map should be TimeSpan instead of DateTime. The same goes for DateTimeOffset.

+1
source

A common solution that I found useful is to use formula=CONVERT(DateTime, <ColumnName>) in the display.

A significant drawback is that it is useful only for read-only access.

0
source

Source: https://habr.com/ru/post/898718/

More articles:


All Articles