Entity Framework Core 8 Where IN vs Where IN OPENJSON

Question

EF Core 8 now inlines values in where statements using a combination of WHERE IN and OPENJSON as opposed to the previous WHERE IN(...).

This change is noted in the documentation and the stated reason is as follows:

The inlining of values here is done in such a way that there is no chance of a SQL injection attack. The change to use JSON described below is all about performance, and nothing to do with security.

Unfortunately, OPENJSON performance on our 2017 SQL Server instance is poor.

The query below, generated by EF Core 8, takes 1.8 seconds to run and results in nearly 400,000 reads:

DECLARE @__scheduleTagIds_0 nvarchar(4000) = N'[5835,5970,6563,6564,6565,6645,6835,6850,7034,7127]';

SELECT  [s].[ScheduleTagId]
       ,[s].[MustStartProdBy]
FROM    [ScheduleTagMustStartBy] AS [s]
WHERE   [s].[ScheduleTagId] IN (
    SELECT  [s0].[value]
    FROM    OPENJSON(@__scheduleTagIds_0) WITH ([value] int '$') AS [s0]
)

If I refactor the query to use a standard WHERE IN(...), the execution time drops to 120ms and 29,000 reads:

SELECT  [s].[ScheduleTagId]
       ,[s].[MustStartProdBy]
FROM    [ScheduleTagMustStartBy] AS [s]
WHERE   [s].[ScheduleTagId] IN (5835,5970,6563,6564,6565,6645,6835,6850,7034,7127)

I have hundreds of queries in my application that use .Where(x => [collection].Contains(x.Id)) and I'm very concerned about the performance degradation I'm seeing in select queries.

Question

What can I do to to mitigate this issue? I'm open to options either in EF or on the SQL Server side (though don't want to change the compatibility level of the database).

Answer 1

Based on the documentation, the only way to prevent the use of OPENJSON(... is to change the DB compatibility level to 120 (lower than SQL Server 2016). This is not an ideal solution. Is there any other way to limit/prevent the usage of OPENJSON?

You don't have to change the DB compatibility level to 120. You can call UseCompatibilityLevel(120) to tell EF to generate the old style SQL regardless of what the compat level is actually set to.

Other mitigations are listed in the documentation here (using EF.Constant in EF 8+ on a case by case basis or TranslateParameterizedCollectionsToConstants in EF 9 to do this globally whilst not changing the compat level).

Is there something configuration-wise on the SQL Server that would improve the performance of OPENJSON? It seems clear from the profiler data that SQL Server is not able to properly use index(es) when using OPENJSON.

It certainly can use indexes with OPENJSON it just might choose not to.

When you pass constants it can determine

• How many items are you passing?
• How many duplicates are you passing? If any?
• What are the exact values you are passing? (which may be useful if you have skewed cardinalities)

With OPENJSON it can't tell any of that so it just falls back to guesses. This may lead to a different (worse) plan.