CODEWITHSUNDEEP
c#oracle-databaseentity-frameworkentity-framework-6c#-interactive
N. Carrer
N. Carrer

Reputation: 148

Entity framework Code-First in c# interactive window (Oracle)

I'm trying to use the c# interactive window to quickly test some of my code. I run into troubles when I try to test code related with DbContext (EF6).

I understand that I need to pass the connection string since the interactive window won't load the App.config file, so I have overridden the constructor that specifies the connection string.

I have also to say that I am using the Oracle database with the ODP.NET provider.

Here is the code I'm trying to run on the interactive window:

#r "MyProjPath\bin\Debug\CsCore.EntityDomain.dll"
#r "MyProjPath\bin\Debug\EntityFramework.dll"
#r "MyProjPath\bin\Debug\EntityFramework.SqlServer.dll"
#r "MyProjPath\bin\Debug\Oracle.ManagedDataAccess.dll"
#r "MyProjPath\bin\Debug\Oracle.ManagedDataAccess.EntityFramework.dll"
var ctx = new CsCore.EntityDomain.Pivot.PivotContext("Data Source=MyDataSource;User Id=MyUser;Password=MyPassword;");
ctx.ReconciliationRules.FirstOrDefault()

and this is the exception I get

The underlying provider failed on Open.
  + System.Data.Entity.Core.EntityClient.EntityConnection.Open()
  + System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(bool)
  + System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction<T>(Func<T>, System.Data.Entity.Infrastructure.IDbExecutionStrategy, bool, bool)
  + System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute<TResult>(Func<TResult>)
  + ObjectQuery<T>.GetResults(Nullable<System.Data.Entity.Core.Objects.MergeOption>)
  + LazyEnumerator<T>.MoveNext()
  + System.Linq.Enumerable.FirstOrDefault<TSource>(IEnumerable<TSource>)
  + System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle<TResult>(IEnumerable<TResult>, System.Linq.Expressions.Expression)
  + System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute<TResult>(System.Linq.Expressions.Expression)
  + System.Data.Entity.Internal.Linq.DbQueryProvider.Execute<TResult>(System.Linq.Expressions.Expression)
  + System.Linq.Queryable.FirstOrDefault<TSource>(IQueryable<TSource>)

My DbContext looks like this:

public class PivotContext : DbContext
{
    public virtual DbSet<PivotReconciliationRule> ReconciliationRules { get; set; }

    public PivotContext() : this("name=myConnectionStringName")
    {
    }

    public PivotContext(string nameOrConnectionString) : base(nameOrConnectionString)
    {
        Database.SetInitializer<PivotContext>(null);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("MYSCHEMA");

        base.OnModelCreating(modelBuilder);
    }
}

I think that the reason is that I'm not able to specify the correct provider. Has someone managed to use code-first EntityFramework code (on oracle) working from the c# interactive window?

Many thanks in advance.

Nicola

Upvotes: 0

Views: 783

Answers (2)

N. Carrer
N. Carrer

Reputation: 148

I finally managed to get it working.

First of all I was receiving the message "The underlying provider failed on Open." because EF was not able to get the correct provider and connection factory. It was in fact trying to use the SqlServer connection factory instead than the one from Oracle. In a database-first approach you could have created the DbContext from an EntityConnection, which specifies the provider. Unfortunately it seems that in this case you must provide as well the model, which is not applicable to code-first.

At the end I solved the provider/factory setup following the infos in this post. The corresponding csx code is:

System.Data.Entity.DbConfiguration.Loaded += (_, a) =>
{
    var services = Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices.Instance;
    a.ReplaceService<System.Data.Entity.Core.Common.DbProviderServices>((s, k) => services);
    var factory = new Oracle.ManagedDataAccess.EntityFramework.OracleConnectionFactory();
    a.ReplaceService<System.Data.Entity.Infrastructure.IDbConnectionFactory>((s, k) => factory);
};

After doing that I still got the following error: Unable to determine the provider name for provider factory of type 'Oracle.ManagedDataAccess.Client.OracleClientFactory'. Make sure that the ADO.NET provider is installed or registered in the application config.

This was due to fact that the OracleClientFactory was not associated to an entity framework provider (from section "entityFramework"). I wasn't able to do this directly from code. I ended up adding the "entityFramework" section to my machine.config:

Declaration of the 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.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

configSection content:

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
    <parameters>
      <parameter value="mssqllocaldb" />
    </parameters>
  </defaultConnectionFactory>
  <providers>
    <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
  </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.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    ... other staff that was already there
  </DbProviderFactories>
</system.data>

With those configs in place I was finally able to use my Oracle code-first EF6 code in the c# interactive window.

Upvotes: 1

james
james

Reputation: 743

First of all, you can use your App.Config and your connection strings from your app config : 

var AppConfig = System.Configuration.ConfigurationManager.OpenExeConfiguration(@"bin\Debug\MyApp.exe");
Console.WriteLine($"Loaded {AppConfig.ConnectionStrings.ConnectionStrings.Count} connection strings");

In general, you need to implement the OnConfiguring method in your DbContext (here for postgresql in core 2.0): 

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseNpgsql("Host=localhost; Database=mydb; User ID=myuser; Password=mypasswd; Port=0000; ");
}

OnConfiguring is called the first time you use the context.

I would need your DbContext, to help you more.

Upvotes: 0

Related Questions