LINQ Best Practices & Optimization Guide

Essential patterns and techniques for writing efficient LINQ queries in .NET 9

DO: Follow These Practices

1. Use AsNoTracking() for Read-Only Queries

// ✅ GOOD - 13-20% faster for read-only
var products = await _context.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

AsNoTracking() disables change tracking, reducing memory and improving performance for queries that don't need to update entities.

2. Use Async/Await

// ✅ GOOD - Non-blocking
var products = await _context.Products
    .Where(p => p.Price > 100)
    .ToListAsync();

// ❌ BAD - Blocks thread
var products = _context.Products
    .Where(p => p.Price > 100)
    .ToList();

3. Project Early with Select()

// ✅ GOOD - Fetch only needed columns
var summaries = await _context.Products
    .Where(p => p.IsActive)
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        Price = p.Price
    })
    .ToListAsync();

4. Filter Before Sorting

// ✅ GOOD - Filter first
var products = await _context.Products
    .Where(p => p.IsActive && p.Price > 100)
    .OrderBy(p => p.Name)
    .ToListAsync();

5. Use Pagination

// ✅ GOOD - Paginate large results
var products = await _context.Products
    .Where(p => p.IsActive)
    .OrderBy(p => p.Id)
    .Skip((page - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

6. Use Include() for Related Data

// ✅ GOOD - Eager loading (1 query)
var products = await _context.Products
    .Include(p => p.Category)
    .Include(p => p.Reviews)
    .Where(p => p.IsActive)
    .ToListAsync();

7. Add Indexes on Filtered/Sorted Columns

-- ✅ GOOD - Index frequently queried columns
CREATE INDEX IX_Products_Price 
    ON Products(Price);

CREATE INDEX IX_Products_Category_IsActive 
    ON Products(Category, IsActive);

8. Use IQueryable for Composable Queries

// ✅ GOOD - Build query incrementally
IQueryable<Product> query = _context.Products;

if (!string.IsNullOrEmpty(category))
    query = query.Where(p => p.Category == category);

if (minPrice.HasValue)
    query = query.Where(p => p.Price >= minPrice);

var results = await query.ToListAsync();

9. Use Any() Instead of Count() > 0

// ✅ GOOD - Stops at first match
if (await _context.Products.AnyAsync(p => p.Price > 1000))
{
    // ...
}

// ❌ BAD - Counts all matching records
if (await _context.Products.CountAsync(p => p.Price > 1000) > 0)
{
    // ...
}

10. Enable Query Logging in Development

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    
    if (builder.Environment.IsDevelopment())
    {
        options.EnableSensitiveDataLogging();
        options.LogTo(Console.WriteLine, LogLevel.Information);
    }
});

DON'T: Avoid These Anti-Patterns

1. Don't Load All Data Then Filter in Memory

// ❌ BAD - Loads everything into memory first
var allProducts = await _context.Products.ToListAsync();
var filtered = allProducts
    .Where(p => p.Price > 100)
    .ToList();

// ✅ GOOD - Filter in database
var filtered = await _context.Products
    .Where(p => p.Price > 100)
    .ToListAsync();

2. Don't Cause N+1 Problem

// ❌ BAD - N+1 queries
var products = await _context.Products.ToListAsync();
foreach (var product in products)
{
    // Additional query per product!
    var category = await _context.Categories
        .FirstAsync(c => c.Id == product.CategoryId);
}

// ✅ GOOD - Single query with Include
var products = await _context.Products
    .Include(p => p.Category)
    .ToListAsync();

3. Don't Use ToList() Too Early

// ❌ BAD - Executes immediately, then filters in memory
var products = await _context.Products.ToListAsync();
var filtered = products
    .Where(p => p.Price > 100)
    .OrderBy(p => p.Name)
    .Take(10);

// ✅ GOOD - All operations in database
var filtered = await _context.Products
    .Where(p => p.Price > 100)
    .OrderBy(p => p.Name)
    .Take(10)
    .ToListAsync();

4. Don't Select Full Entities When Partial Data Needed

// ❌ BAD - Fetches all columns
var products = await _context.Products
    .Where(p => p.IsActive)
    .ToListAsync();
var names = products.Select(p => p.Name).ToList();

// ✅ GOOD - Fetch only name
var names = await _context.Products
    .Where(p => p.IsActive)
    .Select(p => p.Name)
    .ToListAsync();

5. Don't Use Client Evaluation

// ❌ BAD - Method executed client-side
var products = await _context.Products
    .Where(p => IsExpensive(p.Price)) // Can't translate to SQL
    .ToListAsync();

// ✅ GOOD - Direct comparison in SQL
var products = await _context.Products
    .Where(p => p.Price > 500)
    .ToListAsync();

6. Don't Forget to Dispose DbContext

// ❌ BAD - Not using 'using' statement
var context = new AppDbContext();
var products = context.Products.ToList();
// Context not disposed!

// ✅ GOOD - Automatic disposal
using (var context = new AppDbContext())
{
    var products = context.Products.ToList();
} // Context disposed here

// ✅ EVEN BETTER - Dependency Injection
// Let DI container manage lifetime

7. Don't Use Multiple Queries When One Will Do

// ❌ BAD - Multiple queries
var categories = await _context.Products
    .Select(p => p.Category)
    .Distinct()
    .ToListAsync();
    
foreach (var cat in categories)
{
    var count = await _context.Products
        .CountAsync(p => p.Category == cat);
}

// ✅ GOOD - Single GroupBy query
var stats = await _context.Products
    .GroupBy(p => p.Category)
    .Select(g => new { 
        Category = g.Key, 
        Count = g.Count() 
    })
    .ToListAsync();

8. Don't Ignore Query Performance Warnings

// ❌ BAD - Ignoring warnings
#pragma warning disable EF1001
var products = await _context.Products
    .Where(p => SomeClientMethod(p))
    .ToListAsync();

// ✅ GOOD - Fix the warning
var products = await _context.Products
    .Where(p => p.Price > threshold)
    .ToListAsync();

Performance Optimization Checklist

Database Level

Add **indexes** on WHERE clauses
Add **indexes** on ORDER BY columns
Add **indexes** on JOIN columns
Use **composite indexes** when appropriate
Analyze query execution plans
Update statistics regularly

LINQ Query Level

Use **AsNoTracking()** for read-only
Project to DTOs with **Select()**
Use **Include()** for related data
**Filter before sorting**
Use **Any()** instead of Count() > 0
**Paginate** large result sets

Application Level

Use **async/await** everywhere
Enable **connection pooling**
Implement **caching** strategically
Use **compiled queries** for hot paths
Monitor with Application Insights
Profile in production-like env

Quick Reference

Scenario	Use
Read-only queries	`.AsNoTracking()`
Existence check	`.AnyAsync()`
Related data	`.Include()`
Partial data	`.Select()`
Large results	`.Skip().Take()`
Aggregations	`.GroupBy()`
Single result	`.FirstOrDefaultAsync()`
All results	`.ToListAsync()`

Performance Tips

Filter Early: Apply WHERE clauses before ORDER BY or TAKE

Project Smart: Use Select() to fetch only needed columns

Index Wisely: Index columns in WHERE, JOIN, and ORDER BY

Avoid N+1: Use Include() for related data to prevent multiple queries

Back to Demos