Incorrect SQL translation

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

Incorrect SQL translation

Post by rits-development » 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:

Code: Select all

                    var carsOfSpecificOwnerWithRedColor = await context.Set<Car>()
                        .Where(_ => owners.Contains(_.Owner))
                        .Where(_ => _.Color == "red")
                        .ToArrayAsync();
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.

Shalex
Site Admin
Posts: 9543
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.

laurensb
Posts: 7
Joined: Thu 06 Jun 2019 07:44

Re: Incorrect SQL translation

Post by laurensb » Wed 28 Oct 2020 07:41

Any news on this issue? Were you able to reproduce it?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Incorrect SQL translation

Post by Shalex » Wed 28 Oct 2020 18:41

The bug with incorrect SQL translation of .Contains with a collection of more than 1000 items in EF Core 3 is fixed. We will notify you when the new public build of dotConnect for Oracle is available for download.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Incorrect SQL translation

Post by Shalex » Fri 30 Oct 2020 11:09

New build of dotConnect for Oracle 9.13.1127 is available for download now: viewtopic.php?f=1&t=42365.

Post Reply