Forum Stats

  • 3,874,444 Users
  • 2,266,734 Discussions
  • 7,911,851 Comments

Discussions

EF Core - Year, Month, and Day parameters describe an un-representable DateTime

User_CSGCS
User_CSGCS Member Posts: 2 Green Ribbon

Hello,

we are currently using the Entity Framework 3.1.11 and

facing an error while retrieving a field of type DATE in our Oracle Database

(Version Oracle Database 11g Release 11.2.0.4.0 - 64bit Production) .

The Problem is that in the DB some of the Dates have

as year the value 0 (zero) and .NET can't convert a Date like 01.01.0000 to

System.DateTime because the smallest available Date in .NET is 01.01.0001 which

leads the programm to throw the Error  

- System.ArgumentOutOfRangeException: Year, Month, and

Day parameters describe an un-representable DateTime -

To reproduce a DB entry with the year zero I used in

sqlplus the following statement:

update patient set birthday = date'0000-01-05' where

patient_id = 4; (or an insert with " date'0000-01-05'")

Does

someone knows how we could handle the DB entries with year zero dates that a

request to get all patients wouldn't fail because he can't convert one entry to

it's model?


The year zero shouldn't be valid in Oracle but it can be inserted into a date field. This article from 2008 is about the problem with the year zero and gives more examples http://rwijk.blogspot.com/2008/10/year-zero.html


The error message and stacktrace produced in a UnitTest is:

Message: 

  Test method GetByIdTest threw exception: 

  System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime.

 Stack Trace: 

  DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)

  DateTime.ctor(Int32 year, Int32 month, Int32 day, Int32 hour, Int32 minute, Int32 second, Int32 millisecond)

  DateTimeConv.ToDateTime(Byte[] byteRep, Boolean isNotTimeStampTZ, Int32 offset, Int32 length)

  OracleDataReader.GetDateTime(Int32 i)

  lambda_method(Closure , QueryContext , DbDataReader , ResultContext , Int32[] , ResultCoordinator )

  Enumerator.MoveNext()

  Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)

  Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)

  PatientDataAccess.Get(Int64 id, Boolean includeChildren) line 92

  PatientDataAccessTest.GetByIdTest() line 306


but when i catch the error with a "try - catch" block and lookup the stacktrace inside the exception it's a different one but the difference should be that we have more Information where the OracleDateReader is to find but anyways:

  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 Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()

  at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)

  at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)

  at CGM.Arztsysteme.M1X.DataAccess.EFDataAccess.PatientDataAccess.Get(Int64 id, Boolean includeChildren) in ...


This is my first question here and I hope that my problem is good explained and someone has a solution for me. Anyways thank you for reading my question.

If some Information is missing I will try to add them as fast as possible.

Thank you very much,

~ Jan

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,154 Employee

    In typical ODP.NET apps, Safe Type Mapping is used to ensure DB data is passed to .NET without error and without data loss.

    EF Core doesn't have access to the ODP.NET API. You will have to retrieve to perform the type conversion in your own code to eliminate the error.

  • User_CSGCS
    User_CSGCS Member Posts: 2 Green Ribbon

    Hello,

    I thought that the OracleDataProvider would be the same for the EF Core and ODP.NET Applications. So why is there a way to handle these errors in the OPD.NET Version but not in the EF Core Vesion when it seems that they use the same Data Provider. https://www.nuget.org/packages/Oracle.EntityFrameworkCore/3.19.80 -> "Oracle Data Provider for .NET (ODP.NET) Entity Framework Core for Oracle Database."

    Is there a way to fix these issues on your side?

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,154 Employee

    EF Core is an ORM that sits on top of ADO.NET providers.

    An ORM could expose data provider specific functionality, such as ODP.NET or SqlClient specific APIs. EF Core does not because one of its tenets is to allow developers create one code base for all DB providers and DB servers. They can then run (nearly) the same EF Core app against SQL Server as Oracle as MySQL as PostgreSQL, etc. Even the query language is unified if you use LINQ.

    Microsoft is the EF Core open source maintainer. They would need to make the change or, at least, approve such a change being added to it. It's doubtful that would occur as it would break the tenet.

    Your best bet is to handle the safe type mapping within your own code. You can check whether the Oracle DATE has a year 0. If so, convert the value to a string. In essence, that is what ODP.NET Safe Type Mapping is doing.

  • User_CSGCS
    User_CSGCS Member Posts: 2 Green Ribbon

    Thank you for your answer, but I don't see how it fits to the problem.

    As it currently stands, as far as I understand it, is that we have an oracle database with DATE columns that can hold values which are not valid as .Net DateTime.

    If we try to read entities with such mapped DateTime properties, ef core will call the oracle managed data access which in turn will fail to read the value from its own database because of the invalid year:

      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 Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()


    If we try to map it as any other data type or use a converter it still fails, because the oracle managed data access just tries to cast it from DateTime to the new type:

    Specified cast is not valid.

      at Oracle.ManagedDataAccess.Client.OracleDataReader.GetString(Int32 i)

      at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()



    So as far as I see it, the problem has nothing to do with ef core (neither concerns it), but with the oracle managed data access, that is unable to read data from its own DATE columns in these cases because it always seems to try to handle them as a DateTime. So the only one who can handle this problem is the oracle managed data access by checking the actual value before trying to create a DateTime from it. If you are unsure on what to do exactly in these cases you could maybe let the client configure it through the OracleDbContextOptionsBuilder and choose from options like return null, return DateTime.Min, throw exception and so on.


    I don't see a way for the client application to handle this problem on its own with a database that has over a hundred tables with lots and lots of DATE columns.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 3,154 Employee

    The Safe Type Mapping feature was created so that you could map the Oracle DATE to a .NET string.

    Another option we've provided is to use ODP.NET-specific data types in DataSet when you set OracleDataAdapter.ReturnProviderSpecificTypes=true.

    ODP.NET does not prevent EF Core from using these APIs. EF Core, by design, does not allow ODP.NET to have these APIs exposed.

    I filed an ER for you (33195201) for the ODP.NET dev team to evaluate the feature.

    One way you could address the problem currently is to create a PL/SQL trigger, stored procedure, or view that manipulates the date data so that it doesn't return any value to EF Core that can't fit into a System.DateTime.