Sample Entity Framework Provider for Oracle

NEW POST: How To: Step-by-Step

khnumtemu — Fri, 12 Jun 2009 14:06:45 GMT

I want to use this badly, but I am totally green in this area.

Can someone create a post with a Step by Step, 1-2-3 on how to implement this in to a WinForms Project.

Thanks so much.

NEW POST: Work with Oracle 9?

curelom — Fri, 08 May 2009 17:30:42 GMT

Does this work with Oracle 9?

NEW POST: EFOracleSampleProvider installation

JA — Fri, 08 May 2009 00:49:06 GMT

answer is here I think

http://blogs.msdn.com/jkowalski/archive/2008/10/24/using-ef-oracle-sample-provider-with-edm-designer.aspx

NEW POST: EFOracleSampleProvider installation

JA — Fri, 08 May 2009 00:46:57 GMT

anybody, any tips on what is missing

NEW POST: EFOracleSampleProvider installation

garymason — Mon, 30 Mar 2009 14:19:10 GMT

I have the same issue - the provider does not appear in the list of providers for Oracle ( I had a previous trial version driver from another supplier that does appear).

NEW POST: Actual Oracle support coming?

MonkeyTheLeo — Thu, 05 Mar 2009 06:25:10 GMT

Unfortunately Oracle hasn't put anything out there yet (as far as I've searched).

However, there is a definite non-free solution provided by DevArt - http://www.devart.com/dotconnect/oracle/. It looks to be $300 for the developer edition, but you can map your entities to an Oracle db.

CREATED ISSUE: Top Values query is incorrect

dbrowne — Thu, 26 Feb 2009 18:22:43 GMT

This query pattern applies the row limiting before the ORDER BY, returning 10 random rows and then ordering them, instead of ordering the rows before selecting the top rows.

This is wrong:

select
"Project1"."Address" AS "Address",
"Project1"."City" AS "City",
"Project1"."Region" AS "Region",
"Project1"."PostalCode" AS "PostalCode",
"Project1"."Country" AS "Country",
"Project1"."Phone" AS "Phone",
"Project1"."Fax" AS "Fax"
FROM ( SELECT
"Extent1"."CustomerID" AS "CustomerID",
"Extent1"."CompanyName" AS "CompanyName",
"Extent1"."ContactName" AS "ContactName",
"Extent1"."ContactTitle" AS "ContactTitle",
"Extent1"."Address" AS "Address",
"Extent1"."City" AS "City",
"Extent1"."Region" AS "Region",
"Extent1"."PostalCode" AS "PostalCode",
"Extent1"."Country" AS "Country",
"Extent1"."Phone" AS "Phone",
"Extent1"."Fax" AS "Fax",
1 AS "C1"
FROM "Customers" "Extent1"
) "Project1"
WHERE (ROWNUM <= (10) )
ORDER BY "Project1"."Address" ASC

the right pattern is:

SELECT
"Project1"."Address" AS "Address",
"Project1"."City" AS "City",
"Project1"."Region" AS "Region",
"Project1"."PostalCode" AS "PostalCode",
"Project1"."Country" AS "Country",
"Project1"."Phone" AS "Phone",
"Project1"."Fax" AS "Fax"
FROM ( SELECT
"Extent1"."CustomerID" AS "CustomerID",
"Extent1"."CompanyName" AS "CompanyName",
"Extent1"."ContactName" AS "ContactName",
"Extent1"."ContactTitle" AS "ContactTitle",
"Extent1"."Address" AS "Address",
"Extent1"."City" AS "City",
"Extent1"."Region" AS "Region",
"Extent1"."PostalCode" AS "PostalCode",
"Extent1"."Country" AS "Country",
"Extent1"."Phone" AS "Phone",
"Extent1"."Fax" AS "Fax",
1 AS "C1"
FROM "Customers" "Extent1"
ORDER BY "Extent1"."Address" ASC
) "Project1"
WHERE (ROWNUM <= (10) )

See, eg http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
for a discussion of this issue.

David (dbrowne_at_microsoft)

CREATED ISSUE: Inappropriate use of Quoted Identifiers

dbrowne — Thu, 26 Feb 2009 17:53:59 GMT

Oracle is always case-sensitive, including the metadata catalogs. To compensate for this Oracle stores ideintifiers in upper case, and for non-quoted identifiers, Oracle converts the identifier to all caps for compilation. For quoted identifiers, oracle requires exact case matching for objects. You can, for instance have a table named "Orders" and one named ORDERS. the DDL "CREATE TABLE Orders ..." creates a different table than the DDL "CREATE TABLE "Orders" ...". Using quoted identifiers and mixed case identifiers is pretty tricky in Oracle, and is very rarely done.

Typically people use non-quoted identifiers which behave like case-insensitive identifiers because Oracle silently upper cases the identifiers and the comparisons.

First the sample should follow Oracle best practices and not use quoted identifiers for the object names. Second the EF code should not use quoted identifiers in the SQL by default.

David (dbrowne_at_microsoft.com)

NEW POST: Database Independence

driswadkar — Thu, 05 Feb 2009 12:59:36 GMT

Hi

Just a small question. How can i use the same Model for my SQL Server Database? What changes i need to do?

Regards,

Dipak Riswadkar

NEW POST: Actual Oracle support coming?

Greif — Wed, 12 Nov 2008 19:04:28 GMT

Is Oracle (the company) supporting this technology? They have been real quiet on their support of the Microsoft Entity Framework.

In all previous data technologies, Microsoft has released an Oracle provider (ADO, .NET, etc) but not for the Entity Framework. Because both vendors (Microsoft & Oracle) seem to be dropping the ball on supporting the Entity Framework with Oracle, it will be steep hill to climb to get support from the community.

NEW POST: ORA-12546: TNS:permission denied

ameyagholap — Tue, 11 Nov 2008 13:38:25 GMT

http://v421982.blogspot.com/2008/11/i-give-up-contd.html

Still no luck

Please guide

NEW POST: EFOracleSampleProvider installation

girolo — Tue, 21 Oct 2008 17:45:28 GMT

Hello, I've downloded and compiled the solution EFOracleProvider
Now, EFOracleProvider.dll is in the GAC
If I try to create a new ADO.NET Entity Data Model (*.edmx) file and generate it from the database, I don't have the option to select the new EFOracleProvider.
Is it normal ? Can we use VS2008 EF designer with this driver ?

CREATED ISSUE: INSTR wrong argument order

szunyog — Sat, 18 Oct 2008 13:13:46 GMT

When you use StringProperty.StartsWith("xxx") in the where clause, it generates the a wrong sql sentence:

INSTR ('xxx', "Extent1"."StringProperty") = 1
The problem is that the INSTR sql function in oracle suspects the arguments in the opposite order, so the generated sql condition should be INSTR ("Extent1"."StringProperty", 'xxx') = 1

NEW POST: ORA-12546: TNS:permission denied

ameyagholap — Fri, 17 Oct 2008 12:37:36 GMT

What do I do if I get this exception from System.Data.OracleClient?

The edmuser created for testing the sample provider is sort-a super user with all admin and system privileges with role of dba

I cant possibly think of a solution for this

Please guide

NEW POST: Issue with Oracle number data types

tmyklebust — Wed, 20 Aug 2008 17:14:53 GMT

Hello

I am building an application with support for SQL and Oracle. There is one shared conseptual model and one store model placed in separate DLLs together with DB-specific code (one DLL for SQL and one for Oracle). Everything is working fine with SQL, but I have an issue with the Oracle driver that I am not able to solve. I'm not quite sure how much it has to do with this Oracle provider, but I don't know where to ask else.

Anyway - loading and displaying data works just fine. When I change an item of type "Document" and try to save it, I get this exception:

The specified value is not an instance of type 'Edm.Decimal'
Parameter name: value

at System.Data.Common.CommandTrees.DbConstantExpression..ctor(DbCommandTree commandTree, Object value, TypeUsage constantType)
at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildSetClauses(DbModificationCommandTree commandTree, PropagatorResult row, TableChangeProcessor processor, Boolean insertMode, Dictionary`2& outputIdentifiers, DbExpression& returning, Boolean& rowMustBeTouched)
at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildInsertCommand(PropagatorResult newRow, TableChangeProcessor processor)
at System.Data.Mapping.Update.Internal.TableChangeProcessor.CompileCommands(ChangeNode changeNode, UpdateCompiler compiler)

Here are the schemas for my model:

Store:

<EntityType Name="DOCUMENT">
<Key>
<PropertyRef Name="DOC_RNO" />
<PropertyRef Name="DB_RNO" />
</Key>
<Property Name="DB_RNO" Type="number" Nullable="false" Scale="0" />
<Property Name="DOC_RNO" Type="number" Nullable="false" Scale="0" />
<Property Name="DOC_ID" Type="varchar2" Nullable="false" MaxLength="60" />
<Property Name="TITLE" Type="varchar2" Nullable="true" MaxLength="255" />
<Property Name="PACK_RNO" Type="number" Nullable="true" Scale="0" />
<Property Name="WEIGHT" Type="number" Nullable="true" Scale="0" />
<Property Name="CHAIN" Type="number" Nullable="true" Scale="0" />
</EntityType>

Conceptual:

<EntityType Name="Document">
<Key>
<PropertyRef Name="Id" />
<PropertyRef Name="ArchiveId" />
</Key>
<Property Name="Id" Type="Int32" Nullable="false" />
<Property Name="ArchiveId" Type="Int16" Nullable="false" />
<Property Name="Title" Type="String" Nullable="true" />
<Property Name="Key" Type="String" Nullable="false" />
<Property Name="Weight" Type="Int16" Nullable="true" />
<Property Name="CtrId" Type="Int32" Nullable="true" />
<Property Name="ChainNumber" Type="Int16" Nullable="true" />
</EntityType>

Mapping:

<EntitySetMapping Name="Documents">
<EntityTypeMapping TypeName="IsTypeOf(ProArcEntities.Document)">
<MappingFragment StoreEntitySet="DOCUMENT">
<ScalarProperty Name="CtrId" ColumnName="PACK_RNO" />
<ScalarProperty Name="ArchiveId" ColumnName="DB_RNO" />
<ScalarProperty Name="Weight" ColumnName="WEIGHT" />
<ScalarProperty Name="ChainNumber" ColumnName="CHAIN" />
<ScalarProperty Name="Title" ColumnName="TITLE" />
<ScalarProperty Name="Key" ColumnName="DOC_ID" />
<ScalarProperty Name="Id" ColumnName="DOC_RNO" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>

The Oracle table looks like this:

Column Type Length Precision Scale Nulls
1 DOC_RNO NUMBER 22 - 0 N
2 DB_RNO NUMBER 22 - 0 N
3 DOC_ID VARCHAR2 60 - - Y
4 TITLE VARCHAR2 255 - - Y
5 FORMAT_ID VARCHAR2 10 - - Y
6 CREATED DATE 7 - - Y
7 MODIFIED_M DATE 7 - - Y
8 MODIFIED_C DATE 7 - - Y
9 MODIFIED_P DATE 7 - - Y
10 VOIDED DATE 7 - - Y
11 FLAG_DESC NUMBER 22 - 0 Y
12 PACK_RNO NUMBER 22 - 0 Y
13 CHAIN NUMBER 22 - 0 Y
14 PLANHOURS FLOAT 22 20 - Y
15 WEIGHT NUMBER 22 - 0 Y
16 LOCKED DATE 7 - - Y
17 LOCK_BY VARCHAR2 32 - - Y
18 HOLDS NUMBER 22 - 0 Y
19 REVISION VARCHAR2 20 - - Y
20 VERSION VARCHAR2 10 - - Y
21 STATUS VARCHAR2 20 - - Y
22 REV_ID NUMBER 22 - 0 Y
23 VER_ID NUMBER 22 - 0 Y
24 STATDATE DATE 7 - - Y
25 MASK_RNO NUMBER 22 - 0 Y
26 FILE_SEQNO NUMBER 22 - 0 Y
27 FILE_COUNT NUMBER 22 - 0 Y
28 CREATED_BY VARCHAR2 32 - - Y
29 MODIFIED_BY VARCHAR2 32 - - Y

Hopefully an Oracle and/or EF expert can help me out on this one.

Regards,
Terje Myklebust

NEW POST: EdmGenTests

Jeph — Fri, 15 Aug 2008 16:21:00 GMT

In the README.txt file the "EdmGenTests" directory is described "- ad-hoc tests that use EF Oracle Provider to reverse-engineer a Northwind database". However it's not belonging to the .zip. Is it possible to include it ?

UPDATED WIKI: Home

jkowalski — Wed, 13 Aug 2008 04:55:50 GMT

This is a Sample Entity Framework Provider for Oracle. It works with Oracle 10g by wrapping System.Data.OracleClient APIs. It uses the same technique as Entity Framework Sample Provider which wraps System.Data.SqlClient.

Sample Entity Framework Provider for Oracle is compatible with Entity Framework released as part of .NET Framework 3.5 SP1.

DISCLAIMER

This is an unsupported sample and should be treated as such. Although reasonable effort has been put to make sure that basic EF scenarios work with Oracle, there are certain limitations. Use in production environment is strongly discouraged.

INSTALLATION

Install Visual Studio 2008 SP1.
Install Oracle 10g Express Edition from http://www.oracle.com/technology/software/products/database/xe/index.html
Add new user "edmuser" with password "123456" (if you change the user name/password you will need to update connection strings). The user needs to have permissions to create database objects (assigning the user to DBA role is the quickest way to give necessary permissions).
The default connection string is using XE name from tnsnames.ora. If you have installed a different version of Oracle or used different instance configuration, you will have to update connection strings in App.config files.
Review "OracleNorthwind/install_db.cmd" and adjust path to sqlplus.exe as necessary then run the batch file. It will create and populate a Northwind sample database in "edmuser" schema.
Open the solution file EFOracleProvider.sln and run "EFOracleProvider.AdHocTests" and "Entity Framework Query Samples"

KNOWN ISSUES

BINARY_FLOAT/BINARY_DOUBLE types are not supported. This is a limitation of underlying System.Data.OracleClient. Columns using those types have been replaced with NUMBER.
Oracle DDEX Provider for Visual Studio is not included. Example DDEX provider that supports SQL Server is available as part of EF Sample Provider at http://code.msdn.microsoft.com/EFSampleProvider
APPLY operator is not supported by Oracle. As a result, certain queries that internally use this operator (such as "Average - Grouping 1", "Max - Grouping", "Min - Grouping") are not supported.
Certain functions (AVG in particular) return numeric values that cannot be properly materialized as decimal by System.Data.OracleClient. As a result users will get OCI-22053 (overflow).

CREATED RELEASE: EFOracleSampleProvider for VS2008 SP1 (Aug 11, 2008)

Mon, 11 Aug 2008 20:11:59 GMT

RELEASED: EFOracleSampleProvider for VS2008SP1 Beta (Jun 23, 2008)

Tue, 24 Jun 2008 01:17:38 GMT

UPDATED RELEASE: EFOracleSampleProvider for VS2008SP1 Beta (Jun 23, 2008)

Tue, 24 Jun 2008 01:17:38 GMT