Note that the branch is currently closed.

Fix #32325 has resulted in regressions for Oracle EF Provider customers.

Using the replication code given in bug,
SQL EF Provider 8.0.0 generates the following query:

SELECT COUNT(*) FROM [Persons] AS [p] WHERE COALESCE([p].[ThreeCharacterProperty], '') + ';' + COALESCE([p].[FiveCharacterProperty], '') IN ( SELECT [q].[value] FROM OPENJSON(@__queryData_0) WITH ([value] char(3) '$') AS [q] )

which requires correction in OPENJSON subquery generation.

After the fix is delivered in 8.0.2, the same test case generates the following query:

SELECT COUNT(*) FROM [Persons] AS [p] WHERE COALESCE([p].[ThreeCharacterProperty], N'') + N';' + COALESCE([p].[FiveCharacterProperty], N'') IN (N'AAA;BBBBB', N'AAA;CCCCC')

which got updated with the standard IN condition and skipped the creation of the OPENJSON subquery.

This fix was not anticipated in the relational layer because the OPENJSON function is SQL Server provider-specific. As a result, the Oracle provider, which uses a standard IN condition in such cases and is dependent on inferring the right type and size by base class, experienced a regression.

@ajcvickers @roji
Why was the fix (inferring the size and type of the SQL binary expression) decided to be included in the relational layer rather than in the SQL Server provider by overriding the SqlExpressionFactory.ApplyTypeMapping() method?

@indrajitjadeja the fix in #32510 only corrected the length on the type mapping; this is a general type mapping inference fix that isn't SQL Server-specific (even if the original bug manifested specifically in SQL Server). I'm still not clear on how it exactly it affects the Oracle SQL translation - can you please open a new issue with a clear minimal, runnable repro and the SQL generated in 8.0.1 and 8.0.2?