EXISTS, Any, All, Contains, Correlated Subqueries, and Efficient Membership Tests

Learning outcomes

ServiceHub needs filters such as “work orders that have at least one safety tag,” “technicians for whom every assigned order is non-high-priority,” and “work orders whose IDs are in a caller-supplied set.” These are membership/existence questions. Their efficient relational forms are often EXISTS, NOT EXISTS, or IN, but exact translation—including collection parameters—depends on EF Core version and provider.

1. Any asks whether one qualifying row exists

// Existing course model — Chapter 09 extends queries, not persistence identity.public sealed partial class WorkOrder{    public int Id { get; private set; }    public string WorkOrderNumber { get; private set; } = string.Empty;    public string CustomerName { get; private set; } = string.Empty;    public WorkOrderPriority Priority { get; private set; }    public int? AssignedTechnicianId { get; private set; }    public Technician? AssignedTechnician { get; private set; }    public ICollection<WorkOrderTag> WorkOrderTags { get; } = new List<WorkOrderTag>();    public Guid Revision { get; private set; }}public sealed class Technician{    public int Id { get; private set; }    public string EmployeeCode { get; private set; } = string.Empty;    public string DisplayName { get; private set; } = string.Empty;    public IReadOnlyCollection<WorkOrder> WorkOrders => _workOrders;    private readonly List<WorkOrder> _workOrders = new();}public sealed class WorkOrderTag{    public int WorkOrderId { get; private set; }    public WorkOrder WorkOrder { get; private set; } = null!;    public int TagId { get; private set; }    public Tag Tag { get; private set; } = null!;    public DateTime AppliedUtc { get; private set; }    public string AppliedBy { get; private set; } = string.Empty;    public int DisplayOrder { get; private set; }}

var safetyOrders = db.WorkOrders    .Where(w => w.WorkOrderTags.Any(        wt => wt.Tag.Name == "safety"))    .Select(w => new { w.Id, w.WorkOrderNumber });Console.WriteLine(safetyOrders.ToQueryString());

SELECT w.work_order_id, w.work_order_numberFROM work_orders AS wWHERE EXISTS (    SELECT 1    FROM work_order_tags AS wt    INNER JOIN tags AS t ON wt.tag_id = t.tag_id    WHERE wt.work_order_id = w.work_order_id      AND t.name = @__tag_0);

The subquery is correlated because it references the current outer work-order key. The database can stop searching after it proves existence; do not replace this with loading the entire tag collection just to call Any() in memory.

2. All usually becomes NOT EXISTS of a counterexample

var technicians = db.Technicians    .Where(t => t.WorkOrders.All(        w => w.Priority != WorkOrderPriority.High));

Mathematically, “all rows satisfy P” is equivalent to “there does not exist a row that violates P.” A provider commonly emits NOT EXISTS (... WHERE NOT P ...). Note the vacuous truth rule: a technician with zero work orders satisfies All. If the business requires at least one assignment, combine Any() with All(...).

var activeAndSafe = db.Technicians    .Where(t => t.WorkOrders.Any() &&                t.WorkOrders.All(w => w.Priority != WorkOrderPriority.High));

3. Contains over a query becomes relational membership

var taggedIds = db.Set<WorkOrderTag>()    .Where(wt => wt.AppliedBy == actor)    .Select(wt => wt.WorkOrderId);var orders = db.WorkOrders    .Where(w => taggedIds.Contains(w.Id));

This can translate to IN (subquery) or an equivalent EXISTS form. Do not force one spelling because the optimizer/provider may normalize them differently. Compare generated SQL and plan evidence for the production-like engine.

4. EF Core 10 changed parameterized collection translation

For a caller-supplied primitive collection, EF Core 10 defaults to multiple scalar SQL parameters when possible. EF Core 8/9 commonly used a single JSON-like collection parameter for SQL Server. This is a version-sensitive behavior, not timeless LINQ semantics.

int[] ids = [101, 205, 309];var selected = db.WorkOrders    .Where(w => ids.Contains(w.Id));Console.WriteLine(selected.ToQueryString());

SELECT ...FROM work_orders AS wWHERE w.work_order_id IN (@ids1, @ids2, @ids3);

EF may pad parameter counts to reduce the number of distinct SQL shapes. Exact parameter naming/padding is provider/version-specific; inspect your real command log.

5. Per-query collection translation controls are evidence tools

var defaultMode = db.WorkOrders    .Where(w => EF.MultipleParameters(ids).Contains(w.Id));var constants = db.WorkOrders    .Where(w => EF.Constant(ids).Contains(w.Id));var singleParameter = db.WorkOrders    .Where(w => EF.Parameter(ids).Contains(w.Id));

These APIs let you test plan/cache behavior instead of globally guessing. EF.Constant can produce different SQL text per collection and affect plan-cache reuse. EF.Parameter asks for a single collection parameter where the provider supports a representation. None is universally fastest; record collection cardinality and plan evidence.

6. Deliberately wrong: huge request list as “just another Contains”

// Imagine 40,000 caller-supplied IDs.var rows = await db.WorkOrders    .Where(w => request.Ids.Contains(w.Id))    .ToListAsync(ct);

Even if translation succeeds, the request can create a large SQL/parameter payload, hit engine/provider limits, increase compilation cost, or produce poor plans. The repair depends on the engine and topology: bounded/chunked requests, a temporary/staging table, SQL Server table-valued parameter, PostgreSQL array/unnest strategy, or other provider-supported bulk-membership mechanism. Keep the mandatory lab small and free/local; discuss provider-native mechanisms without pretending they are portable EF abstractions.

7. NULL and anti-membership deserve special care

NOT IN in SQL behaves surprisingly when the compared set contains NULL because three-valued logic can turn the predicate UNKNOWN. NOT EXISTS often expresses anti-existence more robustly. EF’s LINQ translation includes null-semantics compensation in many cases, but always inspect SQL when nullable keys/values participate.

var untagged = db.WorkOrders    .Where(w => !w.WorkOrderTags.Any());

This directly states “no related join row exists” and avoids materializing a list of IDs to negate in memory.

8. Plan evidence: syntax is not the cost model

For small lists, IN with scalar parameters may be ideal. For large lists, cardinality knowledge, parameter limits, index selectivity, and compilation behavior matter. Capture ToQueryString, executed parameter count, and a database plan. On SQLite, use EXPLAIN QUERY PLAN; on production-like SQL Server/PostgreSQL use the engine’s real plan tooling.

9. Hands-on lab: existence and collection modes

1. Seed tags so some work orders have “safety,” some have other tags, and some have none.
2. Run Any and capture the EXISTS-shaped SQL.
3. Run All for technicians including one with zero work; explain vacuous truth, then add Any.
4. Run Contains with 3, 20, and 100 IDs; record generated parameter counts and command text.
5. Compare default EF.MultipleParameters, EF.Constant, and EF.Parameter only where your provider supports them; record translation/plan differences.
6. Run the anti-existence query for untagged work orders.
7. Do not extrapolate one SQLite plan to SQL Server/PostgreSQL production behavior.

10. Production judgment and bridge

Use Any/All to express existence semantics, not collection-loading convenience. Treat external membership lists as an input-size/design problem, especially after EF Core 10’s translation change. Lesson 4 now combines entire query result sets with Union, Concat, Intersect, and Except, where shape compatibility and bag-vs-set semantics become decisive.

Authoritative references

• EF Core 10 breaking changes — parameterized collection default and mitigation modes
• Complex Query Operators - EF Core — correlated query translation context
• Query null semantics - EF Core — C# vs SQL NULL semantics
• Efficient Querying - EF Core — index/query-shape evidence
• SQLite Query Planner — free local plan context