Left Joins just works with include

[michaelfleischergasruss]

Hi Oracle Github-Team,

  I work with a Oracle Database V18, NET6 (6.0.8), EF-Core (and the official ORACLE EF-CORE PROVIDER, newest).

  Left Joins never works if .DefaultIsEmpty() is used (no where, for no table).

It always creates an inner join with a null value and a left join which joins on that, will not work, never.

  No... That's really not a Left JOIN.

INNER JOIN (SELECT "a0"."id", "a0"."address", "a0"."address_id", "a0"."barcode", "a0"."city", "a0"."contactor", "a0"."country", "a0"."department", "a0"."email", "a0"."fax_no", "a0"."name_1", "a0"."name_2", "a0"."phone_no", "a0"."post_office_box", "a0"."role", "a0"."street", "a0"."title", "a0"."zip_code", "a0"."zip_code_pob" FROM (SELECT NULL "empty" FROM dual) "e2" LEFT JOIN "SCHEMA"."address_roles" "a0" ON 1 = 1) "t2" ON "t0"."receiver_address_role_id" = "t2"."address_id"

The whole database -schema is scaffolded, I even tried to play with the fluent API to work around this bug.

" If the table already exists, use scaffolding to generate the entity classes corresponding to the relational database tables. Doing so generates the correct fluent API for each table column."- good joke, by Oracle Performance Considerations (oracle.com)

Nothing worked until ->

 

using System.Linq;

using Microsoft.EntityFrameworkCore;

And started using .Include() instead of .Join() .  

This is a faulty behavior and not represents the way EntityFramework-Developer are used to deal with a situation like that, it was really hard to find this solution myself because every common Encyclopedia for Developers or Help-Forums will guide to a SelectMany (absolute unreadable) or a .DefaultIfEmpty() - Solution.

  Include is fine!! But if people should use that, this should be written in the oracle documentation, usually EFCore Developer use stuff like .DefaultIfEmpty() for that. https://docs.oracle.com/en/database/oracle/oracle-data-access-components/19.3/odpnt/ODPEFCore.html about EntityFramework as common way to do Left-Joins (Like, "Never use .DefaultIfEmpty() we don't support that but .Include is fine for Outer-Joins /Left-Joins).

Anyway, Update your Documentation or pls make your EF-Provider more compatible to the way EF-Core works, if it's not compatible then there is no use for using EF-Core with Oracle.

EF-Core is the Microsoft way to write Apis which work with every Database, if that not works with Oracle because Oracle says ".DefaultIfEmpty() is not supported by us" then it should be at least written down somewhere public accessible.

Using .Include (with help of the good Microsoft namespace for it...)

-it always creates a perfect LEFT-Join, while the Oracle Provider seems to be not being capable of getting a simple .Join( oracleContext.TableWhatever.DefaultIfEmpty()   (placing it anywhere else where you possibly could place it also not works).

[alexkeh]

If you can share a sample EF Core test project that demonstrates this behavior, the Oracle .NET team will review it to understand the root cause of the problem.

[alexkeh]

Pausing this issue for now. I'll re-open when a test case is provided so that a root cause can be identified.