Web Api 2, Oracle and Entity Framework

Web Api 2, Oracle and Entity Framework

I spent about the trying of the "to" figure of the expose the Oracle.manageddataaccess 4.121.1.0 library over a Web API 2. 2 OData v4 Endpoint. Looking through the Oracle documentation, Oracle Managed Data Access doesn ' t currently support Entity Framework V6 There is the use of the Entity Framework v5. Devart supplies a Entity Framework 6 compatible product, unfortunately I didn ' t has the time to get budget for this Simpl e Proof of concept and I haven ' t had much luck with trial periods as they never seem to being active when I need them. When Oracle releases the odp.net 12c R3 Libraries later summer, this post would be deprecated as it comes with EF 6 support.

First thing is to the install Entity Framework via NuGet package Manager

Install-Package EntityFramework -Version 5.0.0

Next you need to the install WEBAPI v2.2 with OData support.

Install-Package Microsoft.AspNet.OData

Finally you need to install the Oracle.manageddataaccess library, which is called odp.net.

Install-Package odp.net.managed

Now the all of the packages is installed, the Oracle connection strings need to be setup. I had issues connecting to Oracle 11g with the default "Data Source = xyz; User id= MyUser; Password = abcd123″connection string. I received an error:

ORA-12154: TNS:could not resolve the connect identifier specified

Doing some the "if Oracle can not find the" the list of registered Oracle servers on your network. I remember SQL Server has a discovery protocol similar to this, but I guess that the port numbers is not consistent t The He-they is in the Microsoft world and specify the direct network location in the connection string.

Documentation stated that's should be able to install odp.net and point to the Tnsnames.ora file to get the conn Ection information. the Tnsnames.ora file looks to is configured after the install, so I guess I got lucky there. Consulting with your Oracle DBA could help you locate the information for your server. This stackoverflow post gave me the connection string structure based on the Ora file. You can ' t just copy the values directly in there, and you have a to remove the Alias.

I wanted to configure the connection string in my Web. config file instead of specifying it directly. To does that, you need the Oracle.ManagedDataAccess.Client.OracleClientFactory provider to being specified in the ProviderName field of the configuration file.


<connectionStrings>
<add name="myConnectionString" connectionString="from stackoverflow article" providerName = "Oracle.ManagedDataAccess.Client"/>


Adding this I received a error "Failed to find or load the registered. Net Framework Data Provider." This error occurs when the Oracle.ManagedDataAccess.Client.OracleClientFactory class isn ' t registered in the Web. config or Machine.config. I don ' t like putting assemblies in the GAC on production servers, so I ' m glad this showed up on my workst Ation and not later in the release cycle. Adding the following to the configuration file resolved the error:


<system.data>
<DbProviderFactories>
<add name="Oracle ManagedDataAccess Provider"
invariant="Oracle.ManagedDataAccess.Client"
description=".Net Framework Data Provider for Oracle"
type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess" />
</DbProviderFactories>
</system.data>


With the Entity Framework working, its time-to-focus on WEBAPI. Following any number of tutorials on the subject can get to stated, I used this one.

I made a few tweaks adding odatarouteprefixattribute to my controller and odataroute to my action, but otherwise it's the Same.

Web Api 2, Oracle and Entity Framework