Using WHERE IN with literal strings in Entity Framework

This one cost me an afternoon. The query ran without complaint and handed back the wrong rows.

Suppose we have a model like:

public record Agency
{
    public int Id { get; set; }
    public string AgencyCode { get; set; }
}

And a bunch of records like:

We want to select records based on a list of agency codes that's been provided. For example, abc1, abc2, abc3.

In plain old SQL this is as simple as:

SELECT * FROM Agency WHERE AgencyCode IN ('abc1', 'abc2', 'abc3');

And that returns what you'd expect. Three rows.

In Entity Framework you'd write:

var agencyCodes = new[] { "abc1", "abc2", "abc3" };

var results = await _dbContext
    .Agencies
    .Where(a => agencyCodes.Contains(a.AgencyCode))
    .ToListAsync();

Which looks fine, compiles fine, and returns four rows.

What EF sends to the database

SELECT [a].[Id], [a].[AgencyCode]
FROM [Agencies] AS [a]
WHERE [a].[AgencyCode] IN (
    SELECT [a0].[value]
    FROM OPENJSON(@__agencyCodes_0) WITH ([value] nvarchar(4) '$') AS [a0]
)

The problem sits in the WITH clause.

EF Core 8 changed how collections reach the database. Older versions inlined the values as constants, which produced a different SQL string for every distinct number of items and destroyed query plan caching. EF 8 serialises the array to a JSON string, sends it as one parameter, and unpacks it server-side with OPENJSON. One SQL string regardless of how many values you pass, which is a sensible trade.

But OPENJSON ... WITH needs a type for the column it projects, and EF infers that from the mapped column type. AgencyCode is nvarchar(4), so the values coming out of the JSON array get typed as nvarchar(4) too.

SQL Server then truncates anything longer to four characters, without raising anything.

That truncation applies to the values from your parameter. Pass in abc10 and the database compares abc1, which matches a row you never asked for. This is dotnet/efcore#32735: a length-limited column plus Contains gives you false positives, with no error and no warning.

It's a nasty one, because it only surfaces when your data has values sharing a prefix and differing in length. Which, for anything code-shaped like agency codes, SKUs or store numbers, describes most real datasets.

Fixing it

To fix it, you want the literal IN ('abc1', 'abc2', 'abc3') you'd have written by hand. Getting there depends on your EF version.

EF Core 8

Drop the SQL compatibility level. Anything below 130 tells EF that OPENJSON isn't available, so it falls back to inlining constants:

optionsBuilder.UseSqlServer(connectionString, o => o.UseCompatibilityLevel(120));

Blunt, and global. It affects other translations too, so treat it as a workaround. If you're stuck on 8 and shipping tomorrow, it works.

EF Core 9

Two proper options.

Per query, with EF.Constant:

var results = await _dbContext
    .Agencies
    .Where(a => EF.Constant(agencyCodes).Contains(a.AgencyCode))
    .ToListAsync();

Which gives you the SQL you wanted:

SELECT [a].[Id], [a].[AgencyCode]
FROM [Agencies] AS [a]
WHERE [a].[AgencyCode] IN (N'abc1', N'abc2', N'abc3')

Or globally, reverting to pre-8 behaviour everywhere:

optionsBuilder.UseSqlServer(connectionString, o => o.TranslateParameterizedCollectionsToConstants());

I'd reach for EF.Constant first. It's targeted, and you can see it at the call site. The global switch changes the plan-caching behaviour of every other query in the application, and you won't notice until something slows down.

EF Core 9 and 10

UseParameterizedCollectionMode supersedes both of the above and gives you all three strategies:

optionsBuilder.UseSqlServer(connectionString,
    o => o.UseParameterizedCollectionMode(ParameterTranslationMode.MultipleParameters));

TranslateParameterizedCollectionsToConstants is marked obsolete in favour of it.

EF Core 10 also changes the default. Instead of OPENJSON, it generates one scalar parameter per element:

WHERE [a].[AgencyCode] IN (@codes1, @codes2, @codes3)

Each parameter gets typed on its own, so the truncation can't happen, and the query planner gets a proper look at what it's dealing with. On EF 10 this bug is gone by default.

That cuts both ways. Code written against 8 or 9 that relied on OPENJSON behaviour with large collections can regress when you upgrade to 10, and the fix is EF.Parameter(...) or setting the mode back to Parameter.

The takeaway

If you're on EF Core 8 or 9 and you query with Contains against a column that has a MaxLength, go and check your results. Not the generated SQL, the rows that come back. The query looks reasonable, runs without error, and hands you records you didn't ask for.

A test with abc10 sitting alongside abc1 would have caught it in seconds. Mine didn't, because my test data was tidy and every code was the same length. Real data rarely is.