Incorrect SQL translation
Posted: Thu 24 Sep 2020 13:55
Hi,
We are experiencing a problem with the translation of a linq query to SQL.
We use a query which has a where clause that checks whether a property exists in a list of +1000 items, combined with another (simple) where clause:
By using Entity Framework Profiler, we noticed that, since Oracle doesn't allow for more then 1000 items in an "IN"-clause, the query gets split up like this:
SELECT A, B, C FROM X INNER JOIN Y WHERE (foo in (...) OR foo in (...) AND bar in (...))
This query is not correct and returns 3 cars, including 1 blue car.
The correct translation would be:
SELECT A, B, C FROM X INNER JOIN Y WHERE ( (foo in (...) OR foo in (...)) AND bar in (...))
A repository reproducing the bug can be found here.
We are experiencing a problem with the translation of a linq query to SQL.
We use a query which has a where clause that checks whether a property exists in a list of +1000 items, combined with another (simple) where clause:
Code: Select all
var carsOfSpecificOwnerWithRedColor = await context.Set<Car>()
.Where(_ => owners.Contains(_.Owner))
.Where(_ => _.Color == "red")
.ToArrayAsync();
SELECT A, B, C FROM X INNER JOIN Y WHERE (foo in (...) OR foo in (...) AND bar in (...))
This query is not correct and returns 3 cars, including 1 blue car.
The correct translation would be:
SELECT A, B, C FROM X INNER JOIN Y WHERE ( (foo in (...) OR foo in (...)) AND bar in (...))
A repository reproducing the bug can be found here.