"TIMESTAMP WITH TIME ZONE" <--> DateTImeOffset mapping won't deliver the Zone part on INSERT commands (Entity Framework + Oracle)

Question

I use EF (EDMX model - DB first) to map "TIMESTAMP WITH TIME ZONE" to a DateTimeOffset. when I commit the DateTimeOffset to Oracle, the Zone part is saved incorrectly.

So if using the model, for example, to insert the value 29/02/2012 10:10:10 +04:00, the value that is actually stored in Oracle is 29/02/2012 10:10:10 +02:00 (assuming +02:00 is local zone) Note that the mappings works just fine when querying the data. Only INSERT (via ObjectContext.SaveChanges()) is broken...

I've debugged into the "Oracle.DataAccess.dll" ( using ILSpy :) ) and found that the mapping code for EF omits the zone (the "Oracle Data Provider" passes the DateTimeOffset.DateTime only).

Does anyone know a workaround?

Thanks in advance Eli

BTW: I am using .net4, EF4, Oracle 11g, ODAC 11.2 Release 4 (11.2.0.3.0)

Calices

Answer 1

You could try to dynamicly set the Session Time Zone, which "takes effect when a TIMESTAMP value is converted to the TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE datatype".

...a terrible hack of course, also because you cannot execute alter session directly through SQL. You'd have to use something like

begin DBMS_UTITLITY.EXEC_DDL_STATEMENT ('Alter Session Set TIME_ZONE = ''+04:00'''); end;

Nice

Answer 2

Oracle admitted this was a bug https://community.oracle.com/thread/2360615?tstart=0. And they sad it had been fixed in bug 13851978. But my test on oracle client on 11.2.0.3.0 is still failed.

On solution is as @HAL 9000 suggested setting the session's time zone with the hours and minutes in time span of DatetimeOffset before saving to database. But this won't work if there are multiple DatetimeOffset properties in one object and these properties have different timespans inside Datetimeoffset.

Another alternative is replacing ODP.NET with 3rd-party data provider such as Devart's DotConnect(I've tested this, it works for me). There is a comparison in stackoverflow about different data providers https://mcmap.net/q/752050/-comparison-of-entity-framework-compatible-providers-for-oracle.

Chloechloette

Answer 3

Storing offsets in the database might not hold good for Daylight savings offset Management. It will be a good practice to always use timezone names over offset values where the purpose is unaffected.

--To store actual time and timezone value
to_timestamp_tz('10-SEP-2014 01:40:00.000000000 US/Pacific','DD-MON-YYYY HH24:MI:SS.FF9 TZR')

--To store actual time at timezone converted to UTC timezone value for uniformity
to_timestamp_tz('10-SEP-2014 01:40:00.000000000 US/Pacific','DD-MON-YYYY HH24:MI:SS.FF9 TZR') at time zone 'UTC'

Blip