EF Code First from Database with managed Oracle data access: dynamic connection string creation problems

Question

I am trying to use Code First from Database with the newest EF (6.x) and on an Oracle database. First of all, it works fine as long as the connection string is inside the App.Config file. But when I try to build it dynamically in the C# code (or rather, statically at the moment) it fails. I have it working with a dynamically built connection string when doing Model from Database though, so I'm at a loss right now.

First, I have created a second constructor for the context class that takes a string and does base(connectionString). Then I build the connection string via

OracleConnectionStringBuilder oracleBuilder = new OracleConnectionStringBuilder();
oracleBuilder.DataSource = "TEST.BLA.COM";
oracleBuilder.UserID = "ABC";
oracleBuilder.Password = "abc";
oracleBuilder.PersistSecurityInfo = true;
string connection = oracleBuilder.ToString();

Now trying to open an EntityConnection by giving to it this provider-specific connection string (or even the static one from the App.Config) doesn't work; I get "keyword not supported: user id"). Trying it by creating a context and giving this connection string doesn't work either. I'm pretty sure that this is because I didn't specify the provider to use; after all, it should use the Oracle.ManagedDataAccess.Client provider and not an SQL Server based one.

I then tried to get around this by using an EntityConnectionStringBuilder on top and specifying the provider keyword there, but then I get "keyword not supported: provider" when using it in the context constructor, and "the 'metadata' keyword is always required" when using it with the EntityConnection constructor.

As I said above: I bet it's the provider that I have to specify somehow, but I don't know how. The code that does work is the following:

using (var context = new Model())
{
  context.Database.Connection.Open();
  context.Database.Connection.Close();
}

When I read context.Database.Connection.ConnectionString, it is exactly the provider-specific connection string I created above, but I don't know where to specify the provider again. Do you know of any way to do this? Certainly there must be one.

PS: There seems to be no really fitting tag for EF code first from database yet, so I simply put it in the code first category.

Answer 1

I also had this same issue. Here is what I found to solve the problem.

Oracle installation instructions.

https://docs.oracle.com/cd/E63277_01/win.121/e63268/install.htm#ODPNT0006

Prerequisite install - Software installs from oracle.

http://www.oracle.com/technetwork/developer-tools/visual-studio/overview/index.html

Nuget installs - Entity framework 6, Oracle.ManagedDataAccess.EntityFramework, Oracle.ManagedDataAccess

Microsoft Entity Framework Code First Instructions

https://msdn.microsoft.com/en-us/library/jj200620.aspx

I created a partial class for code first database model class.

using System;
using System.Data.SqlClient;
using System.Linq;

namespace Data.Db
{
    public partial class OracleBlogModel 
    {
        public OracleBlogModel(string connectionString)
            : base(connectionString){}
    }
}

Update the app config, by removing the defaultConnectionFactory from the entityFramework section. The defaultConnectionFactory forces entity to use sql server connection. I have also removed the connection string from the config file.

    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb"/>
      </parameters>
    </defaultConnectionFactory>

Here is what the app config should look like.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework"
      type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
      requirePermission="false"/>
    <section name="oracle.manageddataaccess.client"
      type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
  </configSections>

  <entityFramework>
    <!-- ****************COMMENT defaultConnectionFactory OUT FROM YOUR CONFIG***********************************-->
    <!--<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb"/>
      </parameters>
    </defaultConnectionFactory>-->
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
      <provider invariantName="Oracle.ManagedDataAccess.Client"
        type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <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.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
    </DbProviderFactories>
  </system.data>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <publisherPolicy apply="no"/>
        <assemblyIdentity name="Oracle.ManagedDataAccess" publicKeyToken="89b483f429c47342" culture="neutral"/>
        <bindingRedirect oldVersion="4.121.0.0 - 4.65535.65535.65535" newVersion="4.121.2.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <oracle.manageddataaccess.client>
    <version number="*">
      <dataSources>
        <dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) "/>
      </dataSources>
    </version>
  </oracle.manageddataaccess.client>
  <connectionStrings>
  </connectionStrings>
</configuration>

Now test your connection from unit test project.

using Microsoft.VisualStudio.TestTools.UnitTesting;
using Data.Db;
using System.Linq;


namespace UnitTestProject
{
[TestClass]
public class DataTest
{
    [TestMethod]
    public void Test()
    {

        var sqlConnectionStringBuilder = new SqlConnectionStringBuilder()
        {
            DataSource = "Dynamic Data Source",
            UserID = "Dynamic User ID",
            Password = "Dynamic Password"
        };
        using (var context = new OracleBlogModel(sqlConnectionStringBuilder.ToString()))
        {
            var data = context.Blogs.ToList();
        }
    }
}