Share via

DateTime values supported by Oracle but not Entity Framework

  • Article

In scenario of OracleDatabase and entity framework with third party driver, In oracle database there might be some invalid date values (like 00-000-00) which cannot be accepted by Entity framework (system.datetime specifications doesn't meet with this value).

 

In msdn: you can find scope of System.datetime below

https://msdn.microsoft.com/en-us/library/system.datetime.aspx

Remarks

The DateTime value type represents dates and times with values ranging from 12:00:00 midnight, January 1, 0001 Anno Domini (Common Era) through 11:59:59 P.M., December 31, 9999 A.D. (C.E.)

Time values are measured in 100-nanosecond units called ticks, and a particular date is the number of ticks since 12:00 midnight, January 1, 0001 A.D. (C.E.) in the GregorianCalendar calendar. For example, a ticks value of 31241376000000000L represents the date, Friday, January 01, 0100 12:00:00 midnight. A DateTime value is always expressed in the context of an explicit or default calendar.

 

 

So as a workaround Here are some options:

 

-prevent your application to insert invalid format and try to insert null value instead of that

-change column type to string

-use stored procedure to get data back as string so that entity framework can accept these values.

One question left here, all these changes are on server side solutions (except first solution) and he is unable to apply any changes on server side, question is, is there any way of changing queries created by Entity framework or is there any way of pushing your custom query to entity framework?

 

Answer:

You can manipulate SQL produced by EF in couple of ways:

- In the SSDL, you can use a 'DefiningQuery' and convert datetime values to strings in a (Oracle) SQL fragment.

How And Why To Use DefiningQuery Element

https://blogs.microsoft.co.il/blogs/gilf/archive/2008/05/14/how-why-use-definingquery-element.aspx

DefiningQuery Element (EntityContainer SSDL)

https://msdn.microsoft.com/en-us/library/bb738450.aspx

- In the MSL, you can use a 'QueryView' to accomplish the same end using the Entity-SQL CAST operator.

QueryView Element (EntitySetMapping)

https://msdn.microsoft.com/en-us/library/cc716798.aspx

In V2 there is a new feature announced that allows you more advanced query rewriting

To issue queries directly to the backend through the EF, take a look at the sample code and helpers in the https://code.msdn.com/EFExtensions library

 

Now select is sorted out but how will you update this data? :)

Some help topics that might be useful for setting up a ModficationFunctionMapping:

Stored Procedure Support (Entity Framework)

How to: Define a Model with Modification Stored Procedures (Entity Framework)

 

Hope this helps

 

*Kagan Arca