How to use an Oracle database and Sql Server simultaneously with Entity Framework 6 in a single application?

Question

I am developing an application where the information will be persisted in two databases, one MS SQL Server and one Oracle.

I have no option to use both banks in the same DBMS, because the Oracle database is from another application and another server under which I have no control and the SQL server is our company’s standard DBMS.

The application will initially persist in a MS SQL Server database and at a certain point in the usage flow will need to persist some information (not necessarily the same) in the Oracle database.

It is an ASP.Net MVC 5 application with Entity Framework 6.1.3.

My architecture is divided into layers, following the following structure:

1. Presentation (Solution folder)  
    |-- Presentation.Web (Projeto Asp.net MVC 5)  
2. Domain (Solution folder)  
    |-- Domain.SqlServer (Projeto class library)  
    |-- Domain.Oracle (Projeto class library)  
3. Infraestructure (Solution folder)  
    |-- Crosscutting (Solution folder)  
        |-- Infraestructure.Crosscutting.IoC (Projeto class library)  
    |-- Data (Solution folder)  
        |-- Infraestructure.Data.SqlServer (Projeto class library)  
        |-- Infraestructure.Data.Oracle (Projeto class library)  

All references between "assemblies" will be created by a dependency injection "container", probably using "simple injector".

My approach to the Sql Server database is "code first" with "Migrations". For the Oracle database I will create entity classes, entity configuration, context and etc manually although the database already exists.

Follow below as is the presentation layer web.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <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>
  <appSettings>
    <add key="webpages:Version" value="3.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <entityFramework>
    <!--<defaultConnectionFactory type="Oracle.ManagedDataAccess.EntityFramework.OracleConnectionFactory, Oracle.ManagedDataAccess.EntityFramework" />-->
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"/>
    <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>
  <Oracle.ManagedDataAccess.Client>
    <version number="*">
      <dataSources>
        <dataSource alias="OracleDataSource" descriptor="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Servidor)(PORT=Porta)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=NomeServico)))" />
      </dataSources>
    </version>
  </Oracle.ManagedDataAccess.Client>
  <connectionStrings>
    <add name="SqlServerDbContext" providerName="System.Data.SqlClient" connectionString="Server=Servidor; Database=BancoDeDados; Uid=Usuario; Pwd=Senha;" />
    <add name="OracleDbContext" providerName="Oracle.ManagedDataAccess.Client" connectionString="User Id=Usuario; Password=Senha; Data Source=OracleDataSource;" />
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.6" />
    <httpRuntime targetFramework="4.6" />
    <authentication mode="Windows" />
    <authorization>
      <deny users="?" />
    </authorization>
  </system.web>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Newtonsoft.Json" culture="neutral" publicKeyToken="30ad4fe6b2a6aeed" />
        <bindingRedirect oldVersion="0.0.0.0-9.0.0.0" newVersion="9.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="0.0.0.0-1.6.5135.21930" newVersion="1.6.5135.21930" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-5.2.3.0" newVersion="5.2.3.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="Antlr3.Runtime" publicKeyToken="eb42632606e9261f" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-3.5.0.2" newVersion="3.5.0.2" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

I also installed "Oracle Developer Tools for Visual Studio 2013" and in both the presentation layer and the Oracle data layer I installed the following nuget packages:

• Entityframework 6.1.3
• Oracle.Manageddataaccess 12.1.2400
• Oracle.ManagedDataAccess.Entityframework 12.1.2400

While I had not started the Oracle part configuration the Sql Server data layer was working properly. I created the entire model and generated the database with "Migrations", however after starting to configure Oracle I can no longer update the Sql Server model. Whenever I try to add a new model configuration with Add-Migration returns me the following message:

The Entity Framework Provider type 'Oracle.ManagedDataAccess.Entityframework.Eforacleproviderservices, Oracle.ManagedDataAccess.Entityframework, Version=6.121.2.0, Culture=neutral, Publickeytoken=89b483f429c47342' Registered in the application config file for the ADO.NET Provider with invariant name 'Oracle.ManagedDataAccess.Client' could not be Loaded. Make sure that the Assembly-Qualified name is used and that the Assembly is available to the running application. See http://go.microsoft.com/fwlink/? Linkid=260882 for more information.`

Has anyone ever been through a similar situation and can give me a hint of what might be wrong?

Answer 1

Delete the tag:

<provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

then check whether the error will persist. NOTE: make a backup of the file before.