I got a application where I use DateTime.MaxValue to indicate that a entry have not been deleted yet. The problem is that PostgreSQL (or npgsql or nhibernate) rounds 9999-12-31 23:59:59.999999 to (10000-01-01 00:00:00) when the date/time is stored in postgres.
Reading that value back from the database results in a InvalidCastException.
How can I make nhibernate/npgsql read those values and translate them to DateTime.MaxValue?
There are other applications reading/writing to the database, which makes any other fixes impossible.
*Update, Complete stack trace *
NHibernate.Exceptions.GenericADOException: could not load an entity: [Gate.Users.User#1][SQL: SELECT user0_.id as id15_1_, user0_.site_id as site2_15_1_, user0_.first_name as first3_15_1_, user0_.last_name as last4_15_1_, user0_.can_delete as can5_15_1_, user0_.email as email15_1_, user0_.department as department15_1_, user0_.title as title15_1_, user0_.status as status15_1_, user0_.created_at as created10_15_1_, user0_.updated_at as updated11_15_1_, user0_.timezone as timezone15_1_, user0_.middle_names as middle13_15_1_, user0_.name_prefix as name14_15_1_, user0_.name_suffix as name15_15_1_, user0_.dob as dob15_1_, user0_.removed_at as removed17_15_1_, user0_.proxy_media as proxy18_15_1_, extension1_.id as id13_0_, extension1_.site_id as site2_13_0_, extension1_.address as address13_0_, extension1_.can_delete as can4_13_0_, extension1_.forward_all as forward5_13_0_, extension1_.forward_busy as forward6_13_0_, extension1_.forward_no_answer as forward7_13_0_, extension1_.related_type as related8_13_0_, extension1_.related_id as related9_13_0_, extension1_.e164 as e10_13_0_, extension1_.outbound_e164 as outbound11_13_0_, extension1_.custom_e164 as custom12_13_0_, extension1_.removed_at as removed13_13_0_, extension1_.number_series_id as number14_13_0_, extension1_.timeout as timeout13_0_ FROM users user0_ left outer join extensions extension1_ on user0_.id=extension1_.id WHERE user0_.id=?] ---> System.InvalidCastException: Specified cast is not valid.
at NpgsqlTypes.NpgsqlTimeStamp.op_Implicit(NpgsqlTimeStamp timestamp)
at NpgsqlTypes.NpgsqlTypesHelper.<TypeInfoList>b__a(Object timestamp)
at NpgsqlTypes.NpgsqlBackendTypeInfo.ConvertToFrameworkType(Object providerValue)
at Npgsql.ForwardsOnlyDataReader.GetValue(Int32 Index)
at Npgsql.NpgsqlDataReader.get_Item(String name)
at NHibernate.Driver.NHybridDataReader.get_Item(String name) in d:\CSharp\NH\nhibernate\src\NHibernate\Driver\NHybridDataReader.cs:line 207
at Gate.Core.NHibernate.DateUserType.NullSafeGet(IDataReader rs, String[] names, Object owner) in C:\projects\c#\projects\MyProject\Server\Gate.Core.NHibernate\DateUserType.cs:line 39
at NHibernate.Type.CustomType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\nhibernate\src\NHibernate\Type\CustomType.cs:line 100
at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\nhibernate\src\NHibernate\Type\AbstractType.cs:line 131
at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String[][] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session) in d:\CSharp\NH\nhibernate\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:line 2505
at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 980
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) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 935
at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 867
at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 322
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 453
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 236
at NHibernate.Loader.Loader.LoadEntity(ISessionImplementor session, Object id, IType identifierType, Object optionalObject, String optionalEntityName, Object optionalIdentifier, IEntityPersister persister) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1392
--- End of inner exception stack trace ---
at NHibernate.Loader.Loader.LoadEntity(ISessionImplementor session, Object id, IType identifierType, Object optionalObject, String optionalEntityName, Object optionalIdentifier, IEntityPersister persister) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Loader.cs:line 1401
at NHibernate.Loader.Entity.AbstractEntityLoader.Load(ISessionImplementor session, Object id, Object optionalObject, Object optionalId) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Entity\AbstractEntityLoader.cs:line 42
at NHibernate.Loader.Entity.AbstractEntityLoader.Load(Object id, Object optionalObject, ISessionImplementor session) in d:\CSharp\NH\nhibernate\src\NHibernate\Loader\Entity\AbstractEntityLoader.cs:line 37
at NHibernate.Persister.Entity.AbstractEntityPersister.Load(Object id, Object optionalObject, LockMode lockMode, ISessionImplementor session) in d:\CSharp\NH\nhibernate\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:line 3436
at NHibernate.Event.Default.DefaultLoadEventListener.LoadFromDatasource(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\DefaultLoadEventListener.cs:line 342
at NHibernate.Event.Default.DefaultLoadEventListener.DoLoad(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\DefaultLoadEventListener.cs:line 320
at NHibernate.Event.Default.DefaultLoadEventListener.Load(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\DefaultLoadEventListener.cs:line 104
at NHibernate.Event.Default.DefaultLoadEventListener.ProxyOrLoad(LoadEvent event, IEntityPersister persister, EntityKey keyToLoad, LoadType options) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\DefaultLoadEventListener.cs:line 160
at NHibernate.Event.Default.DefaultLoadEventListener.OnLoad(LoadEvent event, LoadType loadType) in d:\CSharp\NH\nhibernate\src\NHibernate\Event\Default\DefaultLoadEventListener.cs:line 87
at NHibernate.Impl.SessionImpl.FireLoad(LoadEvent event, LoadType loadType) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 2457
at NHibernate.Impl.SessionImpl.Get(String entityName, Object id) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1336
at NHibernate.Impl.SessionImpl.Get(Type entityClass, Object id) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1275
at NHibernate.Impl.SessionImpl.Get[T](Object id) in d:\CSharp\NH\nhibernate\src\NHibernate\Impl\SessionImpl.cs:line 1259
at Gate.Core.NHibernate.Repository`2.<>c__DisplayClassd.<GetById>b__c() in C:\projects\c#\projects\MyProject\Server\Gate.Core.NHibernate\Repository.cs:line 151
at Gate.Core.NHibernate.Repository`2.ExceptionHandling[T](Func`1 action, String failedMessage) in C:\projects\c#\projects\MyProject\Server\Gate.Core.NHibernate\Repository.cs:line 46
That's a bad idea. Use null instead, it'll save a lot of pain.
If you still want to do it, look into IUserType for custom handling of DB values.