Efficient query Entity with composite primary keys in Entity Framework

Implementing the same functionality as a simple Contains() query becomes more complex when using Entity Framework and composite primary keys. This article discusses various ways to solve this problem:

Try using Contains

Attempting to use a tuple representing a composite primary key value for a direct join or a Contains operation fails in the Entity Framework. This is because such operations cannot be converted to SQL, because tuples are not considered as original values ​​in this context.

Using memory processing

Another way is to use AsEnumerable() to extract the database table data into memory and perform the necessary filtering using LINQ to Objects. However, for large tables, this solution is extremely inefficient.

Use two Contains statements (incorrect)

Using a separate Contains() statement for each composite primary key component will result in incorrect filtering. This approach can produce misleading results because entities containing only one matching component will be mistakenly included.

Use calculated values ​​for a single Contains query

You can use a modified Contains() query containing the calculated values ​​derived from the composite primary key component (for example, entity.Id1 * entity.Id2). However, this solution is non-searchable and leads to poor performance.

Combining Contains and Memory Connection (Solution 5)

The most scalable method is to combine Contains and memory connections. This method uses a preliminary Contains() query to narrow down the result set and then refine it more precisely via memory connections.

Build queries using OR clauses (Solution 6)

Preliminary builders like Linqkit allow the creation of queries containing OR clauses for each composite primary key value combination. While this method may work for small lists, for large lists, its performance will be degraded.

Use joint (Solution 7)

Another way is to use UNION to combine multiple queries that combine each composite primary key value. This solution may work for small to medium-sized lists.