EF Core 3 (beta 2) generates ORA-00904: "t"."COLUMNNAME": invalid identifier — oracle-tech

    Forum Stats

  • 3,715,496 Users
  • 2,242,776 Discussions
  • 7,845,366 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

Categories

EF Core 3 (beta 2) generates ORA-00904: "t"."COLUMNNAME": invalid identifier

Bjego
Bjego Member Posts: 13 Red Ribbon
edited September 2020 in ODP.NET

Hey guys,

I just ran into ORA-00904 issues when I run complex queries with the latest Oracle.EntityFrameworkCore (Beta 2 https://www.nuget.org/packages/Oracle.EntityFrameworkCore/3.19.0-beta2 ). Maybe it's worth mentioning that I use Automapper (https://github.com/AutoMapper/AutoMapper ) for business model binding. We have a db schema here with repeating column names in several tables.

The generated SQL looks like this:

SELECT "t9"."T_SB01_KUNDENNR_NUMBER", "t9"."c", "t9"."c0", "t9"."c1", "t9"."c2", "t9"."T_SB14_VORNAME", "t9"."T_SB14_NACHNAME", "t9"."T_SB14_MITARBEITER_ID", "t9"."c3", "t9"."c4", "t9"."T_SB14_VORNAME0", "t9"."T_SB14_NACHNAME0", "t9"."T_SB14_MITARBEITER_ID0", "t9"."c5", "t9"."c6", "t9"."T_SB14_VORNAME1", "t9"."T_SB14_NACHNAME1", "t9"."T_SB14_MITARBEITER_ID1", "t9"."c7", "t9"."GELOESCHT_ZST", "t9"."MARKET_CAP", "t9"."VKDN_INHABER", "t9"."T_SB01_KONTAKT_ID", "t9"."SCHLUESSEL", "t9"."c8", "t9"."KONTAKTTYP_ID", "t12"."c", "t12"."T_SB14_VORNAME", "t12"."T_SB14_NACHNAME", "t12"."T_SB14_MITARBEITER_ID", "t12"."ANZEIGE", "t12"."T_SB39_ACCOUNTMGT_IB_ID", "t12"."BETREUERTYP_ID", "t"."T_SB14_VORNAME", "t"."T_SB14_NACHNAME", "t"."T_SB14_MITARBEITER_ID", "t16"."c", "t16"."T_SB14_VORNAME", "t16"."T_SB14_NACHNAME", "t16"."T_SB14_MITARBEITER_ID", "t16"."ERSTELLT_ZST", "t16"."COLOUR", "t16"."DESCRIPTION", "t16"."IB_CATEGORY_GROUP", "t16"."NAME", "t16"."IB_CATEGORY_ID", "t16"."IB_CATEGORY_TYPE_ID", "t16"."IB_CONTACT2CATEGORY_ID", "t19"."c", "t19"."T_SB14_VORNAME", "t19"."T_SB14_NACHNAME", "t19"."T_SB14_MITARBEITER_ID", "t19"."ANZEIGE", "t19"."T_SB39_ACCOUNTMGT_IB_ID", "t19"."BETREUERTYP_ID", "t21"."IB_STOCKLIST_ID", "t21"."STOCK", "t21"."T_SB01_KONTAKT_ID", "t21"."VKDN_INHABER", "t21"."TICKER", "t21"."IB_CONTACT_STOCKS_ID"FROM (    SELECT "t5"."T_SB01_KUNDENNR_NUMBER", CASE        WHEN (            SELECT "t0"."IB_SECTORTEAM_ID"            FROM (                 (  ( SELECT "i2"."IB_SECTORTEAM_ID", "i2"."BEZEICHNUNG"                FROM "SB"."IB_CONTACT_STOCKS" "i"                INNER JOIN "SB"."IB_STOCKLIST" "i0" ON "i"."IB_STOCKLIST_ID" = "i0"."IB_STOCKLIST_ID"                INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i1" ON "i0"."OBJECT_ID_COMPANY" = "i1"."OBJECTID"                INNER JOIN "SB"."IB_SECTORTEAM" "i2" ON "i1"."IB_SECTORTEAM_ID" = "i2"."IB_SECTORTEAM_ID"                WHERE "t5"."T_SB01_KONTAKT_ID" = "i"."T_SB01_KONTAKT_ID" )                 UNION ALL                 ( SELECT "i7"."IB_SECTORTEAM_ID", "i7"."BEZEICHNUNG"                FROM "SB"."IB_CONTACT_STOCKS" "i3"                INNER JOIN "SB"."IB_STOCKLIST" "i4" ON "i3"."IB_STOCKLIST_ID" = "i4"."IB_STOCKLIST_ID"                INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i5" ON "i4"."OBJECT_ID_COMPANY" = "i5"."OBJECTID"                INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i6" ON "i5"."PARENT_OBJECTID" = "i6"."OBJECTID"                INNER JOIN "SB"."IB_SECTORTEAM" "i7" ON "i6"."IB_SECTORTEAM_ID" = "i7"."IB_SECTORTEAM_ID"                WHERE "t5"."T_SB01_KONTAKT_ID" = "i3"."T_SB01_KONTAKT_ID" )  )                 UNION ALL                 ( SELECT "i13"."IB_SECTORTEAM_ID", "i13"."BEZEICHNUNG"                FROM "SB"."IB_CONTACT_STOCKS" "i8"                INNER JOIN "SB"."IB_STOCKLIST" "i9" ON "i8"."IB_STOCKLIST_ID" = "i9"."IB_STOCKLIST_ID"                INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i10" ON "i9"."OBJECT_ID_COMPANY" = "i10"."OBJECTID"                INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i11" ON "i10"."PARENT_OBJECTID" = "i11"."OBJECTID"                INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i12" ON "i11"."PARENT_OBJECTID" = "i12"."OBJECTID"                INNER JOIN "SB"."IB_SECTORTEAM" "i13" ON "i12"."IB_SECTORTEAM_ID" = "i13"."IB_SECTORTEAM_ID"                WHERE "t5"."T_SB01_KONTAKT_ID" = "i8"."T_SB01_KONTAKT_ID" )             ) "t0"            FETCH FIRST 1 ROWS ONLY) IS NULL THEN 1        ELSE 0    END "c", (        SELECT "t2"."BEZEICHNUNG"        FROM (             (  ( SELECT "i17"."IB_SECTORTEAM_ID", "i17"."BEZEICHNUNG"            FROM "SB"."IB_CONTACT_STOCKS" "i14"            INNER JOIN "SB"."IB_STOCKLIST" "i15" ON "i14"."IB_STOCKLIST_ID" = "i15"."IB_STOCKLIST_ID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i16" ON "i15"."OBJECT_ID_COMPANY" = "i16"."OBJECTID"            INNER JOIN "SB"."IB_SECTORTEAM" "i17" ON "i16"."IB_SECTORTEAM_ID" = "i17"."IB_SECTORTEAM_ID"            WHERE "t5"."T_SB01_KONTAKT_ID" = "i14"."T_SB01_KONTAKT_ID" )             UNION ALL             ( SELECT "i22"."IB_SECTORTEAM_ID", "i22"."BEZEICHNUNG"            FROM "SB"."IB_CONTACT_STOCKS" "i18"            INNER JOIN "SB"."IB_STOCKLIST" "i19" ON "i18"."IB_STOCKLIST_ID" = "i19"."IB_STOCKLIST_ID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i20" ON "i19"."OBJECT_ID_COMPANY" = "i20"."OBJECTID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i21" ON "i20"."PARENT_OBJECTID" = "i21"."OBJECTID"            INNER JOIN "SB"."IB_SECTORTEAM" "i22" ON "i21"."IB_SECTORTEAM_ID" = "i22"."IB_SECTORTEAM_ID"            WHERE "t5"."T_SB01_KONTAKT_ID" = "i18"."T_SB01_KONTAKT_ID" )  )             UNION ALL             ( SELECT "i28"."IB_SECTORTEAM_ID", "i28"."BEZEICHNUNG"            FROM "SB"."IB_CONTACT_STOCKS" "i23"            INNER JOIN "SB"."IB_STOCKLIST" "i24" ON "i23"."IB_STOCKLIST_ID" = "i24"."IB_STOCKLIST_ID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i25" ON "i24"."OBJECT_ID_COMPANY" = "i25"."OBJECTID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i26" ON "i25"."PARENT_OBJECTID" = "i26"."OBJECTID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i27" ON "i26"."PARENT_OBJECTID" = "i27"."OBJECTID"            INNER JOIN "SB"."IB_SECTORTEAM" "i28" ON "i27"."IB_SECTORTEAM_ID" = "i28"."IB_SECTORTEAM_ID"            WHERE "t5"."T_SB01_KONTAKT_ID" = "i23"."T_SB01_KONTAKT_ID" )         ) "t2"        FETCH FIRST 1 ROWS ONLY) "c0", (        SELECT "t4"."IB_SECTORTEAM_ID"        FROM (             (  ( SELECT "i32"."IB_SECTORTEAM_ID", "i32"."BEZEICHNUNG"            FROM "SB"."IB_CONTACT_STOCKS" "i29"            INNER JOIN "SB"."IB_STOCKLIST" "i30" ON "i29"."IB_STOCKLIST_ID" = "i30"."IB_STOCKLIST_ID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i31" ON "i30"."OBJECT_ID_COMPANY" = "i31"."OBJECTID"            INNER JOIN "SB"."IB_SECTORTEAM" "i32" ON "i31"."IB_SECTORTEAM_ID" = "i32"."IB_SECTORTEAM_ID"            WHERE "t5"."T_SB01_KONTAKT_ID" = "i29"."T_SB01_KONTAKT_ID" )             UNION ALL             ( SELECT "i37"."IB_SECTORTEAM_ID", "i37"."BEZEICHNUNG"            FROM "SB"."IB_CONTACT_STOCKS" "i33"            INNER JOIN "SB"."IB_STOCKLIST" "i34" ON "i33"."IB_STOCKLIST_ID" = "i34"."IB_STOCKLIST_ID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i35" ON "i34"."OBJECT_ID_COMPANY" = "i35"."OBJECTID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i36" ON "i35"."PARENT_OBJECTID" = "i36"."OBJECTID"            INNER JOIN "SB"."IB_SECTORTEAM" "i37" ON "i36"."IB_SECTORTEAM_ID" = "i37"."IB_SECTORTEAM_ID"            WHERE "t5"."T_SB01_KONTAKT_ID" = "i33"."T_SB01_KONTAKT_ID" )  )             UNION ALL             ( SELECT "i43"."IB_SECTORTEAM_ID", "i43"."BEZEICHNUNG"            FROM "SB"."IB_CONTACT_STOCKS" "i38"            INNER JOIN "SB"."IB_STOCKLIST" "i39" ON "i38"."IB_STOCKLIST_ID" = "i39"."IB_STOCKLIST_ID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i40" ON "i39"."OBJECT_ID_COMPANY" = "i40"."OBJECTID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i41" ON "i40"."PARENT_OBJECTID" = "i41"."OBJECTID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i42" ON "i41"."PARENT_OBJECTID" = "i42"."OBJECTID"            INNER JOIN "SB"."IB_SECTORTEAM" "i43" ON "i42"."IB_SECTORTEAM_ID" = "i43"."IB_SECTORTEAM_ID"            WHERE "t5"."T_SB01_KONTAKT_ID" = "i38"."T_SB01_KONTAKT_ID" )         ) "t4"        FETCH FIRST 1 ROWS ONLY) "c1", CASE        WHEN "t6"."T_SB14_MITARBEITER_ID" IS NULL THEN 1        ELSE 0    END "c2", "t6"."T_SB14_VORNAME", "t6"."T_SB14_NACHNAME", "t6"."T_SB14_MITARBEITER_ID", COALESCE("t5"."ERSTELLT_ZST", TO_DATE('0001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) "c3", CASE        WHEN "t7"."T_SB14_MITARBEITER_ID" IS NULL THEN 1        ELSE 0    END "c4", "t7"."T_SB14_VORNAME" "T_SB14_VORNAME0", "t7"."T_SB14_NACHNAME" "T_SB14_NACHNAME0", "t7"."T_SB14_MITARBEITER_ID" "T_SB14_MITARBEITER_ID0", CASE        WHEN (("t5"."T_SB01_IB_FSA_APPROVAL" = 'J') AND ("t5"."T_SB01_IB_FSA_APPROVAL" IS NOT NULL)) THEN 1        ELSE 0    END "c5", CASE        WHEN "t8"."T_SB14_MITARBEITER_ID" IS NULL THEN 1        ELSE 0    END "c6", "t8"."T_SB14_VORNAME" "T_SB14_VORNAME1", "t8"."T_SB14_NACHNAME" "T_SB14_NACHNAME1", "t8"."T_SB14_MITARBEITER_ID" "T_SB14_MITARBEITER_ID1", COALESCE(COALESCE("t5"."GEAENDERT_ZST", "t5"."ERSTELLT_ZST"), TO_DATE('0001-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) "c7", "t5"."GELOESCHT_ZST", "i44"."MARKET_CAP", "t5"."VKDN_INHABER", "t5"."T_SB01_KONTAKT_ID", "k"."SCHLUESSEL", "t5"."KONTAKTTYP_ID" "c8", "k"."KONTAKTTYP_ID"    FROM "SB"."T_SB01_KONTAKT" "t5"    LEFT JOIN "SB"."T_SB14_MITARBEITER" "t6" ON "t5"."ERSTELLT_ID" = "t6"."T_SB14_MITARBEITER_ID"    LEFT JOIN "SB"."IB_KONTAKT" "i44" ON "t5"."T_SB01_KONTAKT_ID" = "i44"."T_SB01_KONTAKT_ID"    LEFT JOIN "SB"."T_SB14_MITARBEITER" "t7" ON "i44"."CREATED_FOR_MITARBEITER" = "t7"."T_SB14_MITARBEITER_ID"    LEFT JOIN "SB"."T_SB14_MITARBEITER" "t8" ON "t5"."GEAENDERT_ID" = "t8"."T_SB14_MITARBEITER_ID"    INNER JOIN "SB"."KONTAKTTYP" "k" ON "t5"."KONTAKTTYP_ID" = "k"."KONTAKTTYP_ID"    WHERE "t5"."T_SB01_KONTAKT_ID" = :p_0    FETCH FIRST 2 ROWS ONLY    ) "t9"    LEFT JOIN (        SELECT 0 "c", "t11"."T_SB14_VORNAME", "t11"."T_SB14_NACHNAME", "t11"."T_SB14_MITARBEITER_ID", "b"."ANZEIGE", "t10"."T_SB39_ACCOUNTMGT_IB_ID", "b"."BETREUERTYP_ID", "t10"."T_SB39_SB01_KONTAKT_ID"        FROM "SB"."T_SB39_ACCOUNTMGT_IB" "t10"        INNER JOIN "SB"."BETREUERTYP" "b" ON "t10"."T_SB39_SB41_BETREUER_TYP_ID" = "b"."BETREUERTYP_ID"        INNER JOIN "SB"."T_SB14_MITARBEITER" "t11" ON "t10"."T_SB39_SB14_MITARBEITER_ID" = "t11"."T_SB14_MITARBEITER_ID"        WHERE "b"."BETREUERTYPE_PRIORITY_ID" = 1        ) "t12" ON "t9"."T_SB01_KONTAKT_ID" = "t12"."T_SB39_SB01_KONTAKT_ID"        OUTER APPLY (            SELECT DISTINCT "t13"."T_SB14_MITARBEITER_ID", "t13"."T_SB14_NACHNAME", "t13"."T_SB14_VORNAME"            FROM "SB"."IB_CONTACT_STOCKS" "i45"            INNER JOIN "SB"."IB_STOCKLIST" "i46" ON "i45"."IB_STOCKLIST_ID" = "i46"."IB_STOCKLIST_ID"            INNER JOIN "SB"."IB_ERD_SECTOR_COMPANY_STOCK" "i47" ON "i46"."OBJECT_ID_COMPANY" = "i47"."OBJECTID"            INNER JOIN "SB"."T_SB14_MITARBEITER" "t13" ON "i47"."MAIN_ANALYST_ID" = "t13"."T_SB14_MITARBEITER_ID"            WHERE "t9"."T_SB01_KONTAKT_ID" = "i45"."T_SB01_KONTAKT_ID"            ) "t14"            LEFT JOIN (                SELECT 0 "c", "t15"."T_SB14_VORNAME", "t15"."T_SB14_NACHNAME", "t15"."T_SB14_MITARBEITER_ID", "i48"."ERSTELLT_ZST", "i49"."COLOUR", "i49"."DESCRIPTION", "i49"."IB_CATEGORY_GROUP", "i49"."NAME", "i49"."IB_CATEGORY_ID", "i49"."IB_CATEGORY_TYPE_ID", "i48"."IB_CONTACT2CATEGORY_ID", "i48"."CONTACT_ID"                FROM "SB"."IB_CONTACT2CATEGORY" "i48"                INNER JOIN "SB"."T_SB14_MITARBEITER" "t15" ON "i48"."ERSTELLT_ID" = "t15"."T_SB14_MITARBEITER_ID"                INNER JOIN "SB"."IB_CATEGORY" "i49" ON "i48"."IB_CATEGORY_ID" = "i49"."IB_CATEGORY_ID"                ) "t16" ON "t9"."T_SB01_KONTAKT_ID" = "t16"."CONTACT_ID"                LEFT JOIN (                    SELECT 0 "c", "t18"."T_SB14_VORNAME", "t18"."T_SB14_NACHNAME", "t18"."T_SB14_MITARBEITER_ID", "b0"."ANZEIGE", "t17"."T_SB39_ACCOUNTMGT_IB_ID", "b0"."BETREUERTYP_ID", "t17"."T_SB39_SB01_KONTAKT_ID"                    FROM "SB"."T_SB39_ACCOUNTMGT_IB" "t17"                    INNER JOIN "SB"."BETREUERTYP" "b0" ON "t17"."T_SB39_SB41_BETREUER_TYP_ID" = "b0"."BETREUERTYP_ID"                    INNER JOIN "SB"."T_SB14_MITARBEITER" "t18" ON "t17"."T_SB39_SB14_MITARBEITER_ID" = "t18"."T_SB14_MITARBEITER_ID"                    WHERE "b0"."BETREUERTYPE_PRIORITY_ID" <> 1                    ) "t19" ON "t9"."T_SB01_KONTAKT_ID" = "t19"."T_SB39_SB01_KONTAKT_ID"                    LEFT JOIN (                        SELECT "i51"."IB_STOCKLIST_ID", "i51"."STOCK", "t20"."T_SB01_KONTAKT_ID", "t20"."VKDN_INHABER", "i51"."TICKER", "i50"."IB_CONTACT_STOCKS_ID", "i50"."T_SB01_KONTAKT_ID" "T_SB01_KONTAKT_ID0"                        FROM "SB"."IB_CONTACT_STOCKS" "i50"                        INNER JOIN "SB"."IB_STOCKLIST" "i51" ON "i50"."IB_STOCKLIST_ID" = "i51"."IB_STOCKLIST_ID"                        INNER JOIN "SB"."T_SB01_KONTAKT" "t20" ON "i50"."T_SB01_KONTAKT_ID" = "t20"."T_SB01_KONTAKT_ID"                        ) "t21" ON "t9"."T_SB01_KONTAKT_ID" = "t21"."T_SB01_KONTAKT_ID0"                        ORDER BY "t9"."T_SB01_KONTAKT_ID", "t9"."KONTAKTTYP_ID", "t12"."T_SB39_ACCOUNTMGT_IB_ID", "t12"."BETREUERTYP_ID", "t12"."T_SB14_MITARBEITER_ID", "t"."T_SB14_MITARBEITER_ID", "t16"."IB_CONTACT2CATEGORY_ID", "t16"."T_SB14_MITARBEITER_ID", "t16"."IB_CATEGORY_ID", "t19"."T_SB39_ACCOUNTMGT_IB_ID", "t19"."BETREUERTYP_ID", "t19"."T_SB14_MITARBEITER_ID", "t21"."IB_CONTACT_STOCKS_ID", "t21"."IB_STOCKLIST_ID", "t21"."T_SB01_KONTAKT_ID"

Here is my Splunk Log: (JSON)

{

"Host": "HAM-HAU-12500D",

"App": "OrganisationService",

"Exception": {

"Type": "Oracle.ManagedDataAccess.Client.OracleException",

"OracleLogicalTransaction": {},

"Errors": [

      {

"DataSource": "",

"Message": "ORA-00904: \"t\".\"T_SB14_MITARBEITER_ID\": ungültige ID\n",

"Number": 904,

"ParseErrorOffset": 12832,

"Procedure": "",

"Source": "Oracle Data Provider for .NET, Managed Driver",

"ArrayBindIndex": -1

      }

    ],

"DataSource": "",

"Message": "ORA-00904: \"t\".\"T_SB14_MITARBEITER_ID\": ungültige ID",

"Procedure": "",

"Source": "Oracle Data Provider for .NET, Managed Driver",

"Number": 904,

"IsRecoverable": false,

"ErrorCode": -2147467259,

"TargetSite": "Int32 VerifyExecution(Int32 ByRef, Boolean, OracleInternal.Common.SqlStatementType, Int32, Oracle.ManagedDataAccess.Client.OracleException ByRef, Boolean ByRef, Boolean)",

"StackTrace": "   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.ExecuteDbDataReader(CommandBehavior behavior)   at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)--- End of stack trace from previous location where exception was thrown ---   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.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)   at Oracle.EntityFrameworkCore.Storage.Internal.OracleExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)   at OrganisationService.OrganisationReader.GetAsync(Int32 id, CancellationToken token) in C:\\Git\\OrganisationService\\OrganisationService\\OrganisationReader.cs:line 58   at OrganisationService.v2.Controllers.OrganisationsController.Get(Int32 id, CancellationToken token) in C:\\Git\\OrganisationService\\OrganisationService\\V2\\Controllers\\OrganisationsController.cs:line 75   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()--- End of stack trace from previous location where exception was thrown ---   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ExceptionContextSealed context)   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()--- End of stack trace from previous location where exception was thrown ---   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)",

"Data": {},

"HResult": -2147467259

  },

"Message": "An unhandled exception has occurred while executing the request.",

"Thread": "5",

"TraceIdentifier": "|64c20ee5-432c7d9a3bbe44bf.",

"Logger": "Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware",

"Environment": "Development",

"Level": "Error",

"Date": "2020-07-27T09:18:53.3234831+02:00",

"Controller": "Organisations",

"Action": "Get",

"Client Ip": "::1",

"aspnet-request-url": "http:\/\/localhost\/api\/Organisations\/690944",

"aspnet-request-method": "GET",

"aspnet-request-referrer": "http:\/\/localhost:5000\/swagger\/index.html?urls.primaryName=V2",

"aspnet-request-host": "localhost:5000",

"aspnet-request-useragent": "Mozilla\/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/84.0.4147.89 Safari\/537.36 Edg\/84.0.522.44",

"aspnet-request-querystring ": "api-version=2"

}

· Share on TwitterShare on Facebook

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,751 Employee
    edited July 2020

    Are you using an 11.2 DB? If so, this could be bug 29336890 as described in the beta 2 README.

    · Share on TwitterShare on Facebook
  • 4092341
    4092341 Member Posts: 2
    edited September 2020

    Hi Alex, Bjego,

    We reproduce the same problem with EF 6.4.4, Oracle.ManagedAccess 19.8.0 and Oracle.ManagedDataAccess.EntityFramework 19.7.0 on .Net 4.7.2 with Oracle 19 C database.

    Error :

    ORA-00904: "Extent5"."FTR_ID_SOURCE_FTR_ID" : identificateur non valide

    The Linq Request :

    var fatcaPerson = context.FatcaPersons

                                                  .Where(fp => fp.PER_ID == userInfo.PerId)

                                                  .Include("FATCA_PERSON_HISTORY_LIST_BY_PER_ID.FATCA_PERSON_TRIGGER_LIST_BY_FPH_ID")

                                                  .Include("FATCA_PERSON_HISTORY_LIST_BY_PER_ID.FATCA_PERSON_BLOB_OBJECT_LIST_BY_FPH_ID")

                                                  .SingleOrDefault() ?? new FatcaPerson();

    remark : FTR_ID_SOURCE is a Navigation property of FACA_PERSON_TRIGGER, it was'nt call in the linq request.

    The (simplified) Linq request generated :

    SELECT

    "Project4"."PER_ID" AS "PER_ID",

    ........

    FROM ( SELECT

        "Limit1"."PER_ID" AS "PER_ID",

       ...

        "UnionAll1"."C1" AS "C1",

        "UnionAll1"."PER_ID" AS "C4",

       ....

       "UnionAll1"."FTR_ID_SOURCE_FTR_ID" AS "C23",

        CASE WHEN ("UnionAll1"."FPH_ID" IS NULL) THEN NULL ELSE 1 END AS "C35"

        FROM   (SELECT

            "Extent1"."PER_ID" AS "PER_ID",

            ....

            FROM   "T_FATCA_PERSON" "Extent1"

            WHERE ("Extent1"."PER_ID" = :p__linq__0) AND (ROWNUM <= (2) ) ) "Limit1"

        OUTER APPLY  (SELECT

            CASE WHEN ("Extent5"."FPH_ID" IS NULL) THEN NULL ELSE 1 END AS "C1",

            "Extent4"."FPH_ID" AS "FPH_ID",

            ...

            "Extent5"."FPH_ID" AS "FPH_ID2",

           ...

           "Extent5"."FTR_ID_SOURCE_FTR_ID" AS "FTR_ID_SOURCE_FTR_ID",

            FROM T_FATCA_PERSON_HISTORY" "Extent4"

            LEFT OUTER JOIN "T_FATCA_PERSON_TRIGGER" "Extent5" ON "Extent4"."FPH_ID" = "Extent5"."FPH_ID"

        UNION ALL

            SELECT

            2 AS "C1",

            "Extent6"."FPH_ID" AS "FPH_ID",

            "Extent6"."FPH_ID" AS "FPH_ID1",

            ....

            "Extent7"."FPH_ID" AS "FPH_ID2",

           ....

    )  "Project4"

    ORDER BY "Project4"."PER_ID" ASC,..... 

    the FATCA_PERSON_HISTORY  entity :

      [Table("T_FATCA_PERSON_HISTORY")]

        public partial class FatcaPersonHistory

        {

            #region Constructor

            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]

            public FatcaPersonHistory()

            {

                FATCA_PERSON_TRIGGER_LIST_BY_FPH_ID = new List<FatcaPersonTrigger>();

            }

            #endregion Constructor

          

               [Key]

             [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

            [DataMember]

            public int FPH_ID { get; set; }

            #region Navigation Properties

       

            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]

            public virtual ICollection<FatcaPersonTrigger> FATCA_PERSON_TRIGGER_LIST_BY_FPH_ID { get; set; }

            #endregion Navigation Properties

        }

    The FATCA_PERSON_TRIGGER Entity :

    [Table("T_FATCA_PERSON_TRIGGER")]

    public partial class FatcaPersonTrigger

        {

            #region Properties

              [Key]

              [Column(Order = 0)]

             [DatabaseGenerated(DatabaseGeneratedOption.None)]

            [DataMember]

            public int FPH_ID { get; set; }

              [Key]

              [Column(Order = 1)]

            [DatabaseGenerated(DatabaseGeneratedOption.None)]

            [DataMember]

            public byte FTR_ID { get; set; }

         

            #endregion Properties

            #region Navigation Properties

            public virtual FatcaPersonHistory FPH_ID_SOURCE { get; set; }

            public virtual FatcaTrigger FTR_ID_SOURCE { get; set; }

            #endregion Navigation Properties

        }

        [Table("T_FATCA_TRIGGER")]

        public partial class FatcaTrigger

        {

            #region Constructor

            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]

            public FatcaTrigger()

            {

                FATCA_PERSON_TRIGGER_LIST_BY_FTR_ID = new List<FatcaPersonTrigger>();

            }

            #endregion Constructor

            #region Properties

              [Key]

            [DatabaseGenerated(DatabaseGeneratedOption.None)]

            [DataMember]

            public short FTR_ID { get; set; }

            #endregion Properties

            #region Navigation Properties

            [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]

            public virtual ICollection<FatcaPersonTrigger> FATCA_PERSON_TRIGGER_LIST_BY_FTR_ID { get; set; }

            #endregion Navigation Properties

        }

    Remark Same request with same entites works fine on EF 6.2 ManagedDataAccess 18.3.0 and  ManagedDataacess.EntityFramework 18.3.0 .NET 4.6.2 on 19C database.

    Regards,

    Sylvain

    · Share on TwitterShare on Facebook
  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,751 Employee
    edited September 2020

    @Sylvain. Your error source is different from the first poster's. You are using EF6 while the other poster is using EF Core.

    For your error, do you have the complete test case to run? That would make it easier for us to reproduce the problem and identify the root cause.

    · Share on TwitterShare on Facebook
Sign In or Register to comment.