LINQ Performance: The Tricks That Make Queries Scream

LINQ Performance: The Tricks That Make Queries Scream (And The Pitfalls That Make Them Crawl)

You've learned LINQ. Your queries work. But working isn't the same as fast. The difference between a 50ms query and a 5-second timeout is usually a handful of patterns the documentation doesn't emphasize.

Let's fix that.

The Materialization Cost

Every ToList(), ToArray(), ToDictionary() allocates memory and copies data. Chain multiple? You're copying multiple times:

// Bad — three allocations
var result = items
    .Where(x => x.IsActive)
    .ToList()               // Allocation 1
    .OrderBy(x => x.Name)
    .ToList()               // Allocation 2
    .Take(10)
    .ToList();              // Allocation 3

// Good — one allocation
var result = items
    .Where(x => x.IsActive)
    .OrderBy(x => x.Name)
    .Take(10)
    .ToList();              // Single allocation at the end

Materialize once, at the end, after all transformations.

Any() vs Count() > 0

Need to check if something exists? Any() stops at the first match:

// Bad — counts everything
if (products.Count() > 0) { }

// Good — stops at first item
if (products.Any()) { }

// With condition
if (products.Any(p => p.Price > 100)) { }  // Stops at first matching item

In SQL, Any() becomes EXISTS (stops early). Count() scans the entire table.

Fun fact: The performance difference scales with data size. On a table with 10 million rows where 1 exists, Any() returns immediately while Count() still scans all 10 million. I've seen this single change turn 30-second queries into 10ms responses.

First() vs Single() vs FirstOrDefault()

• First() — returns first, throws if empty
• FirstOrDefault() — returns first or default, no throw
• Single() — returns one, throws if empty OR if multiple exist
• SingleOrDefault() — returns one or default, throws if multiple exist

The crucial difference: Single() must verify no second element exists. That means fetching (or at least checking for) two rows.

// When you expect exactly one and want validation
var user = dbContext.Users.Single(u => u.Email == email);  // Throws if 0 or 2+

// When you want the first and don't care about duplicates
var user = dbContext.Users.First(u => u.Email == email);  // Faster, takes first

For database queries, First() generates TOP 1. Single() needs to verify uniqueness.

The Index Awareness Gap

LINQ doesn't know about your indexes. But your query shapes determine whether indexes are used:

// If Email is indexed, this is fast
var user = dbContext.Users.FirstOrDefault(u => u.Email == email);

// If Email is NOT indexed, this scans the table
var user = dbContext.Users.FirstOrDefault(u => u.Email == email);

Same LINQ, different performance based on database schema. Profile your actual queries. Check execution plans.

Computed Columns in WHERE

Wrapping a column in a function kills index usage:

// Bad — YEAR(OrderDate) prevents index use
var orders = dbContext.Orders
    .Where(o => o.OrderDate.Year == 2024)
    .ToList();

// Good — range query uses index
var start = new DateTime(2024, 1, 1);
var end = new DateTime(2025, 1, 1);
var orders = dbContext.Orders
    .Where(o => o.OrderDate >= start && o.OrderDate < end)
    .ToList();

The rewrite generates OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01', which can use an index on OrderDate.

The Tracking Overhead

By default, EF tracks every entity for change detection. For read-only queries, disable it:

// Default — entities are tracked
var products = dbContext.Products.ToList();

// Faster for read-only — no tracking overhead
var products = dbContext.Products.AsNoTracking().ToList();

AsNoTracking() skips the change tracker, reduces memory, and speeds up materialization. Use it whenever you're just displaying data.

For an entire context that's read-only:

dbContext.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

Batch vs Individual Operations

// Terrible — N database calls
foreach (var product in products)
{
    product.Price *= 1.1m;
    await dbContext.SaveChangesAsync();  // Save inside loop!
}

// Better — one database call
foreach (var product in products)
{
    product.Price *= 1.1m;
}
await dbContext.SaveChangesAsync();  // Save after loop

// Best for bulk — raw SQL, no entity loading
await dbContext.Database.ExecuteSqlRawAsync(
    "UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = {0}", categoryId);

Each SaveChanges is a database round-trip. Batch operations when possible. For massive updates, raw SQL avoids loading entities entirely.

The Pagination Pattern

// Bad — loads all, then slices
var allProducts = dbContext.Products.ToList();
var page = allProducts.Skip(100).Take(20);

// Good — SQL handles pagination
var page = dbContext.Products
    .OrderBy(p => p.Id)  // Required for consistent paging
    .Skip(100)
    .Take(20)
    .ToList();

Skip and Take on IQueryable translate to OFFSET/FETCH or LIMIT. On IEnumerable (after ToList), they filter in memory.

Always page in the database.

String Operations: The Hidden Cost

String methods have different database translations:

// These usually translate well
.Where(p => p.Name.Contains("phone"))     // LIKE '%phone%'
.Where(p => p.Name.StartsWith("Smart"))   // LIKE 'Smart%' — can use index
.Where(p => p.Name.EndsWith("Pro"))       // LIKE '%Pro' — no index

// This might not translate
.Where(p => p.Name.ToLower() == searchTerm.ToLower())  // May cause issues

StartsWith can use indexes. Contains and EndsWith generally can't. And string manipulation functions vary by database provider.

Compiled Queries for Hot Paths

If a query runs thousands of times, compile it once:

private static readonly Func<AppDbContext, int, Product?> GetProductById =
    EF.CompileQuery((AppDbContext db, int id) =>
        db.Products.FirstOrDefault(p => p.Id == id));

// Usage — no expression tree compilation overhead
var product = GetProductById(dbContext, 42);

Compiled queries skip the expression tree parsing on each call. For cold paths (run rarely), the overhead is negligible. For hot paths (called per-request), it adds up.

The Checklist

1. Materialize once — ToList() at the end, not mid-chain
2. Any() over Count() > 0 — stop at first match
3. First() over Single() — unless you need the uniqueness check
4. AsNoTracking() — for read-only queries
5. Paginate in database — Skip/Take on IQueryable
6. Avoid function wrappers in WHERE — kills index usage
7. Profile actual SQL — LINQ is an abstraction; the database decides performance

---

That wraps our LINQ series! We've covered deferred execution, IQueryable vs IEnumerable, projections, grouping, joins, the N+1 problem, async patterns, aggregates, raw SQL, and performance. Each of these concepts builds on the others to make you dangerous with LINQ.

The real learning happens when you apply these patterns to your own queries, profile the results, and see the database doing what you intended. Good luck out there!