EF Core invalid generated query

[tomasfil]

Hello, I have come across bug with EF Core Linq to entities.
This query:

Query
    .Select(s=> new ValueSet<JobLine, List<ValueSet<Transaction, List<string>>>>
    {
        Value1 = s,
        Value2 =s.Transactions!.Select(x=>
        new ValueSet<Transaction, List<string>>
        {
            Value1 = x,
            Value2 = x.PairStatuses!
                                .Where(ps => (x.CreatedDate > ps.StartDate && x.CreatedDate < ps.EndDate) || (x.UpdatedDate > ps.StartDate && x.UpdatedDate < ps.EndDate))
                                .Select(s => s.User!.FullName)
                                .ToList()
        }).ToList()
    })
    .Where(jl => jl.CaseNo == caseNo)
    .Include(x => x.JobLineItems!)
    .ThenInclude(x => x.EbsItem!)
    .Include(x => x.JobHeader!)
    .ThenInclude(x => x.RequestType!)
    .Include(x=>x.Transactions!)
    .ThenInclude(x => x.TransactionItems)
    .AsNoTrackingWithIdentityResolution();

Results into this sql

SELECT "t"."ID", "t"."ATTRIBUTE1", "t"."ATTRIBUTE2", "t"."ATTRIBUTE3", "t"."ATTRIBUTE4", "t"."ATTRIBUTE5", "t"."ATTRIBUTE6", "t"."CASE_NO", "t"."CREATED_BY", "t"."CREATED_DATE", "t"."FROM_INVENTORY", "t"."FROM_LOCATOR", "t"."JOB_HEADER_ID", "t"."MACHINE_ID", "t"."PICKING_ORDER", "t"."PRINTED", "t"."ROUTING_ID", "t"."SOURCE_LINE_ID", "t"."STATUS", "t"."TO_INVENTORY", "t"."TO_LOCATOR", "t"."UPDATED_BY", "t"."UPDATED_DATE", "t"."VALID", "t"."WINDOWS_USER", "t0"."ID", "v"."ID", "t1"."ID", "t1"."APP_ID", "t1"."APPL_REV", "t1"."ATTRIBUTE1", "t1"."ATTRIBUTE2", "t1"."ATTRIBUTE3", "t1"."ATTRIBUTE4", "t1"."ATTRIBUTE5", "t1"."ATTRIBUTE6", "t1"."CONTENT_ID", "t1"."CREATED_BY", "t1"."CREATED_DATE", "t1"."DEVICE_IP", "t1"."INVENTORY_ITEM_ID", "t1"."ITEM", "t1"."JOB_LINE_ID", "t1"."LOT_NO", "t1"."QUANTITY", "t1"."SHIPPING_QUANTITY", "t1"."SOURCE_LINE_ITEM_ID", "t1"."UOM", "t1"."UPDATED_BY", "t1"."UPDATED_DATE", "t1"."VALID", "t1"."VENDOR_LOT_NO", "t1"."WINDOWS_USER", "t1"."ID0", "t1"."BUILD_IN_WIP_FLAG", "t1"."CLASS", "t1"."DESCRIPTION", "t1"."EXPIRATION_DAYS", "t1"."FIXED_LOT_MULTIPLIER", "t1"."INITIAL_GOKI", "t1"."INVENTORY_ITEM_STATUS_CODE", "t1"."ITEM0", "t1"."ITEM_NO", "t1"."ITM_CREATED_BY", "t1"."ITM_CREATION_DATE", "t1"."ITM_UPDATE_DATE", "t1"."ITM_UPDATED_BY", "t1"."MTL_TRANSACTIONS_ENABLED_FLAG", "t1"."PRD_CD", "t1"."RECEIVING_ROUTING_ID", "t1"."RECEIVING_ROUTING_NM", "t1"."SHIPPING", "t1"."STOCK_ENABLED_FLAG", "t1"."UOM_CD", "t1"."WEIGHT", "t0"."ATTRIBUTE1", "t0"."ATTRIBUTE2", "t0"."ATTRIBUTE3", "t0"."ATTRIBUTE4", "t0"."ATTRIBUTE5", "t0"."ATTRIBUTE6", "t0"."CREATED_BY", "t0"."CREATED_DATE", "t0"."DIRECTION_FLG", "t0"."DOC_NAME", "t0"."IMPORTED_BY", "t0"."IMPORTED_DATE", "t0"."JOB_TYPE", "t0"."MARKED", "t0"."NEED_TO_DATE", "t0"."NOTE", "t0"."PL_LAST_PRINT_DATE", "t0"."PL_PRINT_COUNT", "t0"."PRIORITY", "t0"."REQUEST_TYPE_ID", "t0"."SOURCE", "t0"."SOURCE_HEADER_ID", "t0"."SOURCE_NAME", "t0"."STATUS", "t0"."UPDATED_BY", "t0"."UPDATED_DATE", "t0"."VALID", "v"."ATTRIBUTE1", "v"."ATTRIBUTE2", "v"."ATTRIBUTE3", "v"."ATTRIBUTE4", "v"."ATTRIBUTE5", "v"."ATTRIBUTE6", "v"."COL_ORDER", "v"."CREATED_BY", "v"."CREATED_DATE", "v"."DESTINATION_PARTY_ID", "v"."PAIR_TRX", "v"."REC_NUM", "v"."REQUEST_DESC", "v"."REQUEST_TYPE_FLOW", "v"."SOURCE_PARTY_ID", "v"."TRX_MATERIAL_TYPE", "v"."UPDATED_BY", "v"."UPDATED_DATE", "v"."VALID", "t3"."ID", "t3"."APP_ID", "t3"."APPL_REV", "t3"."ATTRIBUTE1", "t3"."ATTRIBUTE2", "t3"."ATTRIBUTE3", "t3"."ATTRIBUTE4", "t3"."ATTRIBUTE5", "t3"."ATTRIBUTE6", "t3"."CASE_NO", "t3"."CODE_TEXT", "t3"."CREATED_BY", "t3"."CREATED_DATE", "t3"."DEVICE_IP", "t3"."ERP_INTERFACED", "t3"."FROM_ACCOUNT", "t3"."FROM_ACCOUNT_ID", "t3"."FROM_INVENTORY", "t3"."FROM_LOCATOR", "t3"."FROM_LOCATOR_ID", "t3"."JOB_LINE_ID", "t3"."LABEL_ID", "t3"."MACHINE_ID", "t3"."TO_ACCOUNT", "t3"."TO_ACCOUNT_ID", "t3"."TO_INVENTORY", "t3"."TO_LOCATOR", "t3"."TO_LOCATOR_ID", "t3"."TRX_TYPE_ID", "t3"."UPDATED_BY", "t3"."UPDATED_DATE", "t3"."VALID", "t3"."WINDOWS_USER", "t3"."ID0", "t3"."APP_ID0", "t3"."APPL_REV0", "t3"."ATTRIBUTE10", "t3"."ATTRIBUTE20", "t3"."ATTRIBUTE30", "t3"."ATTRIBUTE40", "t3"."ATTRIBUTE50", "t3"."ATTRIBUTE60", "t3"."CONTENT_ID", "t3"."CREATED_BY0", "t3"."CREATED_DATE0", "t3"."DEVICE_IP0", "t3"."ERP_INTERFACE_ID", "t3"."INVENTORY_ITEM_ID", "t3"."ITEM", "t3"."LOT_NO", "t3"."QUANTITY", "t3"."SOURCE_TRX_ITEM_ID", "t3"."TRX_ID", "t3"."UOM", "t3"."UPDATED_BY0", "t3"."UPDATED_DATE0", "t3"."VALID0", "t3"."VENDOR_LOT_NO", "t3"."WINDOWS_USER0", "t6"."ID", "t6"."APP_ID", "t6"."APPL_REV", "t6"."ATTRIBUTE1", "t6"."ATTRIBUTE2", "t6"."ATTRIBUTE3", "t6"."ATTRIBUTE4", "t6"."ATTRIBUTE5", "t6"."ATTRIBUTE6", "t6"."CASE_NO", "t6"."CODE_TEXT", "t6"."CREATED_BY", "t6"."CREATED_DATE", "t6"."DEVICE_IP", "t6"."ERP_INTERFACED", "t6"."FROM_ACCOUNT", "t6"."FROM_ACCOUNT_ID", "t6"."FROM_INVENTORY", "t6"."FROM_LOCATOR", "t6"."FROM_LOCATOR_ID", "t6"."JOB_LINE_ID", "t6"."LABEL_ID", "t6"."MACHINE_ID", "t6"."TO_ACCOUNT", "t6"."TO_ACCOUNT_ID", "t6"."TO_INVENTORY", "t6"."TO_LOCATOR", "t6"."TO_LOCATOR_ID", "t6"."TRX_TYPE_ID", "t6"."UPDATED_BY", "t6"."UPDATED_DATE", "t6"."VALID", "t6"."WINDOWS_USER", "t6"."ID0", "t6"."APP_ID0", "t6"."APPL_REV0", "t6"."ATTRIBUTE10", "t6"."ATTRIBUTE20", "t6"."ATTRIBUTE30", "t6"."ATTRIBUTE40", "t6"."ATTRIBUTE50", "t6"."ATTRIBUTE60", "t6"."CONTENT_ID", "t6"."CREATED_BY0", "t6"."CREATED_DATE0", "t6"."DEVICE_IP0", "t6"."ERP_INTERFACE_ID", "t6"."INVENTORY_ITEM_ID", "t6"."ITEM", "t6"."LOT_NO", "t6"."QUANTITY", "t6"."SOURCE_TRX_ITEM_ID", "t6"."TRX_ID", "t6"."UOM", "t6"."UPDATED_BY0", "t6"."UPDATED_DATE0", "t6"."VALID0", "t6"."VENDOR_LOT_NO", "t6"."WINDOWS_USER0", "t6"."FULL_NAME", "t6"."ID1", "t6"."ID00"
      FROM "WMS"."T_JOB_LINES" "t"
      LEFT JOIN "WMS"."T_JOB_HEADERS" "t0" ON ("t"."JOB_HEADER_ID" = "t0"."ID")
      LEFT JOIN "WMS"."V_REQUEST_TYPE" "v" ON ("t0"."REQUEST_TYPE_ID" = "v"."ID")
      LEFT JOIN (
          SELECT "t2"."ID", "t2"."APP_ID", "t2"."APPL_REV", "t2"."ATTRIBUTE1", "t2"."ATTRIBUTE2", "t2"."ATTRIBUTE3", "t2"."ATTRIBUTE4", "t2"."ATTRIBUTE5", "t2"."ATTRIBUTE6", "t2"."CONTENT_ID", "t2"."CREATED_BY", "t2"."CREATED_DATE", "t2"."DEVICE_IP", "t2"."INVENTORY_ITEM_ID", "t2"."ITEM", "t2"."JOB_LINE_ID", "t2"."LOT_NO", "t2"."QUANTITY", "t2"."SHIPPING_QUANTITY", "t2"."SOURCE_LINE_ITEM_ID", "t2"."UOM", "t2"."UPDATED_BY", "t2"."UPDATED_DATE", "t2"."VALID", "t2"."VENDOR_LOT_NO", "t2"."WINDOWS_USER", "v0"."ID" "ID0", "v0"."BUILD_IN_WIP_FLAG", "v0"."CLASS", "v0"."DESCRIPTION", "v0"."EXPIRATION_DAYS", "v0"."FIXED_LOT_MULTIPLIER", "v0"."INITIAL_GOKI", "v0"."INVENTORY_ITEM_STATUS_CODE", "v0"."ITEM" "ITEM0", "v0"."ITEM_NO", "v0"."ITM_CREATED_BY", "v0"."ITM_CREATION_DATE", "v0"."ITM_UPDATE_DATE", "v0"."ITM_UPDATED_BY", "v0"."MTL_TRANSACTIONS_ENABLED_FLAG", "v0"."PRD_CD", "v0"."RECEIVING_ROUTING_ID", "v0"."RECEIVING_ROUTING_NM", "v0"."SHIPPING", "v0"."STOCK_ENABLED_FLAG", "v0"."UOM_CD", "v0"."WEIGHT"
          FROM "WMS"."T_JOB_LINES_ITEM" "t2"
          LEFT JOIN "WMS"."V_EBS_ITEM" "v0" ON ("t2"."INVENTORY_ITEM_ID" = "v0"."ID")
      ) "t1" ON ("t"."ID" = "t1"."JOB_LINE_ID")
      LEFT JOIN (
          SELECT "t4"."ID", "t4"."APP_ID", "t4"."APPL_REV", "t4"."ATTRIBUTE1", "t4"."ATTRIBUTE2", "t4"."ATTRIBUTE3", "t4"."ATTRIBUTE4", "t4"."ATTRIBUTE5", "t4"."ATTRIBUTE6", "t4"."CASE_NO", "t4"."CODE_TEXT", "t4"."CREATED_BY", "t4"."CREATED_DATE", "t4"."DEVICE_IP", "t4"."ERP_INTERFACED", "t4"."FROM_ACCOUNT", "t4"."FROM_ACCOUNT_ID", "t4"."FROM_INVENTORY", "t4"."FROM_LOCATOR", "t4"."FROM_LOCATOR_ID", "t4"."JOB_LINE_ID", "t4"."LABEL_ID", "t4"."MACHINE_ID", "t4"."TO_ACCOUNT", "t4"."TO_ACCOUNT_ID", "t4"."TO_INVENTORY", "t4"."TO_LOCATOR", "t4"."TO_LOCATOR_ID", "t4"."TRX_TYPE_ID", "t4"."UPDATED_BY", "t4"."UPDATED_DATE", "t4"."VALID", "t4"."WINDOWS_USER", "t5"."ID" "ID0", "t5"."APP_ID" "APP_ID0", "t5"."APPL_REV" "APPL_REV0", "t5"."ATTRIBUTE1" "ATTRIBUTE10", "t5"."ATTRIBUTE2" "ATTRIBUTE20", "t5"."ATTRIBUTE3" "ATTRIBUTE30", "t5"."ATTRIBUTE4" "ATTRIBUTE40", "t5"."ATTRIBUTE5" "ATTRIBUTE50", "t5"."ATTRIBUTE6" "ATTRIBUTE60", "t5"."CONTENT_ID", "t5"."CREATED_BY" "CREATED_BY0", "t5"."CREATED_DATE" "CREATED_DATE0", "t5"."DEVICE_IP" "DEVICE_IP0", "t5"."ERP_INTERFACE_ID", "t5"."INVENTORY_ITEM_ID", "t5"."ITEM", "t5"."LOT_NO", "t5"."QUANTITY", "t5"."SOURCE_TRX_ITEM_ID", "t5"."TRX_ID", "t5"."UOM", "t5"."UPDATED_BY" "UPDATED_BY0", "t5"."UPDATED_DATE" "UPDATED_DATE0", "t5"."VALID" "VALID0", "t5"."VENDOR_LOT_NO", "t5"."WINDOWS_USER" "WINDOWS_USER0"
          FROM "WMS"."T_TRX" "t4"
          LEFT JOIN "WMS"."T_TRX_ITEM" "t5" ON ("t4"."ID" = "t5"."TRX_ID")
      ) "t3" ON ("t"."ID" = "t3"."JOB_LINE_ID")
      LEFT JOIN (
          SELECT "t7"."ID", "t7"."APP_ID", "t7"."APPL_REV", "t7"."ATTRIBUTE1", "t7"."ATTRIBUTE2", "t7"."ATTRIBUTE3", "t7"."ATTRIBUTE4", "t7"."ATTRIBUTE5", "t7"."ATTRIBUTE6", "t7"."CASE_NO", "t7"."CODE_TEXT", "t7"."CREATED_BY", "t7"."CREATED_DATE", "t7"."DEVICE_IP", "t7"."ERP_INTERFACED", "t7"."FROM_ACCOUNT", "t7"."FROM_ACCOUNT_ID", "t7"."FROM_INVENTORY", "t7"."FROM_LOCATOR", "t7"."FROM_LOCATOR_ID", "t7"."JOB_LINE_ID", "t7"."LABEL_ID", "t7"."MACHINE_ID", "t7"."TO_ACCOUNT", "t7"."TO_ACCOUNT_ID", "t7"."TO_INVENTORY", "t7"."TO_LOCATOR", "t7"."TO_LOCATOR_ID", "t7"."TRX_TYPE_ID", "t7"."UPDATED_BY", "t7"."UPDATED_DATE", "t7"."VALID", "t7"."WINDOWS_USER", "t8"."ID" "ID0", "t8"."APP_ID" "APP_ID0", "t8"."APPL_REV" "APPL_REV0", "t8"."ATTRIBUTE1" "ATTRIBUTE10", "t8"."ATTRIBUTE2" "ATTRIBUTE20", "t8"."ATTRIBUTE3" "ATTRIBUTE30", "t8"."ATTRIBUTE4" "ATTRIBUTE40", "t8"."ATTRIBUTE5" "ATTRIBUTE50", "t8"."ATTRIBUTE6" "ATTRIBUTE60", "t8"."CONTENT_ID", "t8"."CREATED_BY" "CREATED_BY0", "t8"."CREATED_DATE" "CREATED_DATE0", "t8"."DEVICE_IP" "DEVICE_IP0", "t8"."ERP_INTERFACE_ID", "t8"."INVENTORY_ITEM_ID", "t8"."ITEM", "t8"."LOT_NO", "t8"."QUANTITY", "t8"."SOURCE_TRX_ITEM_ID", "t8"."TRX_ID", "t8"."UOM", "t8"."UPDATED_BY" "UPDATED_BY0", "t8"."UPDATED_DATE" "UPDATED_DATE0", "t8"."VALID" "VALID0", "t8"."VENDOR_LOT_NO", "t8"."WINDOWS_USER" "WINDOWS_USER0", "t9"."FULL_NAME", "t9"."ID" "ID1", "t9"."ID0" "ID00"
          FROM "WMS"."T_TRX" "t7"
          LEFT JOIN "WMS"."T_TRX_ITEM" "t8" ON ("t7"."ID" = "t8"."TRX_ID")
          OUTER APPLY (
              SELECT "t11"."FULL_NAME", "t10"."ID", "t11"."ID" "ID0"
              FROM "WMS"."T_PAIR_STATUS" "t10"
              LEFT JOIN "WMS"."T_USERS" "t11" ON ("t10"."USER_ID" = "t11"."ID")
              WHERE (((("t7"."MACHINE_ID" = "t10"."MACHINE_ID")) AND (((((((("t7"."CREATED_DATE" > "t10"."START_DATE")) AND (("t7"."CREATED_DATE" < "t10"."END_DATE"))))) OR ((((("t7"."UPDATED_DATE" > "t10"."START_DATE")) AND (("t7"."UPDATED_DATE" < "t10"."END_DATE"))))))))))
          ) "t9"
      ) "t6" ON ("t"."ID" = "t6"."JOB_LINE_ID")
      WHERE ("t"."CASE_NO" = :caseNo_0)

      ORDER BY "t"."ID", "t0"."ID", "v"."ID", "t1"."ID", "t1"."ID0", "t3"."ID", "t3"."ID0", "t6"."ID", "t6"."ID0", "t6"."ID1"

And that fails on:

OracleExecutionStrategy.ExecuteAsync() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00907: missing right parenthesis
         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, Int64 internalInitialJSONFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
         at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
      --- End of stack trace from previous location ---
         at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Oracle.EntityFrameworkCore.Storage.Internal.OracleRelationalCommandBuilderFactory.OracleRelationalCommandBuilder.OracleRelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)

System info:
Oracle 11.2g
.NET 7
Oracle.EntityFrameworkCore 7.21.13

This is not the first time that this parenthesis bug arrived. I have simulated the same complex query with MSSQL and Postgre and both work. You should add some kind of unit test to test the generated queries especially for the parenthesis.

[tomasfil]

The issues is definetly the select. Same reproduces directly on table only with select without includes:

 Query.Select(s =>
 new ValueSet<decimal, IEnumerable<string>>
 {
     Value1 = s.Id,
     Value2 = s.PairStatuses!.Where(ps =>
         (s.CreatedDate > ps.StartDate && s.CreatedDate < ps.EndDate) || (s.UpdatedDate > ps.StartDate && s.UpdatedDate < ps.EndDate))
     .Select(s => s.User!.FullName)
 })
     .Where(x=> transactionIds.Contains(x.Id))

[alexkeh]

@tomasfil Does this problem occur with DB 19c or higher? Oracle DB 11.2 is no longer supported. ODP.NET no longer tests nor fixes bugs against DB 11.2 except under rare circumstances.

If so, can you provide a complete EF Core test case? We'll try to reproduce the issue in in house and fix it if we identify an ODP.NET code problem.

Of note, MS ends EF Core 7 support next week. Oracle plans to desupport EF Core 7 at the same time.

[tomasfil]

I will try and provide simple test case soon.

[alexkeh]

Providing a test case may be moot if this bug doesn't occur with Oracle DB 19c or higher. The ODP.NET team doesn't plan to implement bug fixes for out of support DB versions.