5 Replies Latest reply on Oct 31, 2019 4:25 PM by Alex Keh - Product Manager-Oracle

    Oracle.EntityFrameworkCore - Bug setting the schema

    ac92fd24-88ca-43d3-b985-5ba9c71c4a3a

      I'm using the latest preview of Oracle.EntityFrameworkCore, and I've run into an issue when trying to explicitly call out the schema that the DbContext should be pointed to. Looks like there is some bad SQL generated for accessing user_tables object. It's related to setting the schema on the migration history table. My goal, is to eventually have an application that can use my library to run the migration with the schema account, while also using the library in a WebApi that connects with an application user.

       

       

      Code to create the context:

       

      static main(){

       

              DbContextOptionsBuilder<PracticeDbContext> builder = new DbContextOptionsBuilder<PracticeDbContext>();

              builder

                     .UseLoggerFactory(_loggerFactory)

                     .UseOracle(_config.ConnectionString

                     ,x => x.MigrationsHistoryTable(HistoryRepository.DefaultTableName, PracticeConfiguration._SCHEMA)

                     );

                  var context = new PracticeDbContext(builder.Options);

      }

       

          public class PracticeDbContext : DbContext

          {

              public PracticeDbContext(DbContextOptions<PracticeDbContext> options)

                 : base(options)

              {

              }

              protected override void OnModelCreating(ModelBuilder builder)

              {

                  base.OnModelCreating(builder);

                  builder.HasDefaultSchema(PracticeConfiguration._SCHEMA);

            }

      }

       

      2019/06/21 10:51:39.836|INFO|Entity Framework Core 2.2.4-servicing-10062 initialized 'PracticeDbContext' using provider 'Oracle.EntityFrameworkCore' with options: MigrationsHistoryTable=SCH_PRACTICE.__EFMigrationsHistory using lazy-loading proxies  |Microsoft.EntityFrameworkCore.Infrastructure|version=2.2.4-servicing-10062, contextType=PracticeDbContext, provider=Oracle.EntityFrameworkCore, options=MigrationsHistoryTable=SCH_PRACTICE.__EFMigrationsHistory using lazy-loading proxies , EventId_Id=10403, EventId_Name=Microsoft.EntityFrameworkCore.Infrastructure.ContextInitialized, EventId=Microsoft.EntityFrameworkCore.Infrastructure.ContextInitialized

      2019/06/21 10:51:39.975|INFO|2019-06-21 10:51:39.973671 ThreadID:1   (CONN)    OracleDatabaseCreator.Exists() : user: SCH_PRACTICE, data source: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.27.198.10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe))) |Microsoft.EntityFrameworkCore.Database|

      2019/06/21 10:51:40.872|INFO|2019-06-21 10:51:40.870679 ThreadID:1   (CONN)    OracleDatabaseCreator.Exists() : user 'SCH_PRACTICE'exists |Microsoft.EntityFrameworkCore.Database|

      2019/06/21 10:51:40.936|INFO|2019-06-21 10:51:40.934298 ThreadID:1   (SQL)     OracleRelationalCommand.Execute() : SELECT t.table_name FROM user_tables t WHERE t.table_name = N'__EFMigrationsHistory' AND t.owner = N'SCH_PRACTICE' |Microsoft.EntityFrameworkCore.Database.Command|

      2019/06/21 10:51:41.028|ERROR|2019-06-21 10:51:41.026164 ThreadID:1   (ERROR)   OracleRelationalCommand.Execute() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "T"."OWNER": invalid identifier

         at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)

         at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteScalar()

         at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues) |Microsoft.EntityFrameworkCore.Database.Command|

      2019/06/21 10:51:41.028|ERROR|Failed executing DbCommand (96ms) [Parameters=[], CommandType='Text', CommandTimeout='0']

      SELECT t.table_name FROM user_tables t WHERE t.table_name = N'__EFMigrationsHistory' AND t.owner = N'SCH_PRACTICE' Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "T"."OWNER": invalid identifier

         at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)

         at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteScalar()

         at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)|Microsoft.EntityFrameworkCore.Database.Command|elapsed=96, commandType=Text, commandTimeout=0, newLine=

      , commandText=SELECT t.table_name FROM user_tables t WHERE t.table_name = N'__EFMigrationsHistory' AND t.owner = N'SCH_PRACTICE', EventId_Id=20102, EventId_Name=Microsoft.EntityFrameworkCore.Database.Command.CommandError, EventId=Microsoft.EntityFrameworkCore.Database.Command.CommandError

      2019/06/21 10:51:41.054|ERROR|2019-06-21 10:51:41.053105 ThreadID:1   (ERROR)   OracleRelationalCommand.Execute() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "T"."OWNER": invalid identifier

         at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)

         at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteScalar()

         at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues) |Microsoft.EntityFrameworkCore.Database.Command|

      2019/06/21 10:51:44.819|FATAL|Application startup exception Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "T"."OWNER": invalid identifier

         at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)

         at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader()

         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteScalar()

         at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)

         at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteScalar(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)

         at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists()

         at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)

         at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)

         at Practice.Data.PracticeDbProvider.Migrate() in C:\development\practice\Practice\Data\PracticeDbProvider.cs:line 56

         at PracticeApi.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env, IPracticeDbProvider dbProvider) in C:\development\practice\PracticeApi\Startup.cs:line 66

      --- End of stack trace from previous location where exception was thrown ---

         at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app)

         at Microsoft.AspNetCore.Mvc.Internal.MiddlewareFilterBuilderStartupFilter.<>c__DisplayClass0_0.<Configure>g__MiddlewareFilterBuilder|0(IApplicationBuilder builder)

         at Microsoft.AspNetCore.Server.IIS.Core.IISServerSetupFilter.<>c__DisplayClass2_0.<Configure>b__0(IApplicationBuilder app)

         at Microsoft.AspNetCore.HostFilteringStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder app)

         at Microsoft.AspNetCore.Hosting.Internal.AutoRequestServicesStartupFilter.<>c__DisplayClass0_0.<Configure>b__0(IApplicationBuilder builder)

         at Microsoft.AspNetCore.Hosting.Internal.WebHost.BuildApplication()|Microsoft.AspNetCore.Hosting.Internal.WebHost|EventId_Id=6, EventId=6