Incorrect SQL translation

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Posts: 6
Joined: Tue 03 Mar 2020 13:15

Incorrect SQL translation

Post by rits-development » Thu 24 Sep 2020 13:55

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")
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.

Site Admin
Posts: 9077
Joined: Thu 14 Aug 2008 12:44

Re: Incorrect SQL translation

Post by Shalex » Fri 02 Oct 2020 10:35

Thank you for your report and test project. We will investigate the issue and notify you about the result.

Post Reply