EF Core: Wrong SQL generated for negated InExpression for large Enumeration

[arne-imhof-oasis]

Given int[] manyValues = Enumerable.Range(0, 1001).ToArray(); , context.Foo.Where(f => !manyValues.Contains(f.Bar))… wrongly generates …WHERE "Bar" NOT IN (…1000 values…) OR "Bar" NOT IN (…1 value…) while context.Foo.Where(f => manyValues.Contains(f.Bar))… correctly generates …WHERE "Bar" IN (…1000 values…) OR "Bar" IN (…1 value…) The current implementation wrongly always chooses 'OR' even for the negated case, but should simply choose 'AND' in this case.

As a local workaround, create a custom QuerySqlGenerator + Factory and fixup VisitIn. Use EF Core's ReplaceService to patch it in.

[alexkeh]

Which EF Core version, Oracle EF Core version, and DB version are you using?

Do you have a simple, complete test case we can use to reproduce the problem?

[arne-imhof-oasis]

Check ouf assembly Oracle.EntityFrameworkCore in version

• 6.0.21.1
• 7.0.21.1 If your developers take a look (as I did with a tool such as IL Spy) to track down this problem you will find your class Oracle.EntityFrameworkCore.Query.Sql.Internal.OracleQuerySqlGenerator to have a method GeneratePredicateList responsible for ensuring only 1000 Elements per IN/NOT IN, joined with OR

The (admittedly sketchy) notes in my initial issue should be enough to get you going.

[alexkeh]

I created a bug (35047863) to track the issue and have the Oracle EF Core dev team review.

[alexkeh]

This bug has been fixed. I expect it will be available in the Oracle EF Core 21.12 patch.

[alexkeh]

The bug fix was able to get merged into 21.11. So, it will be delivered sooner.