26

I am trying to implemente code First Migrations with Oracle.ManagedDataAccess 6.121.1.0 provider, but with no success at all.

As I am receiving a ORA-code, I am assuming that the connection are been opened successfully. But the Migrations are failing because, maybe, the provider are behaving as a SQL Server, instead of Oracle. I think that beacause it is traying to use 'dbo' as default schema.

Here is my web.config settings:

<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.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
  </configSections>
  <entityFramework>
    <contexts>
      <context type="MyProject.Context.MainContext, MyProject.Context">
        <databaseInitializer type="MyProject.Context.Config.ContextInitializer, MyProject.Context" />
      </context>
    </contexts>
    <defaultConnectionFactory type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess" />    
    <providers>    
      <provider invariantName="Oracle.ManagedDataAccess.Client" 
                type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.1.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.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    </DbProviderFactories>
  </system.data>
  <connectionStrings>
    <add name="MainContext"
         providerName="Oracle.ManagedDataAccess.Client"
         connectionString="Data Source=OracleServer:1521/BRSYSDS;User ID=USER;Password=PASSWORD;" />
  </connectionStrings>
  <!-- other settings -->
</configuration>

Here the Stacktrace:

[OracleException (0x77e): ORA-01918: user 'dbo' does not exist]
OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone) +652 OracleInternal.ServiceObjects.OracleCommandImpl.VerifyExecution(OracleConnectionImpl connectionImpl, Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, OracleException& exceptionForArrayBindDML, Boolean bFirstIterationDone) +39
OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteNonQuery(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, OracleException& exceptionForArrayBindDML, Boolean isFromEF) +7480
Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery() +678
System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.b__0(DbCommand t, DbCommandInterceptionContext1 c) +10
System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch(TTarget target, Func3 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed) +72
System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) +357
System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery() +104
System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement, DbInterceptionContext interceptionContext) +152
System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext) +82
System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable1 migrationStatements, DbConnection connection) +626
System.Data.Entity.Migrations.<>c__DisplayClass30.<ExecuteStatements>b__2e() +19
System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute(Action operation) +9
System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements, DbTransaction existingTransaction) +194
System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements) +7
System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable1 operations, IEnumerable1 systemOperations, Boolean downgrading, Boolean auto) +825
System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, VersionedModel sourceModel, VersionedModel targetModel, Boolean downgrading) +564
System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId) +404
System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration) +447
System.Data.Entity.Migrations.<>c__DisplayClassc.b__b() +13
System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) +422
System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) +78
System.Data.Entity.Internal.DatabaseCreator.CreateDatabase(InternalContext internalContext, Func3 createMigrator, ObjectContext objectContext) +89
System.Data.Entity.Internal.InternalContext.CreateDatabase(ObjectContext objectContext, DatabaseExistenceState existenceState) +116
System.Data.Entity.Database.Create(DatabaseExistenceState existenceState) +218
System.Data.Entity.DropCreateDatabaseAlways1.InitializeDatabase(TContext context) +137

Thiago Lunardi
  • 749
  • 1
  • 5
  • 19

9 Answers9

44

I had the same problem and it was resolved by Thiago Lunardi's response. Thank you. I didn't have enough reputation to vote up your response. To mention here, I succeeded after setting my schema name in UPPERCASE.

Put this in your Context file under your new dbContext class, like this:

public partial class MyAppContext : DbContext
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("UPPERCASE_SCHEMA_NAME");
...
IAmInPLS
  • 4,051
  • 4
  • 24
  • 57
Отгонтөгс Миймаа
  • 753
  • 11
  • 23
  • Voted as the answer to avoid vote my own. :) tks Отгонтөгс Миймаа – Thiago Lunardi Dec 03 '14 at 18:36
  • 1
    Uppercase is the key. I had the same problem. – kpull1 Mar 04 '15 at 16:06
  • where do I insert this? – CularBytes Apr 25 '15 at 17:00
  • @RageCompex at you DbContext definition. `public class MainContext : DbContext, IDbContext { protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.HasDefaultSchema("VSN"); //... } }` – Thiago Lunardi Aug 13 '15 at 14:47
  • UPPER CASE it is! I am trying new project to use Entity Framework (EF) Code First with Oracle Data Provider for .NET (ODP.NET). I have following code in .Net. User "SA" needs to be uppercase. { modelBuilder.HasDefaultSchema("SA"); } – Kathy Lee Mar 16 '16 at 00:09
18

I solve this just setting the default schema at modelBuilder

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

    // ...
}
Thiago Lunardi
  • 749
  • 1
  • 5
  • 19
2

Setting default schema didn't work for me. I found the solution by customizing migrations history table to set a different schema.

You can find a solution here: LINK.

VeRo
  • 81
  • 1
  • 9
2

User Dbo also comes in case of missing fully qualified name of the Table. Which may not map to the right Table in the database.

enter image description here

emecas
  • 1,586
  • 3
  • 27
  • 43
Aditya
  • 171
  • 1
  • 5
1

If you use Automatic Migrations (as I was), then note: modelBuilder.HasDefaultSchema whouldn't help until you switch to explicit migrations.

From Oracle Docs:

Code First Automatic Migrations is limited to working with the dbo schema only. Due to this limitation it is recommended to use code-based migrations, that is, add explicit migrations through the Add-Migration command
Alexey Merson
  • 414
  • 5
  • 12
1

in Code First you can use the DataAnnotations for Table .

[Table("Emplpoyee",Schema="YOUR SCHEMA NAME"]

Faisal Saleem
  • 11
  • 1
1

I had the same problem. I placed my schema name in OnModelCreating() method.

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

    // ...
}

But, setting schema name in UPPERCASE didn't work for me. I added below mentioned code in Confifuration.cs and it worked !!

Go to Migrations -> Configuration.cs

 class Configuration : DbMigrationsConfiguration<CodeFirstOracleProject.Context>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        var historyContextFactory = GetHistoryContextFactory("Oracle.ManagedDataAccess.Client");
        SetHistoryContextFactory("Oracle.ManagedDataAccess.Client",
                                 (dbc, schema) => historyContextFactory.Invoke(dbc, "YourSchemaName"));
    }
}
Hamid Mahmood
  • 31
  • 6
0

In my case writing schema name in Uppercase wasn't sufficient I had to use toUpper() function as such :

 modelBuilder.HasDefaultSchema("YOURSCHEMA".ToUpper())

alongside adding

   public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        var historyContextFactory = GetHistoryContextFactory("Oracle.ManagedDataAccess.Client");
        SetHistoryContextFactory("Oracle.ManagedDataAccess.Client",
                                 (dbc, schema) => historyContextFactory.Invoke(dbc, "YOURSCHEMA".ToUpper()));
    }

removed the migrations and regenerating them fixed the issue.

Badr Bellaj
  • 11,560
  • 2
  • 43
  • 44
0

As a beginner, the major issue I had with the answers here was, what does user 'dbo' has to do with schema name.

After researching, here is what I found.

In oracle, A Schema is a collection of database objects. A schema is owned by a database user and has the same name as the user.

The default schema for entity framework is however dbo, and you can override this as in the code listing below:

  modelBuilder.HasDefaultSchema("YOURSCHEMA".ToUpper())

For oracle, "YOURSCHEMA" has to be the user_id for the database you are connected to.

Then you need to add the below to your configuration file

public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        var historyContextFactory = GetHistoryContextFactory("Oracle.ManagedDataAccess.Client");
        SetHistoryContextFactory("Oracle.ManagedDataAccess.Client",
                                 (dbc, schema) => historyContextFactory.Invoke(dbc, "YOURSCHEMA".ToUpper()));
    }

Finally, delete the migration files generated and rerun Add-Migration again.

I hope this will help somebody.

caged_raven_wings
  • 113
  • 2
  • 9