1 2 Previous Next 17 Replies Latest reply on Jun 25, 2020 9:56 PM by Alex Keh - Product Manager-Oracle

    "Oracle.EntityFrameworkCore" Version="2.19.60" has issues when (Csharp) Poco Property Name does not match the db COLUMN_NAME

    4238079

      Hello.

       

      I have discovered a couple of issues with the current Oracle Entity Framework driver.

      After some heaving debugging, I am calling this the "column alias bug".

       

       

      I have created a complete runnable demo here:

       

      https://github.com/granadacoder/oracle-ef-issues-demo

       

      All you have to do is pull the code and change the connection string.  I left breadcrumbs in the README file.

       

      At a high level, the issue revolves around mapping a POCO PascalCasePropertyName to an Oracle COLUMN_NAME.

       

      builder.Property(cn => cn.PropertyName).HasColumnName("PROPERTY_NAME");

       

       

      There is one actual bug. 

       

      Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "t"."MY_PARENT_UUID": invalid identifier

       

       

      There are also performance issues because the code does not resolve on the server side, and pushes the evaluation to the client.

       

      [WRN] The LINQ expression 'where ([chd].MyParentUuidFk == [par].MyParentKey)' could not be translated and will be evaluated locally.

       

       

      One of the "evaluation to the client" revolves around an anonymous class.  Another one, another instance of the PropertyName not matching the PROPERTY_NAME. (or rather the COLUMN_NAME).

       

      The queries are not super-simple, being composed of multiple IQueryable(s).  However, I have tested the same code against MS-SQL-Server, PostGres and MySql (    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.1.*" /> ).  All of these are working correctly.

       

      Only the Oracle.EF driver has the issues. ( <PackageReference Include="Oracle.EntityFrameworkCore" Version="2.19.60" /> )

       

       

      Because of the delays with Oracle.EntityFrameworkCore 3.1.X driver.......... <PackageReference Include="Oracle.EntityFrameworkCore" Version="3.1.x" />, there is no upgrade path.

       

      Can you please look into these issues.  Again, they are completely reproducible, with the GitHub link provided.

       

      And please add these cases to the regression tests of your 3.1.x code base.

       

      Thank you.

       

       

       

      https://github.com/granadacoder/oracle-ef-issues-demo

       

       

       

      Here is the bug one:

       

      SELECT "par"."MY_PARENT_UUID", "par"."MY_PARENT_NAME", "par"."UPDATE_DATE_TS"

      FROM "MYORACLESCHEMAONE"."MY_PARENT" "par"

      WHERE EXISTS (

          SELECT 1

          FROM "MYORACLESCHEMAONE"."MY_CHILD" "chd"

          INNER JOIN (

              SELECT "i"."MY_PARENT_UUID" "MyParentUuidFk", MAX("i"."UPDATE_DATE_TS") "UpdateDateStamp"

              FROM "MYORACLESCHEMAONE"."MY_CHILD" "i"

              GROUP BY "i"."MY_PARENT_UUID"

          ) "t" ON ("chd"."UPDATE_DATE_TS" = "t"."UPDATE_DATE_TS") AND ("chd"."MY_PARENT_UUID" = "t"."MY_PARENT_UUID")

          WHERE ("chd"."MY_CHILD_MAGIC_STATUS" NOT IN (98, 99) AND ("t"."UPDATE_DATE_TS" < :cutOffDate_1)) AND ("chd"."MY_PARENT_UUID" = "par"."MY_PARENT_UUID")) OR NOT EXISTS (

          SELECT 1

          FROM "MYORACLESCHEMAONE"."MY_CHILD" "m"

          WHERE "par"."MY_PARENT_UUID" = "m"."MY_PARENT_UUID")

       

       

      The sub-query "t" is PRODUCING the alias (which is the Poco.PropertyName , in this case "UpdateDateStamp".

       

      However, the JOIN-ON is not using aliasname...., aka, it is using the actual oracle COLUMN_NAME. ("UPDATE_DATE_TS" in this case).  The 2 do not match, and the run time error is produced.

       

      I am guessing there are similar issues that result in the "could not be translated and will be evaluated locally" issues/warnings.

       

      It really isn't PascalCase vs THIS_CASE.  It is just about the values not being the same.

        1 2 Previous Next