Introduction

Using .Contains on a list of primary keys is often translated by ORMs into a SQL IN predicate. That is not automatically slow. For small and moderate key lists on an indexed primary key column, it is usually exactly the right query.

The real performance problem appears when the key list becomes very large, the query is built inefficiently, or the ORM falls back to client-side work. In those cases, a table-valued parameter, temp table, or batching strategy is often better.

What .Contains Usually Becomes

In Entity Framework or LINQ-to-SQL style code, this:

1var ids = new[] { 1, 5, 8, 13 };
2var users = await db.Users
3    .Where(u => ids.Contains(u.Id))
4    .ToListAsync();

typically becomes SQL shaped like:

SELECT *
FROM Users
WHERE Id IN (1, 5, 8, 13);

If Id is the primary key, the database already has an index that is ideal for this lookup. For a short list, there is usually nothing wrong with this plan.

So the first correction is important: .Contains is not inherently slow. It is often slow only when people scale it beyond the point where a large IN list remains practical.

When It Starts to Hurt

Large key sets create several problems:

• SQL text becomes large.
• Parameter count rises.
• Plan reuse becomes harder.
• Network payload increases.
• Optimizer choices may degrade.

If you are passing a few dozen keys, this is normally fine. If you are passing thousands or tens of thousands, the shape of the query may become the bottleneck rather than the indexed lookup itself.

At that point, the right fix is usually to move the key set into a relational structure and join against it.

Better Options for Large Lists

For SQL Server, a table-valued parameter is a strong option.

1CREATE TYPE dbo.IntIdList AS TABLE
2(
3    Id INT NOT NULL PRIMARY KEY
4);
5GO

Then a stored procedure can join against it:

1CREATE PROCEDURE dbo.GetUsersByIds
2    @Ids dbo.IntIdList READONLY
3AS
4BEGIN
5    SELECT u.Id, u.Name
6    FROM dbo.Users AS u
7    INNER JOIN @Ids AS i ON i.Id = u.Id;
8END;
9GO

From C#, pass the IDs as structured data instead of expanding them into a long IN clause.

1using System.Data;
2using Microsoft.Data.SqlClient;
3
4var table = new DataTable();
5table.Columns.Add("Id", typeof(int));
6foreach (var id in ids)
7{
8    table.Rows.Add(id);
9}
10
11var parameter = new SqlParameter("@Ids", table)
12{
13    SqlDbType = SqlDbType.Structured,
14    TypeName = "dbo.IntIdList"
15};

That lets the database process the key set as a table rather than as a long parameter list.

What About Repeated Single-Key Fetches?

Sometimes people replace one Contains query with a loop of primary-key lookups. That is usually worse because it turns one set-based query into many round trips.

If the entities may already be tracked in the DbContext, APIs like FindAsync can be efficient for a single key. But for many keys that are not already local, set-based retrieval is still the right mindset.

When the list is huge, batch it or join against a key table. Do not fall into an N+1 pattern.

Common Pitfalls

A common mistake is diagnosing .Contains as the root cause without inspecting the generated SQL and the execution plan. Sometimes the real issue is missing indexes, extra includes, or accidental client evaluation.

Another mistake is passing a very large collection directly from memory and expecting the database to treat it like a normal indexed table. It is still an expanded list unless you load it into a relational structure.

Developers also sometimes fetch rows one by one because a big IN clause felt slow. That usually trades one scaling problem for a worse one.

Finally, do not guess about the threshold where batching or TVPs become worthwhile. Measure with your schema, your data size, and your network characteristics.

Summary

• '.Contains on primary keys usually translates to IN (...) and is fine for small to moderate lists.'
• It becomes problematic mainly when the ID list gets very large.
• For large sets, use a table-valued parameter, temp table, or batching strategy.
• Avoid replacing one set-based query with many single-key round trips.
• Inspect generated SQL and execution plans before deciding where the real bottleneck is.