LINQ in .NET 9: Deferred Execution, IQueryable, and the New Operators

Problem Context

LINQ has been the .NET data-shaping toolkit since 2007. But "use LINQ" still hides a chasm of decisions: IEnumerable vs IQueryable, deferred vs immediate execution, when ToList() is a footgun, and which of the 200+ operators is actually right. .NET 9 just added three more (CountBy, AggregateBy, Index) that replace common boilerplate.

This guide is the operational view: how to write LINQ that doesn't enumerate three times, doesn't silently translate to N+1 SQL queries, and uses the .NET 9 additions where they pay off.

Concept Explanation

LINQ is two stacks sharing one syntax. IEnumerable<T> runs in-process via delegates — every operator is just a method call. IQueryable<T>builds an expression tree that a provider (EF Core, Cosmos, OData) translates to its own query language. The rules differ wildly. The same code that's a fast SQL WHERE in EF Core can become a full table load in memory if you cast the wrong way.

flowchart TB
    A["Your LINQ code"] --> B{"Source type?"}
    B -->|IEnumerable<T>| C["LINQ to Objects - in-process delegates"]
    B -->|IQueryable<T>| D["Expression tree"]
    D --> E["EF Core provider - SQL"]
    D --> F["Cosmos provider - SQL/JSON"]
    D --> G["OData provider"]
    C --> H["Deferred until enumerated"]
    D --> H
    H --> I["foreach / ToList / First / Count"]

    style D fill:#4f46e5,color:#fff,stroke:#4338ca
    style I fill:#059669,color:#fff,stroke:#047857

Two non-negotiable rules: (1) all LINQ operators are deferred — nothing executes until you enumerate (foreach, ToList, ToArray, First, Count, Any). (2) Calling .AsEnumerable() or .ToList() on an IQueryable ends translation and pulls the rest of the pipeline into memory. This is the single most common LINQ performance bug.

Implementation

Step 1: The .NET 9 additions

using System.Linq;

string[] words = "the quick brown fox jumps over the lazy dog the end".Split(' ');

// Before .NET 9 — allocates a Lookup<string, string> just to count
var oldWay = words
    .GroupBy(w => w)
    .Select(g => new { Word = g.Key, Count = g.Count() })
    .OrderByDescending(x => x.Count)
    .First();

// .NET 9 — no intermediate grouping, single pass
var (mostCommon, count) = words
    .CountBy(w => w)
    .MaxBy(kv => kv.Value);
// mostCommon = "the", count = 3

// AggregateBy — like CountBy but with a custom accumulator
record Score(string Player, int Points);
Score[] rounds =
[
    new("Alice", 10), new("Bob", 5), new("Alice", 7), new("Bob", 12)
];
var totals = rounds.AggregateBy(
    keySelector: r => r.Player,
    seed: 0,
    func: (acc, r) => acc + r.Points);
// totals: { ("Alice", 17), ("Bob", 17) }

// Index — finally a built-in for "give me the index too"
foreach (var (i, word) in words.Index())
{
    Console.WriteLine($"{i}: {word}");
}

CountBy and AggregateBy avoid the hidden allocation in GroupBy: previously, every group materialized a sub-collection even if you only needed the count or sum. Index replaces the .Select((value, index) => (index, value)) idiom that everyone has copy-pasted from Stack Overflow.

Step 2: IEnumerable vs IQueryable — where to draw the line

// CORRECT — predicate translates to SQL WHERE
var activeAdults = await db.Users
    .Where(u => u.IsActive && u.Age >= 18)
    .Select(u => new UserDto(u.Id, u.Name))
    .ToListAsync(ct);

// WRONG — .AsEnumerable() ends translation. Pulls the entire Users table.
var activeAdultsBad = db.Users
    .AsEnumerable()
    .Where(u => u.IsActive && u.Age >= 18)
    .ToList();

// WRONG — local method calls EF can't translate. Loads all rows then filters in memory.
var matches = db.Users
    .Where(u => MyHelper(u.Name)) // throws "could not be translated" or silently materializes
    .ToList();

// CORRECT — push complex logic into the expression tree
var matchesOk = db.Users
    .Where(u => u.Name.StartsWith(prefix) && EF.Functions.Like(u.Email, "%@corp.com"))
    .ToList();

Rule: compose IQueryable as long as possible, materialize once with ToListAsync / FirstOrDefaultAsync / AnyAsync. The moment you need an in-memory operation (e.g., calling a non-translatable method), evaluate explicitly with .AsEnumerable() and accept that everything below it runs client-side.

Step 3: Streaming with IAsyncEnumerable

// Stream rows without materializing the whole list — great for large exports or SSE
public async IAsyncEnumerable<UserDto> StreamUsersAsync(
    [EnumeratorCancellation] CancellationToken ct = default)
{
    await foreach (var user in db.Users
        .AsNoTracking()
        .Where(u => u.IsActive)
        .Select(u => new UserDto(u.Id, u.Name))
        .AsAsyncEnumerable()
        .WithCancellation(ct))
    {
        yield return user;
    }
}

// Minimal API endpoint — streams JSON as rows arrive
app.MapGet("/users/stream", (UserService svc, CancellationToken ct)
    => svc.StreamUsersAsync(ct));

IAsyncEnumerable<T> is the right shape for "many rows, one at a time". ASP.NET Core serializes it as a streaming JSON array — the client sees bytes as soon as the first row is ready, instead of waiting for ToListAsync.

Step 4: Operator picks that matter

// Existence check — Any() short-circuits, Count() > 0 doesn't (in LINQ to Objects)
if (await db.Orders.AnyAsync(o => o.UserId == id, ct)) { /* ... */ }

// Single value — pick the right one
var u = await db.Users.SingleAsync(u => u.Email == email, ct);          // expects EXACTLY 1; throws otherwise
var u2 = await db.Users.SingleOrDefaultAsync(u => u.Email == email, ct); // 0 or 1
var u3 = await db.Users.FirstAsync(u => u.IsActive, ct);                // first match; throws if none
var u4 = await db.Users.FirstOrDefaultAsync(u => u.IsActive, ct);       // null if none

// Projection — Select before filtering when you only need a subset of columns
var emails = await db.Users
    .Where(u => u.IsActive)
    .Select(u => u.Email)   // SELECT Email FROM Users — not SELECT *
    .ToListAsync(ct);

// .NET 6+ — Chunk for batch processing
foreach (var batch in items.Chunk(500))
{
    await ProcessBatchAsync(batch, ct);
}

Pitfalls

1. Multiple enumeration. IEnumerable<T> can be enumerated more than once; each pass re-runs the whole pipeline. If you call .Count() then foreach, you ran the query twice. Materialize once with .ToList().

2. Captured loop variable in EF queries. for (int i = 0; i < 10; i++) query = query.Where(x => x.Id == i);captures i by reference — every clause checks the final value. Always copy to a local: int copy = i;.

3. OrderBydoesn't mutate. list.OrderBy(...) returns a new sequence; the original is unchanged. Use list.Sort() for in-place, or assign the result.

4. GroupBy in EF Core is restricted. EF Core can translate GroupBy followed by an aggregate (.Select(g => new { g.Key, Count = g.Count() })), but not grouping that returns the elements themselves. If you need the rows per group, fetch and group in memory — or restructure with CountBy/AggregateBy.

5. ToList in async paths. The synchronous .ToList() on an EF query blocks a thread. Always use ToListAsync(cancellationToken) in ASP.NET Core handlers. Same for First, Single, Count, Any.

Practical Takeaways

• On .NET 9, replace .GroupBy(...).Select(g => new { g.Key, Count = g.Count() }) with .CountBy(...). Fewer allocations, clearer intent.
• Use AggregateBy when you'd otherwise reach for GroupBy + Sum on a single key.
• Replace .Select((v, i) => (i, v)) with .Index().
• Push every translatable predicate into the IQueryable chain. Materialize once at the end with ToListAsync.
• Default to FirstOrDefaultAsync; use SingleAsync only when "exactly one" is a domain invariant whose violation should crash.
• For streaming responses, return IAsyncEnumerable<T> from your endpoint — ASP.NET Core handles JSON streaming natively.
• If a query is slow, log the generated SQL (db.Database.LogTo(Console.WriteLine)) before guessing. The generated query rarely matches your mental model.