Optimizing LINQ for Performance: Beyond the Basics

Optimizing LINQ for Performance: Beyond the Basics

LINQ makes C# code expressive, but it also makes it easy to write accidentally expensive queries — especially when mixing LINQ-to-Objects and LINQ-to-Entities (EF Core). In many projects, the problem isn’t LINQ itself, but when the query runs and how many times it runs.

1. Deferred vs Immediate Execution

Deferred execution means the query isn’t executed when you *define* it — only when you *enumerate* it.

var query = customers.Where(c => c.IsActive); // not executed yet

foreach (var c in query)                      // executed here
{
    Console.WriteLine(c.Name);
}

This is great… until you enumerate more than once.

The problem

var activeCustomers = customers.Where(c => c.IsActive);

var count = activeCustomers.Count();          // 1st execution
var first = activeCustomers.FirstOrDefault(); // 2nd execution

var activeCustomers = customers.Where(c => c.IsActive).ToList();

var count = activeCustomers.Count;             // no re-query
var first = activeCustomers.FirstOrDefault();  // no re-query

2. Real Metrics: Deferred vs Immediate

3. Hidden Multiple Enumerations

This one bites senior devs too. You create a helper that takes `IEnumerable` and you iterate it twice.

public decimal GetTotal(IEnumerable orders)
{
    // 1st enumeration
    var valid = orders.Where(o => !o.IsCancelled);

    // 2nd enumeration
    return valid.Sum(o => o.Total);
}

public decimal GetTotal(IEnumerable orders)
{
    var valid = orders
        .Where(o => !o.IsCancelled)
        .ToList(); // materialize once

    return valid.Sum(o => o.Total);
}

4. Project Only What You Need

A very common LINQ performance issue in EF Core is pulling **entire entities** when you only need 2 columns.

Bad:

var result = await _context.Orders
    .Where(o => o.CreatedAt >= from && o.CreatedAt  o.CreatedAt >= from && o.CreatedAt  new { o.Id, o.Total })
    .ToListAsync();

5. Avoid Client-Side Evaluation (EF Core)

Sometimes LINQ expressions can’t be translated to SQL. EF Core then either throws or (older versions) switch to client-side evaluation.
Bad pattern:

var result = await _context.Orders
    .Where(o => MyCustomFilter(o))  // not translatable to SQL
    .ToListAsync();

That will load more rows than needed.

✅ Move the filter into SQL (pure expression) or materialize first intentionally:

var query = _context.Orders
    .Where(o => o.Status == OrderStatus.Paid); // SQL

var list = await query.ToListAsync();          // materialize

var filtered = list.Where(o => MyCustomFilter(o)); // in memory

This way you know exactly where the “slow” part happens.

6. Example Benchmark Code

You can show your team how to measure this with `Stopwatch`:

using System.Diagnostics;

// generate data
var customers = Enumerable.Range(1, 100_000)
    .Select(i => new Customer { Id = i, Name = "C" + i, IsActive = i % 2 == 0 })
    .ToList();

// deferred
var sw = Stopwatch.StartNew();
var query = customers.Where(c => c.IsActive);
var count1 = query.Count();
var first1 = query.FirstOrDefault(); // Use FirstOrDefault() to avoid exceptions
sw.Stop();
Console.WriteLine($"Deferred (2x enum): {sw.ElapsedMilliseconds} ms");

// materialized
sw.Restart();
var materialized = customers.Where(c => c.IsActive).ToList();
var count2 = materialized.Count;
var first2 = materialized.FirstOrDefault(); // Use FirstOrDefault() to avoid exceptions
sw.Stop();
Console.WriteLine($"Materialized: {sw.ElapsedMilliseconds} ms");

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public bool IsActive { get; set; }
}

You can run this once and paste the numbers into your article (like the tables above).

7. Quick Checklist for “Fast LINQ”

// ❌ Bad: Materializes too early
var list = db.Orders.Where(o => o.IsActive).ToList();
var filtered = list.Where(o => o.Total > 100).ToList(); // filtering in memory

// ✅ Good: Keep deferred until final result
var filtered = db.Orders
    .Where(o => o.IsActive)
    .Where(o => o.Total > 100)
    .ToList(); // single SQL query

Materialize at the **boundary** — where you need the final result or pass it to another system.

8. Entity Framework Core Performance Tuning: Tracking vs No-Tracking Queries

// Default: tracking query
var order = await _context.Orders.FirstAsync(o => o.Id == 123);
order.Status = OrderStatus.Shipped;
await _context.SaveChangesAsync(); // EF detects the change automatically

// No-tracking: ~30-40% faster for large result sets
var orders = await _context.Orders
    .AsNoTracking()
    .Where(o => o.CreatedAt >= startDate)
    .ToListAsync();

public class AppDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
    }
}

var order = await _context.Orders
    .AsTracking() // explicitly enable tracking
    .FirstAsync(o => o.Id == 123);

Best Practices

Combine with projection for maximum performance:

// Best: no-tracking + select only needed columns
var result = await _context.Orders
    .AsNoTracking()
    .Where(o => o.Status == OrderStatus.Pending)
    .Select(o => new OrderDto { Id = o.Id, Total = o.Total })
    .ToListAsync();

9. Conclusion