OUTER APPLY query is being generated for Oracle 11g

Jochen Jonckheere

    I'm converting/testing the migration of a project that is using EF Core 2.1 to EF Core 3.1 and thus far all queries worked as expected, except one.

     

    I had one query that suddenly uses an OUTER APPLY, but that is not supported in Oracle 11g. I think it should have generated a LEFT JOIN LATERAL in case the database version is 11.

    At the moment it is a little bit hard to extract a simple example out of my code, but maybe you already have enough information. But if you really need it I can try to create a simple sample.

     

    I'm using Oracle.EntityFrameworkCore 3.1.19.0 and the UseOracleSQLCompatibility has value "11".

      • 1. Re: OUTER APPLY query is being generated for Oracle 11g
        Alex Keh - Product Manager-Oracle

        This may be an existing known issue described in the beta README. Here's the short summary:

         

        "The issue is because the SELECT query that is generated uses ‘OUTER APPLY’. There is an issue in Oracle Database which causes it to return incorrect data when using ‘OUTER APPLY’. [Bug 31053669, Bug 30947031]"

         

        The README also has a sample model, code, and SQL showing how the bug occurs. Check whether the description matches your own situation.

        • 2. Re: OUTER APPLY query is being generated for Oracle 11g
          Jochen Jonckheere

          Hi Alex,

           

          The issue I encountered was not exactly the same as described in the README. My query resulted in a ORA-00933 error, not in returning bad results.

          Because as far as I know an OUTER APPLY is not supported at all in Oracle 11g.

           

          Regards,

           

          Jochen

          • 3. Re: OUTER APPLY query is being generated for Oracle 11g
            Alex Keh - Product Manager-Oracle

            Fundamentally, the bug is the generation of the keywords OUTER APPLY as they are not supported with Oracle DB 11g SQL syntax. This can result in various error codes. The "fix" is to generate equivalent SQL that does not use these keywords. This is sometimes possible for Oracle to do within its provider and sometimes it's not. Changing the LINQ itself so that it generates SQL that returns equivalent results without using OUTER APPLY sometimes works. Certainly, the latter is something customers can try.

             

            This was originally a problem with LINQ in EF prior to EF Core. Oracle asked Microsoft were there any patterns we could implement to avoid generating OUTER APPLY. After some investigation, MS concluded there were some ways to avoid creating SQL with those keywords from a provider and LINQ standpoint, but no guarantee.