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, while db.Records is an IQueryable translated into SQL, keys is an in-memory collection. EF Core cannot translate a Join between them on a composite key, so the query throws an exception at runtime. Calling AsEnumerable first would perform the join through LINQ to Objects, but doing so requires loading the database records into the client before matching them.
To perform the matching in the database, the composite-key rows must be converted into a form that can be passed to SQL. This article compares two implementations for doing so:
CompositeKeyPredicateBuilder, which expands the composite-key rows into aWHEREpredicateOpenJsonQueryBuilder, which sends the composite-key rows as JSON and expands them into a rowset 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 Composite-Key Rows into a WHERE Predicate
The first implementation, CompositeKeyPredicateBuilder, joins the comparisons for the columns of each composite-key row with AND, then joins the conditions for the rows with OR. Given two composite-key rows, 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 comparison expressions for the columns of each composite-key row and combines them into a single Expression<Func<TEntity, bool>> for that row.
An empty collection of composite-key rows produces a predicate that is always false, so the caller does not need a special branch for that case. The builder also detects different property counts, nonexistent property names, and type mismatches during construction.
This approach uses no SQL Server-specific syntax and fits naturally into existing LINQ queries. However, it generates one comparison per composite-key column for every composite-key row. The expression and SQL sizes therefore grow in proportion to the number of composite-key rows multiplied by the number of composite-key columns, increasing expression-construction, SQL-translation, and memory-allocation costs.
Passing Composite-Key Rows as JSON and Expanding Them with OPENJSON
The second implementation, OpenJsonQueryBuilder, serializes the composite-key rows as a JSON array and sends it in a single SQL parameter. SQL Server uses OPENJSON to expand the JSON into a rowset, then joins that rowset with the original table.
The generated SQL has the following form:
The constructor maps entity properties to input-key properties just as in the expression-tree approach. The query construction differs: the expression-tree builder returns a predicate for Where, whereas this builder takes a DbSet and the composite-key rows and returns an IQueryable directly.
Build generates and caches the SQL when each combination of entity type, table, schema, and property mappings is first used, then reuses it on subsequent calls. On every call, it serializes the current composite-key rows to JSON and passes the resulting parameter to FromSqlRaw with the cached SQL.
The generated SQL 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.
In CreateSql, the validated properties are used to generate the SELECT columns, OPENJSON input columns, and join conditions, which are inserted into one SQL statement. To produce the same result as the expression-tree approach when the input contains duplicate composite-key rows, SELECT DISTINCT is applied to the rowset before the join.
Unlike the expression-tree approach, increasing the number of composite-key rows with OPENJSON only adds elements to the JSON array. The SQL statement remains unchanged, and the JSON string remains the sole parameter. Each additional composite-key column adds one column definition to the OPENJSON WITH clause and one join condition, but this growth is independent of the row count.
This approach depends on SQL Server-specific OPENJSON syntax and raw SQL, so it 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 column types:
intandvarchar(50) - Database size fixed at 100,000 records
- Number of composite-key rows: 100, 1,000, and 10,000
- Both approaches use
AsNoTrackingto exclude tracking overhead
The database contains 100,000 records, and the composite-key rows are selected at even intervals across the table. Each composite key matches exactly one record. Each benchmark method returns the retrieved List<DbRecord> directly.
The two methods called by the benchmarks execute the following queries. Both apply AsNoTracking before retrieving the results.
The results were as follows:
Execution times are shown as the mean ± standard deviation.
| Number of composite-key rows | Expression tree | OPENJSON | Expression-tree allocation | OPENJSON allocation |
|---|---|---|---|---|
| 100 | 1.613 ± 0.053 ms | 1.536 ± 0.045 ms | 116.2 KB | 58.52 KB |
| 1,000 | 6.222 ± 0.072 ms | 4.927 ± 0.086 ms | 1,086.5 KB | 436.2 KB |
| 10,000 | 79.269 ± 1.278 ms | 49.578 ± 0.583 ms | 11,006.13 KB | 4,330.59 KB |
At 100 rows, OPENJSON was only 0.077 ms faster than the expression-tree approach, a marginal difference, while allocating about 50% as much memory. At 1,000 rows, the execution-time gap increased to 1.295 ms: OPENJSON took about 79% of the expression-tree execution time and allocated about 40% as much memory. At 10,000 rows, the gap widened to 29.691 ms, with OPENJSON taking about 63% of the expression-tree execution time and allocating about 39% as much memory.
These results were measured with the BenchmarkDotNet 0.15.6 LongRun job (3 launches, 15 warmup iterations, and 100 measurement iterations) on Windows 11 (10.0.26200.9457), using an AMD Ryzen 7 3800X (8 cores and 16 threads), .NET SDK 10.0.400, and .NET 10.0.11 x64. SQL Server 2022 ran in a Docker container on the same machine.
Choosing an Approach
The main considerations are the number of composite-key rows 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 |
| Use a small number of composite-key rows | Expression tree |
| Target SQL Server exclusively | OPENJSON |
| Use a large number of composite-key rows | OPENJSON |
| Limit SQL growth and memory allocation | OPENJSON |
For a small number of composite-key rows, the expression-tree approach is simple to use and avoids database-specific code. For a large number of rows, OPENJSON is a strong option when targeting SQL Server is acceptable because increasing the row count does not make the SQL statement longer.
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 translate the direct Join shown above between an IQueryable and an in-memory collection of composite-key rows. To perform the matching in the database, the composite-key rows must be converted into a representation that can be passed to SQL.
The expression-tree approach converts the composite-key rows 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 composite-key rows multiplied by the number of composite-key columns.
The OPENJSON approach sends the composite-key rows as one JSON parameter and expands them into a rowset in SQL Server. Increasing the row count does not change the SQL statement; adding composite-key columns adds column definitions and join conditions. It introduces a SQL Server dependency, but in this benchmark it provided lower execution time and memory allocation as the row count increased.