We recently migrated from nHibernate v4.x to v5.3.9 and we started to have problems with date time fields. After long analysis I find out that problem caused by DateTime.MaxValue which we using for no date value. Before migration the date was concatenated and stored to database without miliseconds. After upgrade the value is stored in the Oracle DB but reading of date time stamp field is failing for Oracle but not for Microsoft SQL server. The error stack trace:
2021-10-22 14:23:05,141 [9] ERROR EafManagement.Core.NHibernateExtensions - Error during transaction commit
NHibernate.Exceptions.GenericADOException: could not execute query
[ select id1_12_,isdeleted2_12_,islocked3_12_,uuid4_12_,creationdate5_12_,isretired6_12_,retiredate7_12_,........... where rownum <=1 ]
Name:p1 - Value:Test Name:p2 - Value:1 Name:p3 - Value:0 Name:p4 - Value:0
[SQL: select id1_12_,isdeleted2_12_,islocked3_12_,uuid4_12_,creationdate5_12_,isretired6_12_,retiredate7_12_,........ where rownum <=1] ---> System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime.
at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)
at System.DateTime..ctor(Int32 year, Int32 month, Int32 day, Int32 hour, Int32 minute, Int32 second, Int32 millisecond)
at Oracle.ManagedDataAccess.Types.DateTimeConv.ToDateTime(Byte[] byteRep, Boolean isNotTimeStampTZ, Int32 offset, Int32 length)
at Oracle.ManagedDataAccess.Client.OracleDataReader.GetDateTime(Int32 i)
at Oracle.ManagedDataAccess.Client.OracleDataReader.GetValue(Int32 i)
at NHibernate.Type.AbstractDateTimeType.GetDateTime(DbDataReader rs, Int32 index, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Type\AbstractDateTimeType.cs:line 80
at NHibernate.Type.AbstractDateTimeType.Get(DbDataReader rs, Int32 index, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Type\AbstractDateTimeType.cs:line 59
at NHibernate.Type.NullableType.NullSafeGet(DbDataReader rs, String name, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Type\NullableType.cs:line 235
at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(DbDataReader rs, Object id, Object obj, String[][] suffixedPropertyColumns, ISet`1 fetchedLazyProperties, Boolean allProperties, Int32[] indexes, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:line 2848
at NHibernate.Persister.Entity.LoadableExtensions.Hydrate(ILoadable loadable, DbDataReader rs, Object id, Object obj, String[][] suffixedPropertyColumns, ISet`1 fetchedLazyProperties, Boolean allProperties, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Persister\Entity\ILoadable.cs:line 101
at NHibernate.Loader.Loader.LoadFromResultSet(DbDataReader rs, Int32 i, Object obj, ILoadable persister, EntityKey key, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1301
at NHibernate.Loader.Loader.InstanceNotYetLoaded(DbDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1164
at NHibernate.Loader.Loader.GetRow(DbDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session, Boolean mustLoadMissingEntity, Action`2 cacheBatchingHandler) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1041
at NHibernate.Loader.Loader.GetRowFromResultSet(DbDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder, Action`2 cacheBatchingHandler) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 405
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 558
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 303
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1972
--- End of inner exception stack trace ---
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1981
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Loader.cs:line 1837
at NHibernate.Loader.Hql.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Loader\Hql\QueryLoader.cs:line 325
at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Hql\Ast\ANTLR\QueryTranslatorImpl.cs:line 119
at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Engine\Query\HQLQueryPlan.cs:line 116
at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results, Object filterConnection) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Impl\SessionImpl.cs:line 559
at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Impl\SessionImpl.cs:line 524
at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Impl\AbstractSessionImpl.cs:line 172
at NHibernate.Impl.AbstractQueryImpl2.List() in D:\BuildAgent\work\30546188361a242\src\NHibernate\Impl\AbstractQueryImpl2.cs:line 78
at NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Linq\DefaultQueryProvider.cs:line 226
at NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Linq\DefaultQueryProvider.cs:line 96
at NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) in D:\BuildAgent\work\30546188361a242\src\NHibernate\Linq\DefaultQueryProvider.cs:line 101
at .....
This problem obviously caused by changing accuracy of datetime stamp mentioned in nHibernate possible breaking changes at line 1421:
Trying to read century value in the Oracle DB from stored value is not possible not only with application but also with Oracle SQL Developer tool. Format without century can be returned and it shows value as 01.01.00 00:00:00,00000000. So it sounds that Oracle is rounding value to the higher value and then it fails on returning later. This is affecting production code and conversion to null date time needs time but solution is required now since downgrade is not great option.
Question: how to configure nHibernate back to lower precision as it was before for v5.x? Any other possible safe quick fixes before we fully convert into null date time stamp field value?
Real DateTime.MaxValue = 12/31/9999 23:59:59.999999999
Microsoft SQL DB server values stored to DB: Before upgrade: 12/31/9999 23:59:59.000 After upgrade: 12/31/9999 23:59:59.997
Oracle DB server values stored to DB: Before upgrade: 31.12.99 23:59:59,000000 (which shown as year 9999 after formatting date) After upgrade shown as: 01.01.00 00:00:00,0000000
Since I did not received any suggestions, I found an option to modify mapping by adding introduced new type DateTimeNoMs mapping in the nHibernate mapping file:
<property name="RetireDate" column="RETIREDATE" not-null="false" type="DateTimeNoMs"/>
This eliminates millisecond accuracy for time stamps causing problems on Oracle DB when DateTime.MaxValue is used.
NHibernate Type = DateTimeNoMs
.NET Type = System.DateTime
Database Type = DbType.DateTime / DbType.DateTime2
Remarks = type="DateTimeNoMs" must be specified. Ignores fractional seconds. Available since NHibernate v5.0.