How to deal with TIME datatype from SQL Server 2008 with NHibernate?

Question

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

Answer 1

Date/Time Support in NHibernate

Answer 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.

Answer 3

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

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.

Answer 4

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

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