11G .net core 3.1 beta | linq query exception when relating classes

[CoSMtnDog]

I was unable to reopen and I'm unsure if you get notifications so opening a new one for 11G. Apologies if I'm doing this wrong.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

` public class DaleContext : DbContext
{
public DaleContext()
{
ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}

public DaleContext(DbContextOptions options) : base(options)
{
  ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}

public DaleContext(DbContextOptions<SecurityContext> options) : base(options)
{
  ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}
public DbSet<BookingBlock> BookingBlocks { get; set; }
public DbSet<CustomerGroup> CustomerGroups { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
  //leaving this for now to use for direct db integration/validation in unit test
  if (!optionsBuilder.IsConfigured)
  {
    //if this does remain, then pull data source string from json file where it's NOT stored in git
    optionsBuilder
      .UseLoggerFactory(ConsoleLoggerFactory)
      .UseOracle("Data Source = xxxx;"
      , b => b.UseOracleSQLCompatibility("11"));
  }
}

public static readonly ILoggerFactory ConsoleLoggerFactory =
  LoggerFactory.Create(builder =>
  {
    builder.AddFilter((category, level) =>
      category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();
  });

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  modelBuilder.Entity<BookingBlock>().ToTable("PDF_BOOKING_BLOCK");
  modelBuilder.Entity<BookingLimit>().ToTable("PDF_BOOKING_LIMIT");
  modelBuilder.Entity<CustomerGroup>().ToTable("PDF_CUST_GROUP");

  //one to many
  modelBuilder.Entity<CustomerGroup>().HasMany(cg => cg.BookingBlocks).WithOne(bb => bb.CustomerGroup);

  //tables w/ composite keys
  modelBuilder.Entity<BookingBlock>()
      .HasKey(pk => new { pk.RPTNG_FLOW_PATH_ID, pk.CUST_GROUP_IDFTN });

}`

public class CustomerGroup { public CustomerGroup() { BookingBlocks = new List<BookingBlock>(); } [Key] public int CUST_GROUP_IDFTN { get; set; } public string CUST_GROUP_NAME { get; set; } public string CUST_GROUP_DESC { get; set; } public string ENABLED_INDIC { get; set; } public string LAST_CHNGD_IDFTN { get; set; } public DateTime DATE_CRTD { get; set; } public DateTime DATE_LAST_CHNGD { get; set; } public List<BookingBlock> BookingBlocks { get; set; } }

public class BookingBlock { public int RPTNG_FLOW_PATH_ID { get; set; } public int CUST_GROUP_IDFTN { get; set; } public int BLOCK_MIN_LEAD_TIME { get; set; } public int BLOCK_BKNG_WEEKS { get; set; } public string ENABLED_INDIC { get; set; } public int SORT_SEQ { get; set; } public string LAST_CHNGD_IDFTN { get; set; } public DateTime DATE_CRTD { get; set; } public DateTime DATE_LAST_CHNGD { get; set; } public CustomerGroup CustomerGroup { get; set; } }

1st test method

This test method used to work but after upgrade to 11G, it no longer does a simple count:
NOTE: When I comment out the fluent relationship and the CustomerGroup property from BookBlock, then the first test method works.
[TestMethod] public void GetBookBlockCountExample() { using (var context = new DaleContext()) { var bookingBlocks = context.BookingBlocks.ToList(); Console.WriteLine($"count {bookingBlocks.Count}"); } }

Exception for 1st test method

Message:
Test method Arconic.UnitTest.Core.DaleUnitTest.GetBookBlockCountExample threw exception:
Oracle.ManagedDataAccess.Client.OracleException: ORA-00904: "p"."CustomerGroupCUST_GROUP_IDFTN": invalid identifier
Stack Trace:
OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
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, IEnumerable1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF) OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) DbCommand.ExecuteReader() OracleRelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) Enumerator.InitializeReader(DbContext _, Boolean result) OracleExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) Enumerator.MoveNext() List1.ctor(IEnumerable1 collection) Enumerable.ToList[TSource](IEnumerable1 source)
DaleUnitTest.GetBookBlockCountExample() line 29

This is the test method I used w/ 10G and it responds the same in 11G. Could it be the composite key of the child class BookBlock?

2nd test method

[TestMethod] public void Get_AmCastleCompany_FlowPath_27NFT_BookingWeeks() { //relationships not working in 10G because ODP using (var context = new DaleContext()) { var customer = context.CustomerGroups.Where(cg => cg.CUST_GROUP_IDFTN == 275 && cg.BookingBlocks.FirstOrDefault().RPTNG_FLOW_PATH_ID == 2).ToList(); Assert.AreEqual(1, customer.Count); } }

The exception for 2nd test method:

Message:
Test method Arconic.UnitTest.Core.DaleUnitTest.Get_AmCastleCompany_FlowPath_27NFT_BookingWeeks threw exception:
Oracle.ManagedDataAccess.Client.OracleException: ORA-00904: "p0"."CustomerGroupCUST_GROUP_IDFTN": invalid identifier
Stack Trace:
OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
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, IEnumerable1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF) OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) OracleCommand.ExecuteDbDataReader(CommandBehavior behavior) DbCommand.ExecuteReader() OracleRelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject) Enumerator.InitializeReader(DbContext _, Boolean result) OracleExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) Enumerator.MoveNext() List1.ctor(IEnumerable1 collection) Enumerable.ToList[TSource](IEnumerable1 source)
DaleUnitTest.Get_AmCastleCompany_FlowPath_27NFT_BookingWeeks() line 62

[alexkeh]

Since we're on GitHub, can you put your test case into a GitHub repository? I could then download the project and run it myself to reproduce and my team to debug if it comes to that. One thing I would like to see is the SQL generated.

[CoSMtnDog]

my project structure is a solution w/ 8 projects so I wouldn't be able to provide that one.

I'm using vs2019, EF Core 3.1, Oracle's ODP.NET EF Core Beta.

Should be easy enough to copy past the contents into a project you already have. The only thing you'd need then are two tables w/ the noted properties.

For the three classes involved, it's as you see it above. No other magic being done with the example I shared.

Two Model Classes - BookingBlock and CustomerGroup (as noted above)
DBContext Class - DaleContext (noted above)
Unit Test Methods I shared.

[CoSMtnDog]

I've replicated the project structure with just the items in question. I've tested it to ensure I get the same exception as in my original solution.

https://github.com/JQ-IOWA/DebugProjectForOdpNetTeam

[alexkeh]

I got your test case working against a 19c DB without the error. I'll get a hold of 11g test DB tomorrow and try it out.

[alexkeh]

I reproduced the problem and found a solution.

In OnModelCreating, add the following line:
modelBuilder.HasAnnotation("Relational:MaxIdentifierLength", 30);

Oracle DBs prior to version 12.2 have a 30 character limit on object names, which includes quotes. Quotes are appended to the EF Core object names, which put your 29 character object over the limit.

Once I set the MaxIdentifierLength property to 30, I was able to create your schema objects and execute the test you provided.

[CoSMtnDog]

I can also confirm that adding that line worked with the my solution as well.

[CoSMtnDog]

As an aside, this also worked w/ the unsupported 10G. Although, we are in the process of upgrading towards 19 in the near future.

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

[CoSMtnDog]

I stand corrected. Apparently the 11G instance was still up. Ran the same test the next day when db was reverted back to 10G and the query did NOT run as it did w/ 11G.