ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Oracle.EntityFrameworkCore - Bug setting the schema

ac92fd24-88ca-43d3-b985-5ba9c71c4a3aJun 21 2019 — edited Oct 31 2019

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 Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

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

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

, 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 Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)

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

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

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

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 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

Alex Keh-Oracle

There is a known bug (29443379/28963848) that prevents scaffolding to a different schema than the logged in user. That bug has recently been fixed and will be in the production release.

4105651

HI Alex,

I am facing same issue with production release (2.19.30). Is there any workaround for this?

Alex Keh-Oracle

There is a new bug 30352492 with similar symptoms that has just been fixed. No ETA yet on when we will make a version with this patch available on NuGet Gallery, but you can contact Oracle Support for a one-off if you need it ASAP.

User_FLIQ9

Hi Alex,

Is this fixed in 2.19.50?

Thanks for information in advance

Alex Keh-Oracle

The bug fix is not in 2.19.5. It will be available in the next patch release.

1 - 5

Added on Jun 21 2019

#odp-net

5 comments

3,307 views

ODP.NET

Oracle.EntityFrameworkCore - Bug setting the schema

Comments

Post Details