Devart EFCore 3.1 for Oracle provider generates ORA-00904 “Invalid identifier”

[mbouwhui]

We are executing this LINQ to entities query:

Code: Select all

from car in AllCars() 
where car.ID == 1 
select car 

The AllCars() method returns IQueryable<Car> and contains this LINQ query:

Code: Select all

from car in context.Cars 
join model in context.Models on car.ID equals model.CAR_ID 
select new Car  
{ 
   Brand = car.Name, 
   Model = model.Description, 
   OtherModels = from otherModel in context.Models 
                 where otherModel.CAR_ID == car.ID && otherModel.ID != model.ID 
                 select new CarModel  
                 { 
                     Model = otherModel.Name 
                 }
} 

Which generates this SQL:

Code: Select all

SELECT "c".CAR_ID, "c".NAME, "c0".Description, "t".NAME 
FROM Cars "c" 
INNER JOIN MODELS "c0" ON "c".ID = "c0".CAR_ID 
LEFT JOIN ( 
    SELECT "c3".NAME 
    FROM MODELS "c3" 
    WHERE "c3".ID <> "c0".ID 
) "t" ON "c3".CAR_ID = "c".ID 
WHERE “c”.ID == 1 

Causing this SQL error: ORA-00904: "c0".ID: Invalid identifier.
The reason is that Oracle does not allow to reference tables outside of an inline view definition.
From Oracle 12c onwards the LATERAL keyword can be used.

Note that there is no error when we call AllCars() directly (then the LEFT JOIN is replaced with OUTER APPLY that allows referring to tables outside the inline definition).

We are using EF Core 3.1.3 with Devart as provider (Devart.Data.Oracle.EFCore version 9.10.909)
We cannot go to the latest version (9.11.980) due to other bugs in this EFCore provider, but this problem also occurs in the latest version.

[Shalex]

Please send us a small test project with the corresponding DDL/DML script so that we can reproduce the issue in our environment.

[mbouwhui]

Thanks for the reply.
I just e-mailed a small testproject to you that reproduces the error.
Hope to hear from you soon!

[Shalex]

Thank you for the test project. We have reproduced the issue and are investigating it. We will notify you about the result.