The Entity Framework: WHERE IN vs. OPENJSON Problem

I’ve been investigating a performance issue with a slow query that is generated by Entity Framework (9.x) running on a v15 SQL Server database. The query is pretty simple. It’s just selecting rows based on one or more values that are in an indexed string column. But when executed, it doesn’t appear that the index is used at all – making the query run very slowly.

OPENJSON in Entity Framework 8+

This is an example of some Entity Framework code that would generate a WHERE IN clause, prior to the changes that were made in EF 8:

var names = new[] { "Blog1", "Blog2" };

var blogs = await context.Blogs
    .Where(b => names.Contains(b.Name))
    .ToArrayAsync();

And the generated SQL would look like this:

  SELECT [b].[Id], [b].[Name]
  FROM [Blogs] AS [b]
  WHERE [b].[Name] IN (N'Blog1', N'Blog2')

Starting with EF8, however, it generates something more like this:

DECLARE @__names_0 nvarchar(MAX) = N'["Blog1", "Blog2"]';  -- JSON array of IDs
SELECT [b].[Id], [b].[Name]
FROM [Blogs] AS [b]
WHERE [b].[Name] IN (
    SELECT [n].[value]
    FROM OPENJSON(@__names_0) WITH ([value] nvarchar(max) '$') AS [n]
)

Unfortunately for me, according to the Breaking changes in EF Core 8 page, regarding the change to using OPENJSON:

…while the new SQL is more efficient for most cases, it can be dramatically less efficient in a minority of cases, even causing query timeouts in some cases.

It seems that my application is in that minority of cases.

For the particular database/table/index combination I’m using, the OPENJSON version of the SELECT takes around 5 seconds to provide results. But changing that to a WHERE IN provides results in only a couple hundred milliseconds. Exactly what I would expect given the index on the column.

Mitigations

The Mitigations section of the “Breaking Changes” page provides a few ways to tell Entity Framework to use the WHERE IN style, instead of OPENJSON.

I experimented with disabling the OPENJSON syntax entirely, which is possible with EF 9 like this:

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder.UseSqlServer("", o => o.TranslateParameterizedCollectionsToConstants())

That change fixes the particular query I’ve been having problems with. However, interestingly, it caused some other queries to run considerably slower. But there’s another technique you can use instead, on a case-by-case basis:

var blogs = await context.Blogs
  .Where(b => EF.Constant(names).Contains(b.Name))
  .ToArrayAsync();

The use of EF.Constant() tells Entity Framework to use a WHERE IN clause instead of the OPENJSON style for that particular part of the WHERE clause.

By using a combination of WHERE IN and OPENJSON, I’ve been able to optimize my queries – greatly improving the performance of the existing queries without having to make any changes to my existing database structure.