I'm developing an application where information will be persisted in two databases, one MS SQL Server and one Oracle.
I have no option to use the two 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 default DBMS.
The application will initially persist in an MS SQL Server database and at some point in the usage flow will need to persist some (not necessarily the same) information 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, I will probably use "simple injector".
My approach to the Sql Server database is "code first" with "Migrations". For the Oracle database I will create the entity classes, entity configuration, context and etc. manually even though the database already exists.
Below is the web.config of the presentation layer:
<?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 the "Oracle Developer Tools for Visual Studio 2013" and both in 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 configuring the Oracle part the Sql Server data layer was working properly. I created the whole template and did the database generation with "Migrations", however after starting to configure Oracle I can no longer update the Sql Server model. Whenever I try to add a new template configuration with Add-Migration I get 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 assembly is available to the running application. See link for more information. '
Has anyone ever had a similar situation and can you give me a hint of what might be wrong?