5 SQL Optimization Tips Every C# Developer Must Know

TL;DR: Always use AsNoTracking() for read-only queries. Avoid the N+1 problem by using .Include(). Beware of implicit client-side evaluation. Use pagination for large datasets, and never run SaveChanges() inside a massive foreach loop.

Database bottlenecks are the silent killer of application performance. A query that takes 10 milliseconds on your local machine with 50 rows of test data can easily take 10 seconds in production with 5 million rows.

As C# developers, it's easy to treat Entity Framework Core as a black box that just "handles" the database. But writing efficient LINQ is a skill you must master. Here are 5 optimizations you can implement today.

1. The Power of AsNoTracking()

When you query data using EF Core, it attaches a "Change Tracker" to the resulting objects. This is how EF Core knows what to update when you call SaveChanges().

However, if you are just reading data to display it on a webpage (and have no intention of updating it), that Change Tracker is wasting massive amounts of memory and CPU.

❌ The Bad Way

// EF Core allocates memory to track changes on all 10,000 products.
var products = await _context.Products.ToListAsync();
return products;

✅ The Good Way

// Disables the change tracker. Uses drastically less memory and runs faster.
var products = await _context.Products.AsNoTracking().ToListAsync();
return products;

2. Destroying the N+1 Problem

The N+1 problem occurs when you fetch a list of entities (1 query), and then loop through them to fetch their related entities (N queries). This annihilates database performance.

❌ The Bad Way

var users = await _context.Users.ToListAsync(); // Query 1

foreach (var user in users)
{
    // If you have 500 users, this executes 500 additional queries!
    var orders = await _context.Orders.Where(o => o.UserId == user.Id).ToListAsync();
}

✅ The Good Way

// Executes exactly ONE query with a SQL JOIN.
var usersWithOrders = await _context.Users
    .Include(u => u.Orders)
    .ToListAsync();

3. Stop Fetching the Whole Table

Never pull an entire table into memory just to count it or filter it in C#. You want the SQL Server to do the heavy lifting.

❌ The Bad Way

// Pulls 1,000,000 rows into RAM, then C# counts them.
var users = await _context.Users.ToListAsync();
var count = users.Count(); 

✅ The Good Way

// Translates directly to `SELECT COUNT(*) FROM Users`. Sends 1 integer back to C#.
var count = await _context.Users.CountAsync();

4. Batch Operations (Don't Loop SaveChanges)

Calling SaveChangesAsync() opens a database transaction, executes the query, and closes it. Doing this inside a loop is incredibly inefficient.

❌ The Bad Way

foreach (var product in productsToUpdate)
{
    product.Price *= 1.1m;
    await _context.SaveChangesAsync(); // Hitting the database 1,000 times!
}

✅ The Good Way

foreach (var product in productsToUpdate)
{
    product.Price *= 1.1m;
}
// Hit the database exactly once!
await _context.SaveChangesAsync(); 

Pro Tip: For .NET 7+, use ExecuteUpdateAsync() to update thousands of rows with a single SQL command without even pulling them into memory!

5. Select Only What You Need (Projections)

If your User table has 50 columns, but your API only needs the Id and Name, stop fetching the other 48 columns!

❌ The Bad Way

// SELECT * FROM Users
var users = await _context.Users.ToListAsync();

✅ The Good Way

// SELECT Id, Name FROM Users
var userDtos = await _context.Users
    .Select(u => new UserDto { Id = u.Id, Name = u.Name })
    .ToListAsync();

Summary

Entity Framework Core is remarkably powerful, but it relies on you to guide it. Always inspect the generated SQL (using logging or tools like MiniProfiler) to ensure your C# LINQ statements are translating into optimal queries!