Comparison of Entity Framework compatible provider

I have read similar questions and their answers, however, it seems none deal with this exact question except for one, which is out of date (from 2009 - pre EF4).

Does anyone have any positive or negative experience with EF providers for Oracle, if so what is your experience?

Similar questions:
Entity Framework and Oracle
Can you use Microsoft Entity Framework with Oracle?
Entity framework with oracle database
How to choose an Oracle provider for .Net application?

Providers I know of:
Oracle's ODP.NET: http://www.oracle.com/technetwork/topics/dotnet/index-085163.html
Devart's DotConnect: http://www.devart.com/dotconnect/entityframework.html
DataDirect's Progress: http://www.datadirect.com/products/net/release-history.html
OPENLINK: http://uda.openlinksw.com/dotnet/

I know that Oracle's provider is currently beta (3) and is not fully managed - separate binaries for 32bit vs 64bit.

Are any of the others stable?
Are there any that I have missed?
Which have been successfully used in your projects?

回答1:

I ran a quick comparison of the different providers consisting of:

Installation experience.
Connectivity.
Server Explorer experience.
Updating from database experience (database first).
Data type mappings (database first).

Here are the conclusions:

1. Installation experience.

Oracle's ODP.NET (beta 3): Complex installation of separate x86 and x64 versions, installers not designed for Windows. Fills system path (dangerously close to max length), does not install to standard folders (program files + appdata).

Devart's DotConnect: Smooth installation, provider DLL is fully managed.

DataDirect's Progress: Smooth installation, provider DLL is fully managed.

OPENLINK: Seems to require server side installation, did not test further.

2. Connectivity.

Oracle's ODP.NET (beta 3): Complicated to configure, requires Oracle installation on client machine and either additional TSN file in Oracle installation or a long and complex connection string that looks like LISP/Scheme.
Update: Couldn't find this in the documentation, however, connection string can also contain simple data source definition e.g. serverName:port/serviceName.

Devart's DotConnect: Simple connection string + wizard.

DataDirect's Progress: Simple connection string + wizard.

3. Server Explorer experience.

Oracle's ODP.NET (beta 3): Most powerful of the three, enables easy editing, viewing of keys and indexes.

Devart's DotConnect: Viewing of tables and fields.

DataDirect's Progress: Enables easy editing, viewing of keys and indexes.

4. Updating from database experience (database first).

Oracle's ODP.NET (beta 3): Straightforward.

Devart's DotConnect: Shows tables from all schemas, no option to filter - makes finding tables exhausting.

DataDirect's Progress: Straightforward.

5. Data type mappings (database first).

Oracle's ODP.NET (beta 3): Default mapping for number(1,0), number(2,0) and number(3,0) are wrong*. Can manually override for number(2,0) and number(3,0). Fixing for number(1,0) doesn't work (at least not on beta 3 - may have worked in beta 2).

Update: Now that the release version is out (112030) this has been fixed. Some mappings are possible via section in app.config file.

Devart's DotConnect: Shows tables from all schemas, no option to filter - makes finding tables exhausting. Default mapping for binary float, binary double, timestamp with time zone, number(2,0), number(3,0) and number(4,0) are wrong*. Manual override should work - didn't check.

DataDirect's Progress: Default mappings are OK*.

(*) Mappings I expected:

DB Data Type    .NET Data Type
integer     Decimal
int         Decimal
smallint        Decimal
long        String
decimal     Decimal
rowid       String
float       Decimal
double      Decimal
binary float    Single
binary double   Double
char[40]        String
charvar[40]     String
natchar[40]     String
natcharvar[40]  String
natcharacter[40]    String
natcharactervar[40] String
number      Decimal
numeric     Decimal
nvarchar2[40]   String
real        Decimal
date        DateTime
timestamp       DateTime
timestamplocal  DateTime
timestampzone   DateTimeOffset
xml String
raw15       Binary
raw16       Guid
raw17       Int64
number(1,0)     Boolean
number(2,0)     Byte or SByte
number(3,0)     Byte or SByte (accepted Int16 as OK too)
number(4,0)     Int16
number(5,0)     Int16 (accepted Int32 as OK too)
number(6,0)     Int32
number(7,0)     Int32
number(8,0)     Int32
number(9,0)     Int32
number(10,0)    Int32 (accepted Int64 as OK too)
number(11,0)    Int64
number(15,0)    Int64
number(16,0)    Int64
number(17,0)    Int64
number(18,0)    Int64
number(19,0)    Int64 (accepted Decimal as OK too)
number(20,0)    Decimal (would accept Int64 as OK too)
number(21+,0)   Decimal

If you want the database type to be able to store any number in the .NET type range then IntX requires a number(N,0) where Ceil(log10(2^X)) = N for storage**.

Bool (Int1) ==> number(1,0)
Byte (Int8) ==> number(3,0)
Int16, UInt16 ==> number(5,0)
Int32, UInt32 ==> number(10,0)
Int64 ==> number(19,0)
UInt64 ==> number(20,0)

** Calculation assumes unsigned numbers, for signed numbers ceil(log(2^(X-1)).

References for data type assumptions:
TECH on the Net
Devart
Oracle

回答2:

I haven't tried EF 4.2 which should have support for the latest data providers , 4.1 only has support for the MS SQL Providers due the numerious new feature addons.

Here are all providers listed http://blogs.msdn.com/b/adonet/archive/2011/11/01/ef-4-2-released.aspx

EF 4.2 is only available as NuGet package and should be stable, i'm using 4.1 in production (Asp.Net MVC and MS SQL Server)

Also, here are some previous answers similar to your question: Support Oracle Provider for Entity Framework (msdn)

Short, if you are using Entity Framework 4.1, you should update, you could have severe core problems with it. Upgrade to 4.2 and then test again, everything should work fine with the providers.

dotConnect for Oracle for example has support for Entity Framework v4, so this will work without problems in EF 4.2