After upgrade to nHibernate v5.x causing DateTime errors on Oracle DB-CodePudding

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:

Oracle 9g dialects now use timestamp(7) for all date time types, instead of timestamp(4).

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

CodePudding user response：

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.