When working with Entity Framework (EF), you often encounter scenarios where you need to filter a database table by a set of composite keys that you've calculated or retrieved in memory. Traditionally, this can lead to inefficient queries or complex workarounds.

C# Tuples offer a clean, type-safe way to represent these composite keys and perform batch filtering.

The Problem

Imagine you have a list of VariantId and Term pairs, and you need to retrieve RentalPrice records from the database that match both values. If you're using an older version of EF or EF Core, you might find that Contains on a list of complex objects doesn't translate directly to SQL.

The Tuple Approach (In-Memory Filtering)

If your dataset isn't massive, or if you're restricted by your EF version, an in-memory filter using Tuples is a robust solution. Here's how you can implement it:

private async Task<List<RentalPrice>> GetRentalPrices(
    IEnumerable<ProductVariant> variants, 
    IEnumerable<ImportRequest> batch)  
{  
    // 1. Build a list of composite keys using Tuples
    var keys = new List<(int VariantId, int? Term)>();
 
    foreach (var item in batch)  
    {  
        var variant = variants.FirstOrDefault(x => x.Sku == item.Sku);  
        if (variant != null)  
        {  
            keys.Add((variant.Id, item.Term));  
        }  
    }
 
    using (var db = new AppDbContext())  
    {  
        // 2. Fetch records into memory (be careful with table size!)
        var allRentalPrices = await db.RentalPrices.ToListAsync();  
        
        // 3. Filter using the Tuple collection
        var result = allRentalPrices  
            .Where(w => keys.Contains((w.VariantId, w.Term)))
            .ToList();
 
        return result;  
    }  
}

Why this works

By using (int, int?) tuples, you get a value-type comparison for free. The keys.Contains(...) check correctly matches the properties of each RentalPrice record against your in-memory list without having to write custom equality comparers or complex Where clauses.

Performance Considerations

The implementation above uses .ToListAsync() to pull the entire table into memory before filtering. This is fine for small reference tables, but for large datasets, it will cause performance issues and high memory usage.

The Modern EF Core Way (8.0+)

Starting with EF Core 8.0, you can often perform this filtering directly on the database side using primitive collections or anonymous types. EF Core can now translate Contains on collections of anonymous types into efficient SQL (like a VALUES clause or IN with multiple columns).

// Modern EF Core 8.0 example
var keys = batch.Select(x => new { x.VariantId, x.Term }).ToList();

var results = await db.RentalPrices
    .Where(p => keys.Contains(new { p.VariantId, p.Term }))
    .ToListAsync();

This version executes entirely on the SQL server, returning only the records you need.

Conclusion

Whether you're forced to filter in-memory or can leverage the latest EF Core features, Tuples and anonymous types provide a clean syntax for managing composite keys. For legacy projects, the Tuple Contains approach is a great tool for your utility belt.