升级到nHibernate v5.x后，在Oracle上导致DateTime错误
升级到nHibernate v5.x后，在Oracle上导致DateTime错误
提问于 2021-10-25 06:00:00
我们最近从nHibernate v4.x迁移到v5.3.9，并且开始出现日期时间字段的问题。经过长时间的分析，找出了由DateTime.MaxValue引起的问题，我们使用的是不需要日期的值。在迁移之前，日期被连在一起并存储到数据库中，没有毫秒。升级后，该值存储在Oracle DB中，但对Oracle来说，读取日期时间戳字段失败，而对于Microsoft服务器则不然。错误堆栈跟踪：
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 .....这个问题显然是由于更改nHibernate可能的破变中提到的1421行中提到的日期时间戳的准确性而引起的：
- Oracle 9g+方言现在对所有日期时间类型使用时间戳(7)，而不是时间戳(4)。
尝试从存储的值中读取Oracle DB中的世纪值不仅在应用程序中是不可能的，而且使用Oracle SQL Developer工具也是不可能的。可以返回没有世纪的格式，它显示值为01.01.00 :00:000000000000。因此，听起来Oracle将值舍入到更高的值，然后在稍后返回时失败。这影响到生产代码，转换为空日期时间需要时间，但现在需要解决方案，因为降级不是很好的选择。
问题：如何将nHibernate配置回v5.x之前的较低精度？在完全转换为空日期时间戳字段值之前，还有其他可能的安全快速修复吗？
真实DateTime.MaxValue = 12/31/9999 23:59:59.999999999
存储到数据库的Microsoft服务器值:升级前: 12/31/9999 23:59:59.000升级后: 12/31/9999 23:59:59.997
Oracle DB服务器值存储到DB:升级前: 31.12.99 23:59:59:59000000(在格式化日期后显示为9999年)升级后显示为: 01.01.00 :00:0000000
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-10-27 06:02:44
由于没有收到任何建议，我找到了一个通过在类型DateTimeNoMs映射映射文件中添加引入的新nHibernate来修改映射的选项：
<property name="RetireDate" column="RETIREDATE" not-null="false" type="DateTimeNoMs"/>这消除了时间戳在使用DateTime.MaxValue时对Oracle造成问题的毫秒精度。
NHibernate型= DateTimeNoMs
.NET型= System.DateTime
数据库类型= DbType.DateTime / DbType.DateTime2
备注=必须指定type="DateTimeNoMs"。忽略小数秒。可从NHibernate v5.0开始使用。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69703441
复制相关文章
相似问题
腾讯云开发者
