Column Compatibility Mapping Oracle

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
spaa33
Posts: 3
Joined: Thu 20 Aug 2009 17:38

Column Compatibility Mapping Oracle

Post by spaa33 » Thu 20 Aug 2009 18:10

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
Posts: 3
Joined: Thu 20 Aug 2009 17:38

Trial and Error?

Post by spaa33 » Mon 24 Aug 2009 16:54

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

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Tue 25 Aug 2009 14:15

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
Posts: 3
Joined: Thu 20 Aug 2009 17:38

Post by spaa33 » Tue 25 Aug 2009 15:55

Very useful information. Thank You.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 16 Sep 2009 08:20

Byte and SByte types are now supported through Number mapping.

piers7
Posts: 5
Joined: Wed 29 Jun 2011 07:12
Contact:

BINARY_FLOAT / BINARY_DOUBLE mapped wrong

Post by piers7 » Wed 29 Jun 2011 07:19

Shalex wrote: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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 29 Jun 2011 15:26

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
Posts: 5
Joined: Wed 29 Jun 2011 07:12
Contact:

Hacking SSDL BINARY_FLOAT to float

Post by piers7 » Thu 30 Jun 2011 08:07

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
Posts: 5
Joined: Wed 29 Jun 2011 07:12
Contact:

ORA-03115 reading BINARY_FLOAT with Direct=true

Post by piers7 » Thu 30 Jun 2011 08:39

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 01 Jul 2011 13:42

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
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 08 Dec 2011 13:35

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
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 09 Dec 2011 16:48

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).

Post Reply