Using EF Oracle Sample Provider with EDM Designer


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 http://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 (18)

  1. Padma Alluri says:

    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.

  2. borismod says:

    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

  3. ZachLi says:

    This is great stuff. When will the timestamp type of oracle will be supported? It is very common type in oracle.

  4. janaka@adfero.co.uk says:

    step 10. the ssdl generated by edmgen in .net 3.5 sp1 requires replacing ‘store:Schema="dbo"’ with NOTHING. This is again oracle 11g.

  5. janaka@adfero.co.uk says:

    what i said above is in addition to replacing ‘Schema="dbo"’ with NOTHING btw

  6. janaka@adfero.co.uk says:

    for the benifit of others step 8. can work as follows also

    edmgen /provider:EFOracleProvider /mode:fullgeneration /connectionstring:"Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORAdb.yourdomain.co.uk)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORAdb.yourdomain.co.uk)));User Id=your_user_name;Password=your_password;" /project:SomeProjectName

    if you get any part of the connection string wrong you will get errors like

    Error when invalid SERVICE_NAME is given:

    error 7001: The provider did not return a ProviderManifestToken string.

           ORA-12514: TNS:listener does not currently know of service requested in

    connect descriptor

    Error when invalid HOST is given:

    error 7001: The provider did not return a ProviderManifestToken string.

           ORA-12545: Connect failed because target host or object does not exist

  7. janaka@adfero.co.uk says:

    if you are running the EFOracleProvider along side another modified version based on a copy of the project don’t forget to create a new strong name key using sn.exe -k keyname.snk, in the project properties > signing > change to use this .snk file > recompile (the project is set to register in the GAC automagically).

    then get the new public token by running sn.exe -T newEForacleProviderAssembly.dll and update the machine.config <DbProviderFactory> (step 5.)

    Without this you may get an error saying that the @Provider name in the ssdl file is invalid or not registered properly because it is pointing at the other one that has a different name.

  8. janaka@adfero.co.uk says:

    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.

  9. allen_st_clair@msn.com says:

    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.

  10. allen_st_clair@msn.com says:

    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)

  11. adrien82 says:

    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

  12. adrien82 says:

    i have resolved….i have changed my framework .NET 3.5 with FRAMEWORK .NET 3.5 Service Pack 1

    :-)

  13. janaka@adfero.co.uk says:

    Hi,

    Have this project now available on Codeplex at http://eforacleodpprovider.codeplex.com/

  14. adrien82 says:

    I have done all the instructions in the guide but I have the following error when effectued the query:

    System.Data.OracleClient.OracleException non è stata gestita dal codice utente

     Message="ORA-00942: tabella o vista inesistenten"

     Source="System.Data.OracleClient"

     ErrorCode=-2146232008

     Code=942

     StackTrace:

          in System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)

          in System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)

          in System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)

          in System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)

          in EFOracleProvider.EFOracleCommand.ExecuteDbDataReader(CommandBehavior behavior)

          in System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)

          in System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)

     InnerException:

    WHY THIS PROBLEM?????? HELP ME thanks

  15. Romain Suire says:

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

  16. fmnieto says:

    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.

  17. andest01 says:

    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

  18. riix says:

    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.