Devart EFCore 3.1 for Oracle provider generates ORA-00904 “Invalid identifier”
Posted: Fri 24 Apr 2020 12:47
We are executing this LINQ to entities query:
The AllCars() method returns IQueryable<Car> and contains this LINQ query:
Which generates this SQL:
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.
Code: Select all
from car in AllCars()
where car.ID == 1
select car 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
}
} 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 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.