Using EF Oracle Sample Provider with EDM Designer

아티클
10/24/2008

Many people are asking if it is possible to use EFOracleProvider with EDM Designer in Visual Studio 2008 SP1. The answer is yes, but because the sample doesn't include a DDEX provider required for VS integration, there are certain steps that have to be run manually.

I've compiled a step-by-step guide for those interested in trying this out (this assumes NorthwindEF sample database installed according to instructions included with the sample, but it should be straightforward to adjust it to your own setup)

PART 1 : INSTALLING ORACLE SAMPLE PROVIDER

1. Download and unzip EFOracleSampleProvider.zip from https://code.msdn.com/EFOracleProvider

2. Follow instructions in the README.txt to set up a sample database.

3. Open elevated Visual Studio instance. Build the sample project.

4. Open elevated command prompt and open machine.config using notepad:
notepad %WINDIR%\Microsoft.NET\Framework\v2.0.50727\config\machine.config

5. Find <DbProviderFactories> section and add EFOracleProvider entry:

 <add name="EF Oracle Data Provider" invariant="EFOracleProvider" 
     description="EF Provider for Oracle testing" 
     type="EFOracleProvider.EFOracleProviderFactory, EFOracleProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b"/>

6. The completed system.data section has to look similar to this:

 <system.data>
  <DbProviderFactories>
    <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
    <add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
    <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
    <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
      <add name="EF Oracle Data Provider" invariant="EFOracleProvider" description="EF Provider for Oracle testing" type="EFOracleProvider.EFOracleProviderFactory, EFOracleProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b"/>  
    <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
  </DbProviderFactories>
</system.data>

PART 2: GENERATING MODEL FROM ORACLE DATABASE

7. Create a new project in VS. For simplicity let's create a console application

8. Open elevated command prompt. Enter the directory that contains the newly created project and run the following command:

 %WINDIR%\Microsoft.NET\Framework\v3.5\edmgen.exe /provider:EFOracleProvider /mode:fullgeneration 
      /connectionstring:"data source=XE;user id=edmuser;password=123456" /project:NorthwindEFModel

The output should be:

 Microsoft (R) EdmGen version 3.5.0.0
Copyright (C) 2008 Microsoft Corporation. All rights reserved.

Loading database information...
warning 6005: The data type 'timestamp(9)' is not supported, the column 'OrderDate' in table 'dbo.Orders' was excluded.
warning 6005: The data type 'timestamp(3)' is not supported, the column 'RequiredDate' in table 'dbo.Orders' was excluded.
Writing ssdl file...
Creating conceptual layer from storage layer...
Writing msl file...
Writing csdl file...
Writing object layer file...
Writing views file...

Generation Complete -- 0 errors, 2 warnings

9. This will create a bunch of NorthwindEFModel.* files for you.

10. Open Northwind.ssdl file in a text editor and replace all instances of Schema="dbo" with empty string (this is needed because tables in the sample Oracle database don't use a schema)

11. In order to use generated model in the EF Ddesigner, we have to create NorthwindEFModel.edmx file. This can be done manually (just copy/paste contents of individual files into an empty EDMX as indicated by the comments) or by using EdmGen2 tool from Code Gallery:

 C:\Path\To\EdmGen2.exe /toedmx NorthwindEFModel.csdl NorthwindEFModel.ssdl NorthwindEFModel.msl

12. This will create NorthwindEFModel.edmx, which we can add to the project in VS.

13. At this point you can now delete the following files generated by EdmGen.exe, which won't be necessary:

NorthwindEFModel.csdl
NorthwindEFModel.ssdl
NorthwindEFModel.msl
NorthwindEFModel.ObjectLayer.cs
NorthwindEFModel.Views.cs

PART 3: TESTING GENERATED MODEL

14. The only remaining thing to do is to add App.config file with connection string for our Oracle database:

 <?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="NorthwindEFModelContext" 
         connectionString="provider=EFOracleProvider;
                           metadata=res://*/NorthwindEFModel.csdl|res://*/NorthwindEFModel.ssdl|res://*/NorthwindEFModel.msl;
                           Provider Connection String='data source=XE;user id=edmuser;password=123456'" 
         providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

15. We can now try out our model by running a sample LINQ to Entities query:

 using (NorthwindEFModelContext context = new NorthwindEFModelContext())
{
    foreach (var c in context.Customers.Where(c=>c.City == "Seattle"))
    {
        Console.WriteLine(c.CompanyName);
    }
}

Comments

Anonymous
December 11, 2008
Hi I am facing the problem on using this EFOracleSampleProvider. I have done all the first 7 steps what you mentioned in the article, But when i run the following command %WINDIR%Microsoft.NETFrameworkv3.5edmgen.exe /provider:EFOracleProvider /mode:fullgeneration /connectionstring:"data source=XE;user id=edmuser;password=123456" /project:NorthwindEFModel it is giving error as error 7001: The provider did not return a ProviderManifestToken string. ORA-12154: TNS:could not resolve the connect identifier specified I have given the correct cridentials to connectionstring. please give us the solution for the above error.
Anonymous
December 25, 2008
Padma, if you have 2 Oracle clients installed under your product[version_number], then EFOracleProvider will look for tnsnames.ora in client_2. The workaround is copy tnsnames.ora to client_2 folder as well. Hope this helps, Boris
Anonymous
April 02, 2009
This is great stuff. When will the timestamp type of oracle will be supported? It is very common type in oracle.
Anonymous
May 11, 2009
step 10. the ssdl generated by edmgen in .net 3.5 sp1 requires replacing 'store:Schema="dbo"' with NOTHING. This is again oracle 11g.
Anonymous
May 11, 2009
what i said above is in addition to replacing 'Schema="dbo"' with NOTHING btw
Anonymous
May 12, 2009
The comment has been removed
Anonymous
May 12, 2009
The comment has been removed
Anonymous
May 12, 2009
have got something working only tested by pulling a single row from a table in an 11g database. Note this database has case sensitive table and column names.
Anonymous
June 14, 2009
Hi. I follow all your steps and find this error when I query data. The Mapping and Metadata information for EntityContainer 'MyDBContext' no longer matches the information used to create the pre generated views.
Anonymous
June 14, 2009
If I skip step 10, another error will be displayed to notify me that the table / view I queried is not existed. (bad schema i think)
Anonymous
June 30, 2009
I have done all the instructions in the guide but I have the following error: error 7001: Il metodo 'CreateDbCommandDefinition' del tipo 'EFOraclePr racleProviderServices' dell'assembly 'EFOracleProvider, Version=1.0.0. =neutral, PublicKeyToken=def642f226e0e59b' non ha un'implementazione. why this error???? HELP ME
Anonymous
June 30, 2009
i have resolved....i have changed my framework .NET 3.5 with FRAMEWORK .NET 3.5 Service Pack 1 :-)
Anonymous
July 07, 2009
Hi, Have this project now available on Codeplex at http://eforacleodpprovider.codeplex.com/
Anonymous
July 09, 2009
The comment has been removed
Anonymous
August 26, 2009
Hi, I have a problem about Int64 and EDM.Decimal. In my case, IDs are "number" in my SSDL file, "Int64" in my CSDL file and "Long" in my objects. The problem appears when I try to add entities to database. Stack Trace : System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.ArgumentException: The specified value is not an instance of type 'Edm.Decimal' So I decided to look your EFOracleOdpProviderManifest and I fine a solution (maybe not the good one). In the "GetEdmType()" method, case "number", I add : if (precision == 0 && scale == 196) return TypeUsage.CreateDefaultTypeUsage(PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Int64));
Anonymous
March 02, 2010
Has anybody managed to make this work on Windows 7? I have two gactutil.exe; two (or more)edmgen.exe; and two machine.config. I have tried multiple combinations with no luck. Thanks.
Anonymous
March 09, 2010
hmmm... I get an error on step 8: error 7001: The specified store provider 'EFOracleProvider' cannot be found in the configuration or it's not valid. Unable to find the requested .Net Framework Data Provider. It may not be installed. I'm running windows 7 x64
Anonymous
May 10, 2010
if using Oracle 11g version, note the change everywhere from "OracleProvider" to "OracleOdpProvider", and new public key token value (get it from the gac entry) - I missed that one for a few hrs. Also now getting same: "Unable to find the requested.NET Framework Data Provider." Am also using Win7 32, VS2008.

다음을 통해 공유

Using EF Oracle Sample Provider with EDM Designer

PART 1 : INSTALLING ORACLE SAMPLE PROVIDER

PART 2: GENERATING MODEL FROM ORACLE DATABASE

PART 3: TESTING GENERATED MODEL

Comments

추가 리소스