Optimizing LINQ Queries for Performance

LINQ’s fluent syntax makes complex queries read like prose—until invisible SQL traps devour CPU and I/O. These best-practice patterns keep the elegance while hitting millisecond targets.

Real-Life Analogy: Barista Workflow

A skilled barista grinds beans once, steams milk once, then pours three lattes in a row. An amateur repeats grind-steam-pour for every cup. Great LINQ chains batch, filter early, and offload heavy work to the espresso machine (database).

Golden Rules

1. Filter Early – push Where() near the source so indexes work.
2. Project Narrowly – Select() needed columns only; avoid SELECT *.
3. Avoid N + 1 – use Include() or projection joins (Select anonymous objects).
4. Materialise Late – call ToList() once at the end, never mid-chain.
5. Let SQL Count – prefer .Any(), .Count() to client-side loops.

Common Anti-Patterns

• Multiple Include()s + Filtering After Materialisation – loads entire graphs.
• Client-Side Methods in Predicates – e.g., DateTime.IsWeekend() forces client evaluation.
• Unintended Cross-Joins – missing navigation filter creates Cartesian explosion.

Index-Friendly Techniques

• Use StartsWith() or EF.Functions.Like("abc%") instead of Contains() for large tables.
• Avoid ToLower()/ToUpper(); prefer case-insensitive collation.
• Convert DateTime comparisons to ranges (>= 2025-06-01 AND < 2025-07-01).

Compiled Queries

Heavy endpoints benefit from EF.CompileQuery (sync) or EF.CompileAsyncQuery (async); the expression tree translates only once.

static readonly Func<AppDbContext,string,int,Task<IReadOnlyList<ProductDto>>> _search =
    EF.CompileAsyncQuery(
        (AppDbContext ctx, string term, int top) =>
            ctx.Products
               .Where(p => p.Name.Contains(term))
               .OrderBy(p => p.Price)
               .Select(p => new ProductDto(p.Id, p.Name, p.Price))
               .Take(top));

Streaming vs Buffering

EF Core streams rows by default, but enumerating a result twice triggers a database round-trip each time. Cache results when reuse outweighs memory cost.

Pagination Pitfalls

• Skip()/Take() over deep pages performs poorly; prefer seek-based pagination (WHERE Id > @lastId).
• Never call Count() in a loop for total pages—compute once.

Bulk Updates & Deletes (EF Core 8)

// one round-trip, no materialisation
await ctx.Orders
         .Where(o => o.Status == OrderStatus.Abandoned &&
                     o.CreatedAt < DateTime.UtcNow.AddMonths(-1))
         .ExecuteDeleteAsync();

Measuring Success

• ToQueryString() ➜ inspect SQL for cartesian joins.
• SQL Profiler or pgBadger ➜ highlight slow queries.
• BenchmarkDotNet ➜ microbenchmark hot paths.

Final Thoughts

Elegant LINQ and efficient SQL are not mutually exclusive. Filter early, project narrow, batch cleverly, and profile continuously—you’ll serve queries as quickly as a seasoned barista knocking out flat whites.