Whenever I use an OracleDataReader and it returns a column of Timestamp(6) with Time Zone, if I use reader.GetFieldValue<object>(0) to retrieve the value, it gets returned as a DateTime instead of a DateTimeOffset and I lose the Time Zone information.
Looking at some decompiled source of the latest Oracle.ManagedDataAccess.Client, it seems there's a HashTable in OracleTypeMapper which controls the mapping between Oracle and .Net Types called m_OraToNET. The following lines add the mappings for Oracle Time Zone types:
m_OraToNET.Add(OraType.ORA_TIMESTAMP_TZ, typeof(DateTime));
m_OraToNET.Add(OraType.ORA_TIMESTAMP_TZ_DTY, typeof(DateTime));
m_OraToNET.Add(OraType.ORA_TIMESTAMP_LTZ, typeof(DateTime));
m_OraToNET.Add(OraType.ORA_TIMESTAMP_LTZ_DTY, typeof(DateTime));
Shouldn't the TZ types map to DateTimeOffset rather than DateTime? This would be in line with Oracle documentation (which also states the LTZ types will map to DateTime which matches the above).
I believe the above, possibly incorrect, mapping is causing problems with EntityFramework when using DateTimeOffset/Timestamp with TZ columns with a StoreGeneratedPattern of Computed as the Server Generated values are returned using an OracleDataReader. I've posted about that problem on StackOverflow here: c# - Should it be possible to use a StoreGeneratedPattern of Computed on a DateTimeOffset column with EntityFramework? -…
Is anyone able to confirm whether the above mapping is correct or not?
Cheers, Pete