CODEWITHSUNDEEP
oracle-databaseentity-frameworkcode-firstentity-framework-migrationsodp.net
Thiago Lunardi
Thiago Lunardi

Reputation: 749

Oracle.ManagedDataAccess.EntityFramework - ORA-01918: user 'dbo' does not exist

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

Upvotes: 26

Views: 21202

Answers (9)

caged_raven_wings
caged_raven_wings

Reputation: 363

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.

Upvotes: 0

Badr Bellaj
Badr Bellaj

Reputation: 12851

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.

Upvotes: 0

Hamid Mahmood
Hamid Mahmood

Reputation: 31

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"));
    }
}

Upvotes: 1

Aditya
Aditya

Reputation: 191

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

Upvotes: 2

Отгонтөгс Миймаа
Отгонтөгс Миймаа

Reputation: 788

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");
...

Upvotes: 44

Faisal Saleem
Faisal Saleem

Reputation: 11

in Code First you can use the DataAnnotations for Table .

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

Upvotes: 1

Alexey Merson
Alexey Merson

Reputation: 414

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

Upvotes: 1

VeRo
VeRo

Reputation: 81

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.

Upvotes: 2

Thiago Lunardi
Thiago Lunardi

Reputation: 749

I solve this just setting the default schema at modelBuilder

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

    // ...
}

Upvotes: 18

Related Questions