Column Compatibility Mapping Oracle

[spaa33]

Is there a current Compatibility Table for Oracle Data Types to .NET Entity Column Data Types? Does this already exist in the forums or on the Visual Studio help files?

I'd like to build my Oracle database with Compatible Data Types for easy mapping in the designer or by manual editing of the CSDL/SSDL. I'd like a reference for developing my database.

I noticed that some data mappings from oracle to entity columns have been added and may change over time(e.g Int16) and that some other datatypes may not be supported?

What I'd be looking for is something like:
(don't expect this to be correct for current devart compatibility)
[.NET: boolean] = [oracle: NUMBER(1)] Mapping = Designer and Manual
[.NET: boolean] = [oracle: BYTE(1)] Mapping = Manual Only
[.NET: Int32] = [oracle: NUMBER(*,0)] Mapping = Designer and Manual
[.NET: Int64] = [oracle: NUMBER(*,0)] Mapping = Designer and Manual
[.NET: Double] = [oracle: NUMBER(*,*)] Mapping = Designer and Manual
[.NET: Double] = [oracle: FLOAT] Mapping = Designer and Manual
[.NET: String] = [oracle: VARCHAR2(*)] Mapping = Designer and Manual
[.NET: String] = [oracle: NVARCHAR2(*)] Mapping = Manual Only
[.NET: Guid] = [oracle: RAW(16)] Mapping = Manual Only
[.NET: DateTimeOffset] = Unsupported

Examples needed
[.NET: Binary]
[.NET: Boolean]
[.NET: Byte]
[.NET: DateTime]
[.NET: DateTimeOffset]
[.NET: Decimal]
[.NET: Double]
[.NET: Guid]
[.NET: Int16]
[.NET: Int32]
[.NET: Int64]
[.NET: SByte]
[.NET: Single]
[.NET: String]
[.NET: Time]

I'd also like to know if there is a "preferred" oracle type that might be more compatible or closer to the matching data types that could provide the best performance by requiring the least conversion computation needed.

Regards,
DS

[spaa33]

Is trial and error the only way to find out what entity framework data types are supported in this oracle data provider?

[Shalex]

Oracle to .NET types mapping table (Entity Data Model Wizard):

[Oracle:CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, NCLOB, LONG, ROWID, UROWID, XMLTYPE, INTERVAL YEAR TO MONTH] -> [SSDL:the same] -> [CSDL:String] -> [.NET:System.String]
[Oracle:RAW, LONG RAW, BLOB] -> [SSDL:the same] -> [CSDL:Binary] -> [.NET:System.Byte[]]
[Oracle:RAW(16)] -> [SSDL:guid] -> [CSDL:Guid] -> [.NET:System.Guid]
[Oracle:DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE] -> [SSDL:the same] -> [CSDL:DateTime] -> [.NET:System.DateTime]
[Oracle:INTERVAL DAY TO SECOND] -> [SSDL:the same] -> [CSDL:Time] -> [.NET:System.TimeSpan]
[Oracle:FLOAT, REAL, BINARY_FLOAT, BINARY_DOUBLE ] -> [SSDL:the same] -> [CSDL:Decimal] -> [.NET:System.Decimal]
[Oracle:NUMBER(1)] -> [SSDL:bool] -> [CSDL:Boolean] -> [.NET:System.Boolean]
[Oracle:NUMBER(2)..NUMBER(9)*] -> [SSDL:int] -> [CSDL:Int32] -> [.NET:System.Int32]
[Oracle:NUMBER(10)..NUMBER(18)*] -> [SSDL:int64] -> [CSDL:Int64] -> [.NET:System.Int64]
[Oracle:NUMBER(x, 0)..NUMBER(x, 15)*] -> [SSDL:double] -> [CSDL:Double] -> [.NET:System.Double]
[Oracle:other NUMBERs] -> [SSDL:decimal] -> [CSDL:Decimal] -> [.NET:System.Decimal]

* the negative scale cases are taken into account

.NET to Oracle types default mapping table:

[.NET:System.Byte[]] -> [CSDL:Binary] -> [SSDL:BLOB] -> [Oracle:BLOB]
[.NET:System.Boolean] -> [CSDL:Boolean] -> [SSDL:bool] -> [Oracle:NUMBER(1)]
[.NET:System.DateTime] -> [CSDL:DateTime] -> [SSDL:TIMESTAMP ] -> [Oracle:TIMESTAMP]
[.NET:System.DateTimeOffset] -> [CSDL:DateTimeOffset] -> [SSDL:datetimeoffset] -> [Oracle:TIMESTAMP WITH TIME ZONE]
[.NET:System.Decimal] -> [CSDL:Decimal] -> [SSDL:decimal] -> [Oracle:NUMBER]
[.NET:System.Double] -> [CSDL:Double] -> [SSDL:double] -> [Oracle:NUMBER]
[.NET:System.Guid] -> [CSDL:Guid] -> [SSDL:guid] -> [Oracle:RAW(16)]
[.NET:System.Int16] -> [CSDL:Int16] -> [SSDL:int16] -> [Oracle:NUMBER]
[.NET:System.Int32] -> [CSDL:Int32] -> [SSDL:int] -> [Oracle:NUMBER]
[.NET:System.Int64] -> [CSDL:Int64] -> [SSDL:int64] -> [Oracle:NUMBER]
[.NET:System.Single] -> [CSDL:Single] -> [SSDL:single] -> [Oracle:NUMBER]
[.NET:System.String] -> [CSDL:String] -> [SSDL:VARCHAR2] -> [Oracle:VARCHAR2]
[.NET:System.TimeSpan] -> [CSDL:Time] -> [SSDL:INTERVAL DAY TO SECOND] -> [Oracle:INTERVAL DAY TO SECOND]

The Byte and SByte types are not supported at the moment, but they will be supported with the [Oracle:NUMBER] mapping in the nearest future.

[spaa33]

Very useful information. Thank You.

[AndreyR]

Byte and SByte types are now supported through Number mapping.

[piers7]

Oracle to .NET types mapping table (Entity Data Model Wizard):

[Oracle:FLOAT, REAL, BINARY_FLOAT, BINARY_DOUBLE ] -> [SSDL:the same] -> [CSDL:Decimal] -> [.NET:System.Decimal]

Why on earth would you map BINARY_FLOAT / BINARY_DOUBLE to decimal, and not to float/double respectively? That's the whole point of using the 4/8 byte IEEE standard types, surely. Even *Oracle* gets this mapping right.

[AndreyR]

You can change the type of the BINARY_FLOAT property to "single" in SSDL, and to "System.Single" in CSDL part of the model.
As for the BINARY_DOUBLE property, the mappings should be "double" and "System.Double", respectively.
I have checked this approach, it worked for me.
If you are using a Devart Entity model, the Store properties can be edited in Model Explorer->Tables/Views->[Your table]->Columns.
If you are using Microsoft Entity models, you should open the model in XML Editor and make changes in it.

[piers7]

Thanks for that. It does indeed work if I hack the SSDL, but that's a bug surely. Either the initial model generation got it wrong (and should have kicked out a float [ie 'single']), or (probably preferable) there should be a type mapping from BINARY_FLOAT to float (and not to decimal).

[piers7]

This SSDL \ CSDL hack only appears to work using non-direct mode. It works for writes, but any time I try and read the data back I get:

ORA-03115: unsupported network datatype or representation

This happens using the following SSDL \ CSDL combos:
BINARY_FLOAT \ Decimal (what 'update from database' creates)
decimal \ Decimal
double \ Double
single \ Single

If I remove the BINARY_FLOAT column from the model completely (delete from SSDL and CSDL) it works just fine.

If I change the driver to non-direct mode (Direct=false and specify a Home) it works fine

[AndreyR]

We plan to add support for the BINARY_FLOAT and BINARY_DOUBLE data types in the Direct mode in future versions. Unfortunately, I don't have a definite timeframe for this functionality to be implemented.
We will investigate the possibility to perform a default mapping to Single/Double, respectively.
I will let you know about the results of our investigation.

[Shalex]

The behaviour of EDM Wizard is changed: the BINARY_FLOAT and BINARY_DOUBLE data types will be mapped to Single and Double correspondingly starting from the next build of dotConnect for Oracle. We will post here when it is available for download.

[Shalex]

New version of dotConnect for Oracle 6.60 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).