Start With Whatever Reads Best

Over a small list in memory, write whichever LINQ reads best. It won't matter. The rules below are for queries that reach a database or run in a hot path, where code that looks the same can do very different amounts of work.

Take a typical case, made up for illustration. A sales report filtered last month's big orders and took 40 seconds. The query looked fine. The problem was one method upstream returning IEnumerable<Order> instead of IQueryable, so the Where ran in C# after EF Core had fetched all two million rows. Changing the return type made it run in under a second.

LINQ looks the same whether it runs in memory or becomes SQL, and that is exactly what makes it easy to get wrong. Below is the difference between IEnumerable and IQueryable, deferred execution and the multiple-enumeration trap, the operators worth knowing (including the .NET 9 additions), and how to check what actually reaches the database.

If a LINQ query is mysteriously slow
  • Your endpoint takes 2 seconds and the SQL log shows the same query running 47 times
  • You wrote .GroupBy(x => x.Category).Select(g => new { g.Key, Count = g.Count() }) and want to know if there's a better way in modern .NET
  • You're unsure whether .Where().First() or .First(predicate) is faster (spoiler: on EF Core, it's the same SQL)
  • You see .ToList().Where(...) in a code review and the alarm bells are ringing

Deferred Execution and the Two LINQs

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&lt;T&gt;| C["LINQ to Objects - in-process delegates"]
    B -->|IQueryable&lt;T&gt;| 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) query operators such as Where and Select 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.

✅

Check it: Find a method that returns IEnumerable from a database query, and whose caller applies a Where. That filter runs in memory.

LINQ in Real Code

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
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}");
}

record Score(string Player, int Points); // type declarations follow top-level statements

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 — EF can't translate a local method in Where, so this throws at runtime.
var matches = db.Users
    .Where(u => MyHelper(u.Name)) // throws "could not be translated"
    .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.

✅

Check it: Log the SQL for one filtered query and look for your filter in the WHERE clause. If it isn't there, the filter ran in C#.

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.

✅

Check it: Call a large export and watch when the first bytes arrive. With streaming, they come before the query has finished.

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 only the columns you need
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);
}

How LINQ Goes Wrong

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. OrderBy doesn'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 has limits. EF Core can translate GroupBy followed by an aggregate (.Select(g => new { g.Key, Count = g.Count() })), and since EF Core 7 GroupBy can also be the final operator, returning the rows in each group. More complex shapes can still fail to translate, so check the SQL. CountBy and AggregateBy are for in-memory collections: don't expect EF Core to translate them.

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.

✅

Check it: Turn on SQL logging and load one page. A statement that appears more than once is a query being enumerated again.

Rules I Follow

  • For in-memory collections on .NET 9 and later, 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.

Try It on Your Own System This Week

Three checks on code you already have, about an hour in total:

  1. Search for ToList() followed by Where. Everything after the ToList runs in memory.
  2. Search request handlers for synchronous ToList, First and Count on database queries. Each one blocks a thread.
  3. Search for Count() > 0. Any() stops at the first match.

What's New Worth Using

LINQ keeps gaining small, useful methods, and they remove some long-standing awkward patterns.

CountBy and AggregateBy, from .NET 9, avoid the allocation of building groups you only wanted to count. .NET 10 added LeftJoin and RightJoin, ending the GroupJoin plus SelectMany plus DefaultIfEmpty ritual that nobody wrote correctly from memory, and EF Core 10 translates them to SQL:

var ordersPerCustomer = orders.CountBy(o => o.CustomerId);

var rows = customers.LeftJoin(
    orders,
    c => c.Id,
    o => o.CustomerId,
    (customer, order) => new { customer.Name, Total = order == null ? 0 : order.Total });

Use Index() from .NET 9 rather than Select((x, i) => ...) when you need the position. Chunk, MaxBy and DistinctBy have been around since .NET 6, and they're still underused.

Performance in hot paths

LINQ allocates enumerators and delegates, and in most code that doesn't matter. It matters in a tight loop that runs a million times a second. Measure before you rewrite anything, because recent .NET versions have made LINQ much faster, and many old benchmarks no longer hold. If a profile really does point at LINQ, a foreach over a span is the usual fix.

A few habits help everywhere. Any() is better than Count() > 0, since it stops at the first match. Call ToList() once, on purpose, when you need to reuse a result. Enumerating the same query twice runs it twice, and against a database that's two round trips. Avoid .ToList() in the middle of an EF Core query, because everything after it runs in memory, after you've fetched all the rows.

Know what EF Core can translate

A LINQ expression that compiles is not guaranteed to run in the database. Since EF Core 3, an expression it can't translate throws, rather than silently fetching everything, which is a good thing. Log the generated SQL in development, and read it for anything on a hot path. A clean-looking query can produce a very expensive plan, and you'll only see that in the SQL.