Examples of Oracle database operations with EF

Using EF Oracle Sample Provider with EDM Designer (from MSDN)

Many people is asking if it's possible to use Eforacleprovider with EDM Designer in Visual Studio SP1. The answer is yes and because the sample doesn ' t include a DDEX provider required for VS integration, there be certain s Teps that has to be run manually.

I ' ve compiled a step-by-step guide to those interested in trying the out (this assumes Northwindef sample database Insta Lled According to instructions included with the sample, but it should is 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:

<nameinvariantdescriptiontype= "Eforacleprovider.eforacleproviderfactory, Eforacleprovider, version=1.0.0.0, Culture=neutral, publickeytoken=def642f226e0e59b "/>   

6. The completed System.Data section have to look similar to this:

<System.Data><DbProviderFactories><AddName= "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"/><AddName= "OLE DB Data Provider"Invariant= "System.Data.OleDb"Description= ". Net Framework Data Provider for OLE DB"Type= "System.Data.OleDb.OleDbFactory, System.Data, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089"/><AddName= "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 "/><AddName= "SqlClient Data Provider"Invariant= "System.Data.SqlClient"Description= ". Net Framework Data Provider for SQL Server"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< Span class= "KWRD" >= "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:

' 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 would create a bunch of northwindefmodel.* files for you.

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

One. In order to use generated model in the EF Ddesigner, we had to create northwindefmodel.edmx file. This can is done manually (just copy/paste contents of individual files into a empty EDMX as indicated by the comments) O R by using the EdmGen2 tool from Code Gallery:

C:\PATH\TO\EDMGEN2.EXE/TOEDMX northwindefmodel.csdl northwindefmodel.ssdl NORTHWINDEFMODEL.MSL

This would create NORTHWINDEFMODEL.EDMX, which we can add to the project in VS.

At the "This" can now delete the following files generated by EdmGen.exe, which won ' t is necessary:

• northwindefmodel.csdl
• northwindefmodel.ssdl
• Northwindefmodel.msl
• NorthwindEFModel.ObjectLayer.cs
• NorthwindEFModel.Views.cs

Part 3:testing GENERATED MODEL

The only remaining thing to does is to add App. Config file with connection string for our Oracle database:

<?XmlVersion= "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 >               

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

New Northwindefmodelcontext ()) {    "Seattle")    {        Console.WriteLine (c.companyname);}    }

I use my poor English proficiency, a simple translation of a bit (for reference):

Many people ask if it is possible to use Eforacleprovider with the EDM designer in Visual Studio SP1. The answer is yes, but because the example does not include the DDEX provider required for VS integration, there are some steps that must be run manually.

I have summed up a step-by guide for those interested in trying this (which is assuming the Northwindef sample database is installed according to the example, with the instructions provided, but it should be easy to tweak to your own setup program)

Part 1th: Installing the ORACLE sample provider

1. Download and unzip eforaclesampleprovider.zip from Http://code.msdn.com/EFOracleProvider

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

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

4. Open an elevated command prompt and Machine.config open with Notepad:
Notepad%windir%\microsoft.net\framework\v2.0.50727\config\machine.config

5. Locate the <DbProviderFactories> section and add the Eforacleprovider entry:

<nameinvariantdescriptiontype= "Eforacleprovider.eforacleproviderfactory, Eforacleprovider, version=1.0.0.0, Culture=neutral, publickeytoken=def642f226e0e59b "/>   

6. The completed System.Data section has a look similar to this:

<System.Data><DbProviderFactories><AddName= "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"/><AddName= "OLE DB Data Provider"Invariant= "System.Data.OleDb"Description= ". Net Framework Data Provider for OLE DB"Type= "System.Data.OleDb.OleDbFactory, System.Data, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089"/><AddName= "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 "/><AddName= "SqlClient Data Provider"Invariant= "System.Data.SqlClient"Description= ". Net Framework Data Provider for SQL Server"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 2nd: Generating models from an ORACLE database

7. Create a new project in VS. for the sake of simplicity, let's create a console application

8. Open an elevated command prompt. Enter the directory that contains the newly created project, and then 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:

' 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 bundle of northwindefmodel.* files for you.

10. Open the northwind.ssdl file in a text editor and replace the schema of all instances = "dbo" with an empty string (this is required because the tables in the sample Oracle database do not use schemas)

11. For using the generated model in EF Ddesigner, we must create the northwindefmodel.edmx file. This can be done manually (copy/paste only the content into an empty EDMX as indicated by the individual file) or use the EdmGen2 tool from the code base :

C:\PATH\TO\EDMGEN2.EXE/TOEDMX northwindefmodel.csdl northwindefmodel.ssdl NORTHWINDEFMODEL.MSL

12. This will create northwindefmodel.edmx that we can add to the project, VS.

13. At this point, you can now delete the following files that generate EdmGen.exe, which will not be required:

• northwindefmodel.csdl
• northwindefmodel.ssdl
• Northwindefmodel.msl
• NorthwindEFModel.ObjectLayer.cs
• NorthwindEFModel.Views.cs

Part 3rd: Test the resulting model

14. The only thing left to do is to add our Oracle database with the connection String App. Config file:

<?XmlVersion= "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 our model by running the LINQ example on an entity query:

New Northwindefmodelcontext ()) {    "Seattle")    {        Console.WriteLine (c.companyname);}    }