LINQ (Language Integrated Query) is a cornerstone of modern C# development, providing a clean, type-safe way to query data. However, when working with large datasets, such as those common in industries like insurance, telecommunications, or financial services, performance becomes critical. Poorly optimized LINQ queries can lead to excessive memory usage, slow response times, and even application failures under load.

In this article, we'll explore five performance-critical LINQ patterns using a domain model representative of high-volume data scenarios: customer accounts with transaction histories. We'll show how to optimize queries to handle millions of records efficiently.

Used Domain Model

To demonstrate these patterns, we'll use a domain model that reflects a typical high-volume data scenario:

public class CustomerAccount
{
    public int Id { get; set; }
    public string CustomerId { get; set; } 
    public string AccountNumber { get; set; }
    public DateTime OpenDate { get; set; }
    public DateTime? CloseDate { get; set; }
    public decimal CurrentBalance { get; set; }
    public AccountStatus Status { get; set; }
    public ICollection<Transaction> Transactions { get; set; }
}

public class Transaction
{
    public int Id { get; set; }
    public int AccountId { get; set; }
    public DateTime Timestamp { get; set; }
    public TransactionType Type { get; set; }
    public decimal Amount { get; set; }
    public string Reference { get; set; }
    public TransactionStatus Status { get; set; }
}

public enum AccountStatus
{
    Active,
    Closed,
    Frozen,
    PendingClosure
}

public enum TransactionType
{
    Deposit,
    Withdrawal,
    Transfer,
    Fee,
    Interest
}

public enum TransactionStatus
{
    Pending,
    Completed,
    Failed,
    Reversed
}

1. Strategic Materialization - Know When to Call ToList()

LINQ queries are deferred by default; execution happens when you enumerate the result.
Materializing a collection prematurely (e.g., with ToList()) can be a performance hit if the result is never fully consumed.

Good practice

// We need all active accounts to display in a grid
var activeAccounts = db.CustomerAccounts
                       .Where(a => a.Status == AccountStatus.Active)
                       .ToList();   // Materialize once, before paging logic

// Subsequent operations are performed on the in‑memory list
var paged = activeAccounts.Skip((page - 1) * pageSize).Take(pageSize);

Bad practice

// Materializing inside a loop – thousands of round trips
foreach (var acct in db.CustomerAccounts
                         .Where(a => a.Status == AccountStatus.Active))
{
    var transactions = acct.Transactions.ToList(); // N+1 query problem
}

Takeaway: Materialize only when you need a concrete collection, and do it once.

2. Project Before You Materialize - Select Only What You Need

Fetching more data than necessary inflates the result set and slows down both the database and the network.

// Project only the fields we actually want
var accountSummaries = db.CustomerAccounts
    .Where(a => a.Status == AccountStatus.Active)
    .Select(a => new
    {
        a.Id,
        a.AccountNumber,
        a.CurrentBalance,
        OpenMonths = EF.Functions.DateDiffMonth(a.OpenDate, DateTime.UtcNow)
    })
    .ToList(); // Materialize after projection

Here we send only a handful of columns to the database instead of the entire entity graph.

Bonus tip: If you’re using EF Core, the Select projection can be translated into a single SQL query, eliminating the need to materialize the full entity first.

3. Avoid the N+1 Problem with Eager Loading

The N+1 problem occurs when an outer query pulls one entity and then, for each entity, fires another query to fetch related data. In the customer/account scenario, loading each account’s transactions lazily can lead to dozens of round trips.

// Eager load transactions for a handful of accounts
var accountsWithTx = db.CustomerAccounts
    .Where(a => a.Status == AccountStatus.Active)
    .Include(a => a.Transactions)
    .Take(10)           // Keep the batch small
    .ToList();          // One round trip, all data

// Now we can iterate without extra queries
foreach (var acct in accountsWithTx)
{
    Console.WriteLine($"{acct.AccountNumber}: {acct.Transactions.Count} transactions");
}

If you only need a subset of the transactions (e.g., the last 5), combine Take inside the Include:

.Include(a => a.Transactions.OrderByDescending(t => t.Timestamp).Take(5))

4. Stream Large Datasets with IAsyncEnumerable

When working with massive result sets, pulling everything into memory defeats the purpose of LINQ. Use IAsyncEnumerable to stream results row by row.

public async Task ProcessLargeTransactionsAsync()
{
    await foreach (var tx in db.Transactions
        .Where(t => t.Type == TransactionType.Withdrawal && t.Status == TransactionStatus.Completed)
        .OrderBy(t => t.Timestamp)
        .AsAsyncEnumerable())
    {
        // Process each transaction as it arrives
        // No large in‑memory collection is built
        Console.WriteLine($"Processed {tx.Id} at {tx.Timestamp}");
    }
}

This pattern is especially useful for reporting or batch jobs where you can process records incrementally.

5. Compiled Queries for Hot Code Paths

When the same query is executed many times (e.g., in a service loop or a UI refresh), compiling it once can shave milliseconds off each call.

private static readonly Func<AppDbContext, int, Task<List<Transaction>>> 
    _getTransactionsByAccountId = EF.CompileAsyncQuery(
        (AppDbContext ctx, int accountId) =>
            ctx.Transactions
               .Where(t => t.AccountId == accountId && t.Status == TransactionStatus.Completed)
               .OrderByDescending(t => t.Timestamp)
               .Take(100)
    );

public async Task<List<Transaction>> GetRecentTransactionsAsync(int accountId)
{
    return await _getTransactionsByAccountId(_dbContext, accountId);
}

The compiled query eliminates the need for EF to parse and plan the SQL each time.
When to use it: Repeated, identical queries that don’t change parameters or shape.
When not to use it: One‑off queries or those that evolve frequently.

Checklist

Common Mistakes to Avoid

Materializing inside a loop

// BAD: Executes a separate query for every account
foreach (var acctId in accountIds)
{
    var recentTx = dbContext.Transactions
        .Where(t => t.AccountId == acctId)
        .OrderByDescending(t => t.Timestamp)
        .Take(5)
        .ToList();   // One round‑trip per account
}

// GOOD: Pull all needed transactions in a single query
var recentTx = dbContext.Transactions
    .Where(t => accountIds.Contains(t.AccountId))
    .OrderByDescending(t => t.Timestamp)
    .Take(5)
    .ToList();   // One round‑trip, all data

Mixing IEnumerable and IQueryable

// BAD: Forces in‑memory processing after AsEnumerable()
var activeAccounts = dbContext.CustomerAccounts
    .AsEnumerable()          // Everything after this runs in C# memory
    .Where(a => a.Status == AccountStatus.Active)   // No SQL WHERE clause
    .ToList();

// GOOD: Keep the query expression tree until materialization
var activeAccounts = dbContext.CustomerAccounts
    .Where(a => a.Status == AccountStatus.Active)   // Translated to SQL
    .ToList();

Over‑eager loading

// BAD: Loads every transaction, its type, status, and even related audit data
var account = dbContext.CustomerAccounts
    .Include(a => a.Transactions)                 // Entire transaction graph
        .ThenInclude(t => t.Status)               // Hypothetical navigation
    .FirstOrDefault(a => a.Id == id);
// Might end up pulling thousands of rows if the account has a long history

// GOOD: Load only the data you really need
var account = dbContext.CustomerAccounts
    .Include(a => a.Transactions
        .Where(t => t.Status == TransactionStatus.Completed))  // Filtered include
    .FirstOrDefault(a => a.Id == id);

Tip: When you find yourself nesting Include calls that pull in more than 10–20 columns, pause and ask, “Do I really need every field?”

This small change can cut query size by orders of magnitude and keep your UI snappy.

Conclusion

LINQ is a powerful abstraction, but like any abstraction, it hides cost.  By consciously applying the five patterns above, you can keep query performance in check while still enjoying the expressive clarity that LINQ offers.  These 5 patterns aren’t complex; they’re about understanding data flow and making intentional decisions about when and how to execute queries.