tl;dr; - If Entity Framework processes a query that holds greater than 2100 parameters with a .Contains, Entity Framework generates a query that SQL Server cannot adequately plan around, and it can absolutely devastate performance.
Let me cut straight to the point on this one - there is a subtle behavior in Entity Framework Core that you may or may not be aware of when you're using .Contains with SQL Server that can absolutely tank your performance.
SQL Server has a 2100 parameter limit. If you didn't know that, you should either A) be counting yourself lucky your queries are small enough where you've never had to think about that, or B) you might be feeling nervous about the contents of the rest of this post.
How .Contains Works in SQL Server
Whenever you use .Contains - EF Core compiles that query in one of two ways, depending on the number of items in the collection that you are filtering by.
Option #1 comes up when the number of parameters in your query (as a total) is less than 2100. In that case, SQL generates a WHERE ... IN clause with each of the parameters - it would look something like this:
SELECT * FROM [Users]
WHERE UserKey IN (@Value0, @Value1, ... @Value512)
Option #2 comes up whenever your number of parameters exceeds the 2100 parameter limit. In that case, Entity Framework cheats by using the SQL Server Table value function OPENJSON. Those queries are generated to look something like this
DECLARE @Value0 = '["1", "2", "3", ... "2500"]'
SELECT * FROM [Users]
WHERE UserKey IN (
SELECT [t].[value]
FROM OPENJSON(@Value0) WITH ([value] INT '$') AS [t]
)
these queries may not be exactly accurate, I'm doing this from memory, please be kind :)
In EF Core 8, dotnet introduced the WHERE IN syntax for all .Contains queries. Immediately after this was released in dotnet 8, people began to complain that this new query compiler behavior was absolutely wrecking their query performance (#32394).
What's the problem
To get into the why of what caused the degradation in performance - we need to take a quick detour into how the query planner and Table Value Functions in SQL Server work.
In order to determine the best way to evaluate a query - SQL Server generates query plans which are meant to determine the "best" strategies that it should use when working with a given set of tables. When Table Value Functions are used, SQL Server isn't able to natively get heuristics about the "table" that the function will return, so it uses a best guess in the range of 0 - 100. In our case - 50 was chosen.
Some of you may be beginning to spot the issue. Something like:
wait, so I'm passing in > 2100 parameters, but SQLs best guess is 50? That doesn't add up...
And you, astute reader, are perfectly on track!
See - with this erroneous estimate, SQL has lost its ability to correctly plan for the query that it will be executing. For us - we believe that this manifested as SQL deciding that it didn't need to create a hash set for the JSON parameter collection as it believed that spooling over the parsed table wouldn't incur many additional cycles. Which was woefully wrong.
Targeting a table with millions of rows, queries that had fewer than 2100 parameters were able to select their results in ~ 250 ms. Queries that used the OPENJSON strategy could take up to six minutes.
That means that as your application continues to expand, the data you query could approach a point where an otherwise innocuous LINQ query suddenly absolutely tanks your performance and usability. All of this without any sort of warning to the contrary.
Do other database providers have the same problem?
🤷 No clue - I haven't profiled other database providers since my work is predominantly in SQL Server and my personal projects that use postgres don't have near the amount of required data.
Moving Forward
I don't believe in presenting an issue without a potential solution.
We were able to work around this issue with a short-term and a long-term fix.
The short term fix was to chunk all of our queries. If we detected that we were going to call .Contains on a collection that would exceed the parameter limit, we dispatched CEIL( CollectionSize / 2048 ) queries - each with their own slice of the collection's data. Obviously this was less than ideal, as making N database roundtrips is a performance problem in of itself - though the N still didn't approach anywhere near the 6 minutes that we had regressed to.
The long term fix was quite a bit more technical - and I'd be happy to write more about it if there is interest.
What we found was that we could get our performance back if we could somehow shape the query that is going to the database in the format of
DECLARE @jsonArray = '[...]'
DECLARE @tableParameter1 TABLE
([value] INT PRIMARY KEY) =
SELECT [t].[value] FROM OPENJSON(@Value0)
WITH ([value] INT '$') AS [t]
SELECT ...
memory again, sorry for the fragmented query!
tableParameter1 is what it says on the tin - a parameter that has been declared to store an ad-hoc table instead of a scalar value.
Unfortunately, Entity Framework is hard-coded to only output a single SELECT, so there was no way for us alter the query to form that shape...
...is what I would have said if interceptors hadn't been added in Entity Framework Core 3!!!
With interceptors we have the ability to augment the actual query text that is being made to SQL Server. Typically these are used for adding things like query hints or creating some sort of common soft delete code. In our case, we were able to completely replace the occurrence of the OPENJSON table value functions, and replace them with our table parameter.
In order to make sure that we didn't globally change the behavior of .Contains throughout the app (even if it may be warranted), we employed Entity Framework's query tags to add an extension method on IQueryable to make sure that only queries we wanted to rewrite got rewritten.
Afterword
I don't want this to come off as negative towards Entity Framework itself or the Entity Framework team. So far I have managed to maintain a very healthy career using the building blocks that the maintainers have provided us for free, and EF has to serve a massive number of use cases and providers. This particular change reads as well intentioned. A slow query beats the old Entity Framework behavior of just throwing an exception. My guess is that once the regression was discovered in teh wild, rolling it back was no longer a clean option.
Still, when the failure mode is a six minute query with zero warning, I think that there needs to be some sort of bulletin somewhere about what can happen whenever the parameter counts begin to get too large for what SQL Server can support. Maybe its part of an MSDN doc, or a warning message issued via ILogger. Maybe it's this article itself (Hi! 👋). Who knows?
Either way - this behavior is subtle, and it seems to strike without warning. Code that is performing fine one day can all ofa sudden just fall apart leaving you scratching your head when the pager goes off. IMO - people should know.
Top comments (3)
This is a great example of why “it works fine” isn’t the same as “it scales fine.”
The 2100-parameter boundary is easy to miss because the LINQ code looks completely harmless. The interesting part for me is the execution-plan consequence of OPENJSON rather than the limit itself. A small change in cardinality estimation can completely change the plan when the underlying table is large.
I also like the interceptor + query-tag approach. It keeps the workaround targeted instead of changing Contains behavior across the entire application.
The 250ms → 6 minute regression is the part that really gets my attention. These are exactly the production issues that can survive code review because nothing looks obviously wrong in the application code.
I’d definitely be interested in seeing the long-term interceptor implementation and the benchmarks around the table-variable approach. That would be useful to compare against alternatives like TVPs or temporary tables.
I would like to get to know you better. Would you please contact me? telegram@CRDT_CTO
This is a great example of a performance issue that can stay completely invisible at the application level until the data scale crosses a threshold.
The part that stood out to me most is the difference between the logical LINQ query and the physical SQL plan. From the application's perspective,
.Contains()hasn't really changed, but once EF switches to theOPENJSONstrategy, SQL Server's cardinality estimation can lead it toward a completely different execution plan.I also like the way you approached the fix. Chunking is a pragmatic short-term solution, while using an interceptor with query tags gives you much more control without globally changing EF's behavior.
The six-minute versus ~250ms difference is especially striking. This is exactly the kind of production issue that makes profiling the generated SQL and execution plan just as important as profiling the application code itself.