I'm using LLBLGen PRO v5.2 with Oracle 11g database (ODP.Net). One of the table columns has type TIMESTAMP(0) WITH TIME ZONE. Once I create entity definition for the table the said column has type DateTime and would not show the time zone information.

Is there any way to make sure timezone information is present when data is pulled?

ODP.NET converts the value in oracle to a DateTime instance, which either converts it to local time or UTC. the Timezone information itself is lost.

ODP.NET can return the value in an ODP.NET specific type, OracleTimeStampTZ, but as that type is located in ODP.NET, it's not a type supported by mappings by default. Using a type converter sadly won't work, as the value returned by the datareader is already a DateTime instance.

ODP.NET can return an OracleTimeStampTZ but only through an OracleDataAdapter, as it has a save type mapping flag on that class alone. It's also a one-flag-for-all setting so all values will be converted to ODP.NET native types and it's not really useful anyway as we don't use the OracleDataAdapter for query fetches and there's no other flag (that we know of) that makes this automatic. The OracleDataReader (we use it through the interface IDbDataReader and IDataReader) has a specific method to obtain an OracleTimeStampTZ but we use generic code to obtain the values, so we use the GetDateTime() field not the oracle specific method.

Otis, Thank you for confirming this part.

Old thread I know but couldn't you use OracleDataReader.GetDateTimeOffset, I know its not part of dbdatareader but neither is sqldatareader.getdatetimeoffset and I assume you are using that?

TomDog wrote:

Old thread I know but couldn't you use OracleDataAdapter.GetDateTimeOffset, I know its not part of dbdatareader but neither is sqldatareader.getdatetimeoffset and I assume you are using that?

No we don't use that, the datetimeoffset value is returned as-is by the datareader as the column value. We simply call GetValues(array) for entities and GetValue(ordinal) for DateTimeOffset (And GetInt32(ordinal) for int32's etc.) in projections. In some projects we also use GetValues(array). No ado.net provider specific methods are called.

(See DataReaderProjectionRow.GetDateTimeOffset)

Otis wrote:

We simply call GetValues(array) for entities and GetValue(ordinal) for DateTimeOffset (And GetInt32(ordinal) for int32's etc.) in projections.

Fair enough, though from looking at the ODP code I can see that if OracleCommand.UseEdmMapping is set true then OracleDataReader.GetValue will return a DateTimeOffset for a TIMESTAMP field if so mapped.

Ah that's something we could use. Tho it might be it also turns NUMBER(10,0) into an int32 (as 'edm' likely means EF), and normal ODPNET returns an int64 for that type... Will have to check it.

Otis wrote:

Ah that's something we could use. Tho it might be it also turns NUMBER(10,0) into an int32 (as 'edm' likely means EF), and normal ODPNET returns an int64 for that type... Will have to check it.

Yes according to entityEDMmapping it does. To override that config settings could be used ala entityDataTypeMapping but alas it doesn't appear you can do it in code like you can for other settings with OracleConfigurationClass, so I don't know how type mappings are done in Entity Framework Core... - column annotations?

To my knowledge they're hard-coded. But I'll see if it can be made optional, i.e. switching a setting on will use the EDM mappings (Number(10,0) to int32 for instance), and this will also open the door for supporting efcore on oracle in our designer (we just use 2 tables for mappings based on the setting which is switched on by default for ef core). At runtime, the setting then has to be set through the configuration settings in our own framework.

EF Core uses these default mappings, and they differ a bit from the EDM mappings.

Switching on the EDM mappings at the command level using the property might work for timezone fields indeed, but also will make the mappings change for number() fields, and they changed the defaults along the way (as usual with oracle, it's unclear what the defaults are if you pull the latest odpnet from nuget). With an EDM (edmx file) you can configure these yourself, but in our framework there's no such thing so the defaults apply.

We'll have to think about what the best route is for this. (as we need different mapping tables in the driver, and something to signal the DQE if a setting should be set)

Any further progress on this? I would love to see support for DateTimeOffset as I use a lot of these, and I have to support both SQL Server and Oracle. Currently I store the date/time and timezone offset in two separate columns, and use custom templates to combine them into a DateTimeOffset value

Using EDM mappings wasn't a good solution due to the other changes in types, so we couldn't fix this.

Thanks for the update.