Deploying and Configuring ODP.NET to work without installation with Entity Framework

Asked 5/12, 2012 at 10:25 Answered 12/8, 2013 at 17:57

Solved oracle entity-framework app-config odp.net dataprovider

How do you deploy and configure ODP.NET to work without installation with Entity Framework?

A. How to deploy and configure Oracle.DataAccess.Client?

B. How to deploy and configure Oracle.ManagedDataAccess.Client?

C. What do you need to do in order to make builds with EDMXs with Oracle SSDLs work?

D. What do you need to install for designer support?

Monadelphous answered 5/12, 2012 at 10:25 Comment(0)

This answer summarizes (hopefully) all the steps required, many of which documented in various places online and might save someone hours of Googling.

A. How to deploy and configure Oracle.DataAccess.Client.

A.1. Download ODAC112030Xcopy_64bit.zip or ODAC112030Xcopy_32bit.zip.

A.1.1. Extract the content of the following folders within the zip file into your application/host's bin/setup folder:

A.1.1.1. instantclient_11_2

A.1.1.2. odp.net4\bin\

A.1.1.3. odp.net4\odp.net\bin\

A.1.1.4. odp.net4\odp.net\PublisherPolicy\4\

A.2. Add the following section to the beginning of your application's/host's app.config/web.config (if you already have a configSections element, add the section to it:

<configSections>
  <section name="oracle.dataaccess.client"
    type="System.Data.Common.DbProviderConfigurationHandler, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
</configSections>

A.3. Add the following sections to the end of your application's/host's app.config/web.config:

A.4. From the ODAC112030Xcopy's folder Run:

configure.bat odp.net4 somename

I recommend using oraclehome112030_32 or oraclehome112030_64 as the "somename" above.

<system.data>
  <DbProviderFactories>
    <!-- Remove in case this is already defined in machine.config -->
    <remove invariant="Oracle.DataAccess.Client" />
    <add name="Oracle Data Provider for .NET"
         invariant="Oracle.DataAccess.Client"
         description="Oracle Data Provider for .NET"
         type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=4.112.3.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
  </DbProviderFactories>
</system.data>

<oracle.dataaccess.client>
  <settings>
    <add name="bool" value="edmmapping number(1,0)" />
    <add name="byte" value="edmmapping number(3,0)" />
    <add name="int16" value="edmmapping number(5,0)" />
    <add name="int32" value="edmmapping number(10,0)" />
    <add name="int64" value="edmmapping number(19,0)" />
    <add name="int16" value="edmmapping number(38,0)" />
    <add name="int32" value="edmmapping number(38,0)" />
    <add name="int64" value="edmmapping number(38,0)" />
  </settings>
</oracle.dataaccess.client>

B. How to deploy and configure Oracle.ManagedDataAccess.Client.

B.1. Download ODP.NET_Managed_1120350_Beta.zip

B.1.1. Extract the following files into your application/host's bin/setup folder.

B.1.1.1. Oracle.ManagedDataAccess.dll

B.1.1.2. x64\Oracle.ManagedDataAccessDTC.dll or x86\Oracle.ManagedDataAccessDTC.dll

B.2. Add the following section to the beginning of your application's/host's app.config/web.config (if you already have a configSections element, add the section to it:

<configSections>
  <section name="oracle.manageddataaccess.client"
    type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.112.3.50, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</configSections>

B.3. Add the following sections to the end of your application's/host's app.config/web.config:

<system.data>
  <DbProviderFactories>
    <!-- Remove in case this is already defined in machine.config -->
    <remove invariant="Oracle.ManagedDataAccess.Client" />
    <add name="ODP.NET, Managed Driver"
         invariant="Oracle.ManagedDataAccess.Client"
         description="Oracle Data Provider for .NET, Managed Driver"
         type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.112.3.50, Culture=neutral, PublicKeyToken=89b483f429c47342" />
  </DbProviderFactories>
</system.data>

<oracle.manageddataaccess.client>
  <version number="*">
    <settings>
      <!-- Set this path if you are using TNS aliases as connection strings (not recommended) -->
      <!-- Instead you can use "SERVER_NAME:PORT/SERVICE_NAME" as your data source -->
      <setting name="TNS_ADMIN" value="C:\"/>
    </settings>
    <edmMappings>
      <edmMapping dataType="number">
        <add name="bool" precision="1"/>
        <add name="byte" precision="2" />
        <add name="int16" precision="5" />
      </edmMapping>
    </edmMappings>
  </version>
</oracle.manageddataaccess.client>

C. For building:

C.1. Add this section to your EDMX's assembly's app.config:

(Haven't tried this with Oracle.ManagedDataAccess.Client yet)

<oracle.dataaccess.client>
  <settings>
    <add name="bool" value="edmmapping number(1,0)" />
    <add name="byte" value="edmmapping number(3,0)" />
    <add name="int16" value="edmmapping number(5,0)" />
    <add name="int32" value="edmmapping number(10,0)" />
    <add name="int64" value="edmmapping number(19,0)" />
    <add name="int16" value="edmmapping number(38,0)" />
    <add name="int32" value="edmmapping number(38,0)" />
    <add name="int64" value="edmmapping number(38,0)" />
  </settings>
</oracle.dataaccess.client>

C.2. Add a file named Oracle.xsd to the same assembly with the content:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="odpnetappconfigmappings" xmlns:xs="http://www.w3.org/2001/XMLSchema">

  <xs:complexType name="addtype">
    <xs:attribute name="name" type="xs:string" />
    <xs:attribute name="value" type="xs:string" />
  </xs:complexType>

  <xs:complexType name="settingstype">
    <xs:sequence minOccurs="0" maxOccurs="unbounded">
      <xs:element name="add" type="addtype" />
    </xs:sequence>
  </xs:complexType>

  <xs:complexType name="oracledataaccessclienttype">
    <xs:sequence minOccurs="0" maxOccurs="1">
      <xs:element name="settings" type="settingstype" />
    </xs:sequence>
  </xs:complexType>

  <xs:element name="oracle.dataaccess.client" type="oracledataaccessclienttype" />

</xs:schema>

C.3. Add the above XSD to the above app.config's list of Schemas.

C.4. If you are getting errors for boolean mappings during build even though build is succeeding, add the app.config mappings to Visual Studio's devenv.exe.config.

C.5. If you want to use Oracle.ManagedDataAccess.Client, either edit data provider attribute in the EDMX manually prior to build (I have not tried this) or edit it prior to creation of Context at run time and load MSSL from edited copy instead of from resource (this seems to work and I also use a similar trick to choose which MSSL to load for different DB providers).

D. For designer support:

D.1. Download win64_11gR2_client.zip or win32_11gR2_client.zip and install.

D.1.1. Select "Administrator" as type of installation.

D.2. download ODT and install.

I tried this (A and B) on a blank machine (VM) with Windows 7 x64.

This procedure does not seem to work with x86 version of Oracle.DataAccess.Client on Windows x64.

The procedure does seem to work with the x64 version of Oracle.DataAccess.Client on Windows x64 and with both versions of Oracle.ManagedDataAccess.Client.

Monadelphous answered 5/12, 2012 at 10:25 Comment(17)

How to connect to Oracle db with Entity Framework & ODP.NET Managed Driver ? Maybe useful forums.oracle.com/forums/… Any more information about it? – Crib 4/4, 2013 at 8:9

@Crib See section B. in my answer. I tried this with the beta and it sort of worked - the queries returned different results. – Monadelphous 4/4, 2013 at 8:23

The managed client and EF only really partially work together right now. Beta 2 is supposed to have better support. – Vue 4/4, 2013 at 14:29

Here's two things to consider: 1) I had to copy these additional DLLs to my ASP.NET MVC bin folder: oci.dll, Oracle.DataAccess.dll (of course), orannzsbb11.dll, oraociei11.dll, and OraOps11w.dll (copy appropriate x86/x64). 2) I was able to get an x86 versions of the app working on an x64 machine by having the App-Pool in IIS set to allow 32-bit applications. Also, when using Entity Framework with Oracle.DataAccess, Oracle's DLL has to be version 4.X+ period! (2.X won't work; this took me a while to figure out :/) – Olcott 8/4, 2013 at 16:0

oracle.com/technetwork/database/enterprise-edition/downloads/… Download the latest ODP.NET production release in Oracle Database 12c Client. This release includes the fully-managed driver, pluggable database support, Identity column support, native PL/SQL Boolean, and Transaction Guard. Configuring ODP.NET, Managed Driver requires additional steps post-install. ODP.NET, Managed Driver is 100% fully managed. Developers can deploy a single assembly, side by side with other ODP.NET versions easily in a deployment package smaller than 10 MB. – Crib 6/8, 2013 at 11:8

@Crib I'll try it and post an update. I hope this version is better than its predecessors. – Monadelphous 6/8, 2013 at 23:34

@Crib a 852/873MB download just to get the 32b/64b client is a bad start. I am very skeptic about the bug fixes (e.g. mapping number(1) to boolean and number to double), which the product managers did not agree need fixing in the Oracle forums. – Monadelphous 8/8, 2013 at 15:34

Alex Keh (from Oracle) says: "It is currently part of the Oracle DB 12c client. To use managed ODP.NET, you have to download and install the DB client. From there, you can extract just the managed ODP.NET assembly and setup files. These files are less than 10 MB and can be deployed to any target machines. Currently, we are packaging a stand alone managed ODP.NET release and ODAC 12 release that will be much smaller. This will be released on OTN shortly." – Crib 12/8, 2013 at 5:53

Added update on latest client as a separate answer - this one is already too long :-) – Monadelphous 12/8, 2013 at 17:58

Environment: Visual Studio 2013, .NET 4.5.1, Enterprise Library 6, "Oracle Data Provider for .NET (ODP.NET) Managed Driver" version 121.1.1 from NuGet. Issue: failure constructing Enterprise Library database for Oracle managed client, with errors including "does not have a valid ADO.NET provider name set in the connection string" Solution: added to .config just the "<system.data>" bit in step B3. – Pinta 2/2, 2014 at 17:57

Entity Framework Code First support for ODP.NET ? ODAC 12c release supports Entity Framework DATABASE First, not Code First. any solution about it? – Crib 11/2, 2014 at 11:33

A million times thank you. This solved my bool mapping issues where other posts didn't. Google should redirect here for: Member Mapping specified is not valid. The type 'Edm.Boolean[Nullable=False,DefaultValue=]' of member in type is not compatible with 'OracleEFProvider.number[Nullable=False,DefaultValue=,Precision=1,Scale=0]' of member in type – Blasius 16/5, 2014 at 15:25

Why do you map int16,32,64 twice? Also the mapping seems to be off from Oracle website: docs.oracle.com/cd/E11882_01/win.112/e23174/featLINQ.htm – Christiano 14/8, 2014 at 18:48

@RayCheng see my field size calculations, Oracle assumes the data starts at the DB and the .NET (and CPU) should be able to hold their entire range. I assume that the data starts in an application, is stored in a DB and then returned to an application, therefore the DB types should be large enough to store the entire .NET (and CPU) types. The reason for the duplicate mappings was to enable mapping (of a legacy DB with) wrongly sized DB columns, that were used in FKs to correctly sized DB columns, thus breaking FK constraints in EF, which assumes both sides should be the same .NET type. – Monadelphous 16/8, 2014 at 6:44

Do you know where I can still download: ODP.NET_Managed_1120350_Beta.zip? I am trying to connect to an Oracle 9/10 DB, and the ODP.NET Version 12+ won't connect. The only Version 11 ODT download I see at the Oracle site does not include an Oracle.ManagedDataAccess.dll – Spearhead 14/9, 2017 at 20:2

After attempting to follow: I agree with @Jrop that additional files are needed to work with Oracle 12.1 (thank you). AND if you have the ability or chance to use the NuGet packages, do so. (Requires EF6, so didn't work for me). I also found that if you are trying the XCopy machine-wide install (after trying the above and failing), there are 3 places to check external to code: 1) Registry, 2)WIN Path, 3)Oracle Win Service. Once these work, the oracle drivers will work. If Win Service not started or is giving errors on starting, then you uninstall.bat, re-install, and RESTART – Karisakarissa 8/4, 2020 at 13:28

@Karisakarissa for the newer version, see my other answer regarding managed client. For even newer versions than that, you may need to add an answer if your own, as I haven't used Oracle's DB in years. – Monadelphous 8/4, 2020 at 19:1

Update:

Migrating from Oracle.DataAccess.Client to Oracle.ManagedDataAccess.Client v12.1.0 (12c) the easy way:

Edit: Download link for managed ODAC v12c Release 1.

If you download winx64_12c_client.zip or winnt_12c_client32.zip, extract them and install (full (admin) installation or custom installation with ODP.NET component, client installation doesn't include this component) on a VM, you'll find the folder \odp.net\managed under the "client" folder.

Within this folder you will find common\Oracle.ManagedDataAccess.dll, x86\Oracle.ManagedDataAccessDTC.dll and x64\Oracle.ManagedDataAccessDTC.dll.

Copy Oracle.ManagedDataAccess.dll into your bin directory and either copy the correct platform of Oracle.ManagedDataAccessDTC.dll under your x86/x64 bin directory or, per client, install the correct platform of that specific DLL into the GAC, keeping your bin as AnyCPU.

The assembly version of the new DLL is 4.121.1.0, the PublicKeyToken seems to be the same (didn't check it, but it worked without me changing it).

As before, add this section to your app.config configuration/configSections (at the beginning of the app.config file):

<configuration>
   <configSections>

Section:

    <section name="oracle.manageddataaccess.client"
        type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

and close with:

</configSections>

Under system.data/DbProviderFactories (after the above):

<system.data>
  <DbProviderFactories>

add:

    <remove invariant="Oracle.DataAccess.Client" />
    <remove invariant="Oracle.ManagedDataAccess.Client" />

To make sure you don't have any conflicts in your machine.config

then add:

  <add name="ODP.NET, Managed Driver" invariant="Oracle.DataAccess.Client"
       description="Oracle Data Provider for .NET, Managed Driver"
       type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

and close with:

  </DbProviderFactories>
</system.data>

Notice I used Oracle.DataAccess.Client as the invariant instead of the official Oracle.ManagedDataAccess.Client. This is because I do not want to change all my EDMXs and recompile their assemblies to get them to use the new managed client instead of the old unmanaged one.

Then add the following section (after the above) to your app.config

 <oracle.manageddataaccess.client>
    <version number="*">
      <edmMappings>
        <edmMapping dataType="number">
          <add name="bool" precision="1"/>
          <add name="byte" precision="2" />
          <add name="int16" precision="5" />
        </edmMapping>
      </edmMappings>
    </version>
  </oracle.manageddataaccess.client>

This passed my initial testing, haven't tested thoroughly yet though.

Monadelphous answered 12/8, 2013 at 17:57 Comment(15)

What's about #7820361 ? – Crib 13/8, 2013 at 7:45

Maybe you can added a simple sample using Oracle.ManagedDataAccess.Client. – Crib 13/8, 2013 at 8:45

@Crib For EF No sample needed - works as is due to my trick of registering new provider under the old provider's name. For ADO.NET out of the box, just create a DbConnection of the new type using reflection from the new DLL. – Monadelphous 13/8, 2013 at 11:55

@Kiquenet, until Oracle makes a NuGet package with all 3 DLLs (client, DTC32, DTC64) and gets client to automatically choose correct DLL to link to (by name, using a separate name for each version) according to sizeof(IntPtr), the NuGet package won't be useful. However, thanks for the link - I wasn't aware of the NuGet package. If you work for Oracle, please ask the PM to improve this. – Monadelphous 13/8, 2013 at 11:59

Alex Keh says about Nuget: "We do not plan to put managed ODP.NET on NuGet. We believe that the managed ODP.NET download with ODAC will provide the same benefits of NuGet in terms of assembly isolation and download size. There's a thread discussing whether Oracle should provide managed ODP.NET NuGet support. Once you use ODAC 12c, I would like to know your thoughts on whether NuGet support is still necessary. forums.oracle.com/thread/2559445" – Crib 14/8, 2013 at 8:48

Officially have released ODAC 12c on OTN, which includes ODP.NET, Managed Driver. There are a number of ways to download the managed driver depending on your use case. • If you use Oracle Developer Tools with ODP.NET, Managed Driver, such as with Entity Framework Database First, then download the Oracle Universal Installer ODAC version. • If you want a smaller deployment option that includes other ODAC software, download the ODAC xcopy version. • If you want the smallest possible ODP.NET, Managed Driver download, use the ODP.NET, Managed Driver xcopy version (2 MB zipped). – Crib 21/8, 2013 at 6:54

Does this work with EF CodeFirst? I see references to the EDMX files but I'm using CodeFirst, so not sure if it is going to work at all :( – Seasick 10/9, 2013 at 5:23

Haven't tried it, but if you create the tables manually I don't see why it wouldn't. If you expect it to create the tables programmatically, then you'll have to check if Oracle implemented that option. – Monadelphous 10/9, 2013 at 11:12

@DannyVarod No, tables are already created, although I'm still stuck in the boolean conversion thing, I will try it step by step on a fresh VM and see what happens since in my dev environment did not work. – Seasick 10/9, 2013 at 19:7

What problem are you experiencing with booleans? Is it a run time exception? Did this work with the previous (unmanaged) version? – Monadelphous 10/9, 2013 at 19:25

The infamous "error 2019: Member Mapping specified is not valid. The type 'Edm.Boolean[Nullable=False,DefaultValue=]'" for boolean properties in C# mapped to NUMBER(1,0) columns in Oracle 11g – Seasick 10/9, 2013 at 20:7

Oh and yes it is a runtime exception, code builds just fine, added the whole mapping stuff to the web.config, rebuilt it, added the schema, etc etc. But still got the exception at runtime any time I try to access any entity that has a boolean property. – Seasick 10/9, 2013 at 20:18

Did you notice the new data types mapping section? (Different than in unmanaged version.) Try adding both the new and old mapping sections, in case you are accidentally using the wrong DLL version. – Monadelphous 11/9, 2013 at 14:5

ODAC 12c release supports Entity Framework DATABASE First, not Code First – Crib 11/2, 2014 at 11:33

Alex Keh from Oracle says that in 2014 summer will be available new release ODAC Managed driver supporting Entity Framework Code First. – Crib 13/2, 2014 at 8:21

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags