Applications often need to retrieve database records that match a list of keys received from an external API or a CSV file. A single-column key can be handled with Contains, but a composite key is less straightforward.
Suppose we want to join the database entity DbRecord with the in-memory type CompositeKey on Key1 and Key2.
The most direct approach would be to write the following Join:
However, db.Records is an IQueryable that must be translated into SQL, while keys is an in-memory collection. EF Core cannot translate this composite-key Join into SQL, so the query throws an exception at runtime.
Calling AsEnumerable first would make the join work through LINQ to Objects, but it would require loading the database records into the client before matching them. Instead, the key collection needs to be transformed into a representation that can be matched on the database side.
This article compares two implementations for doing so:
CompositeKeyPredicateBuilder, which expands the keys into aWHEREpredicateOpenJsonQueryBuilder, which sends the keys as JSON and turns them back into rows with SQL Server'sOPENJSON
Only the relevant parts of the code are shown here. The complete implementations and benchmark project are available in the GitHub repository.
Expanding the Keys into a WHERE Predicate
The first approach builds a predicate by joining the components of each composite key with AND and joining the keys themselves with OR. Given two keys, it produces a condition like this:
EF Core can translate this expression as a regular Where predicate. The caller specifies the corresponding properties on the entity and input-key types, builds the predicate, and passes it to Where.
At its core, the builder creates a comparison expression for each key and combines them into a single Expression<Func<TEntity, bool>>.
An empty key collection produces a predicate that is always false, so the caller does not need a special branch for that case. The builder also detects mismatched property counts, names, and types during construction.
This approach does not use SQL Server-specific syntax and fits naturally into an existing LINQ query. On the other hand, both the expression and the generated SQL grow with the number of keys, increasing the cost of expression construction, SQL translation, and memory allocation.
Passing the Keys as a Rowset with OPENJSON
The second approach serializes the key collection as JSON and sends it in a single SQL parameter. SQL Server uses OPENJSON to turn the JSON back into rows and columns, then joins that rowset with the table.
The generated SQL has the following form:
The call site is nearly identical to the expression-tree approach:
Build serializes the key collection and passes the generated SQL to FromSqlRaw.
The OPENJSON WITH clause uses the actual table name, column names, and SQL types obtained from the EF Core model. Before executing any SQL, the builder detects configuration errors such as an unmapped entity property, a missing property on the input-key type, or mismatched CLR types.
he SQL-building portion of CreateSql generates the SELECT columns, OPENJSON input columns, and join conditions from the validated properties, then inserts them into a single SQL statement.
The shape of the SQL does not grow with the number of keys, and the only parameter is the JSON string. The tradeoff is a dependency on SQL Server-specific OPENJSON syntax and raw SQL, so this implementation cannot be ported unchanged to another database provider.
Benchmark
The two approaches were measured in the following environment:
- .NET 10 / EF Core 10.0.0
- SQL Server 2022
- BenchmarkDotNet 0.15.6
- Composite key:
intandvarchar(50) - Key counts: 100, 1,000, and 10,000
Each benchmark prepares one matching database record for every key, executes the query, and returns the number of retrieved records. The measurement therefore includes not only predicate or SQL generation, but also matching in SQL Server and reading the results.
The results were as follows:
| Key count | Expression tree | OPENJSON | Expression-tree allocation | OPENJSON allocation |
|---|---|---|---|---|
| 100 | 1.953 ms | 1.520 ms | 116.2 KB | 57.43 KB |
| 1,000 | 6.018 ms | 4.074 ms | 1,086.5 KB | 431.79 KB |
| 10,000 | 80.096 ms | 49.037 ms | 11,006.13 KB | 4,310.55 KB |
At 100 keys, the difference was 0.433 ms. At 10,000 keys, it grew to 31.059 ms. The OPENJSON approach took about 61% of the expression-tree execution time and allocated about 39% as much memory. The benefit of avoiding an expanded predicate became more pronounced as the key count increased.
These numbers apply only to the data types, result counts, SQL Server configuration, and execution environment used here. Actual performance also depends on table size, indexes, network conditions, and the number of columns and rows returned.
Choosing an Approach
The main considerations are the number of keys and whether a database-specific implementation is acceptable.
| Requirement | Preferred approach |
|---|---|
| Support databases other than SQL Server | Expression tree |
| Compose the query as regular LINQ | Expression tree |
| Match a relatively small key collection | Expression tree |
| Target SQL Server exclusively | OPENJSON |
| Frequently match large key collections | OPENJSON |
| Limit SQL growth and memory allocation | OPENJSON |
For a small key collection, the expression-tree approach is simple to use and avoids database-specific code. For a large collection, OPENJSON is a strong option when targeting SQL Server is acceptable because it sends the keys as a rowset without changing the shape of the SQL.
Running the Benchmark
The repository includes a Docker Compose configuration for starting SQL Server:
Set the connection string and run the benchmark in the Release configuration:
Conclusion
EF Core cannot necessarily translate a direct Join between an IQueryable and an in-memory set of composite keys. To perform the matching in the database, the key collection must be converted into a representation that can be sent to SQL.
The expression-tree approach converts the keys into a Where predicate composed of AND and OR expressions. It integrates naturally with LINQ and avoids database-specific features, but the expression and SQL grow with the number of keys.
The OPENJSON approach sends the key collection as one JSON parameter and turns it back into a rowset in SQL Server. It introduces a SQL Server dependency, but in this benchmark it provided lower execution time and memory allocation as the key count increased.