SQL Server

I once inherited a dashboard page that took over five seconds to load. The API endpoint behind it looked innocent enough, but digging in, I found a classic case of death by a thousand cuts: lazy loading, bloated entities, and chatty database calls. It was a textbook example of default EF Core behavior backfiring under real-world load.

After fixing that mess (and many others like it), I’ve developed a small playbook of go-to optimizations. These aren’t wild, complex tricks. They’re five fundamental patterns that I apply to almost every high-traffic ASP.NET Core project.

Here’s what I do to keep my data layers fast and lean.

1. Ditch Full Entities, Project to DTOs

This one is my golden rule for read queries. If you’re just displaying data, stop loading full-blown EF Core entities.

The problem is that when you pull an entity, EF Core has to hydrate every single property. Even the ones you don’t need. This results in bigger SQL queries that join more tables and pull back way more data than your API client will ever see.

It’s pure waste.

Instead, use Select to project directly into a Data Transfer Object (DTO).

// The slow way
var users = await _context.Users
    .Include(u => u.Profile) // Pulls everything from two tables
    .ToListAsync();

// The fast, clean way
var users = await _context.Users
    .Select(u => new UserSummaryDto 
    {
        Id = u.Id,
        Name = u.Name,
        Email = u.Email
    })
    .ToListAsync();

On a …

Last month, our customer portal started timing out. Users browsing past page 1,000 of their order history were met with a dreaded loading spinner that never went away. A quick look at the logs confirmed it: our classic offset-based pagination was hitting a table with 2.3 million orders, and query times had jumped from 50ms to over 4 seconds. SQL Server was burning up CPU cycles.

The culprit was Skip() and Take(). This one has burned me in production more than once. The fix was switching to keyset pagination (sometimes called cursor-based pagination), a technique that uses the last seen value to find the next page instead of counting rows to skip.

Here’s how it works and how to implement it properly in EF Core.

Why Skip() and Take() Grind to a Halt

Standard pagination in LINQ uses Skip(page * pageSize).Take(pageSize). Under the hood, this translates to SQL’s OFFSET and FETCH NEXT. For SQL Server to skip 50,000 rows, it has to actually read all 50,000 rows first.

Here’s the generated SQL for fetching page 2,501 (with a page size of 20):

-- This gets painfully slow as the offset increases
SELECT * FROM Orders 
ORDER BY OrderId 
OFFSET 50000 ROWS FETCH NEXT 20 ROWS ONLY;

Even with a perfect index on OrderId, the database still has to scan 50,020 rows from the index just to return the final 20. It’s an enormous amount of wasted work that gets worse with every page you click.

The Fix: Keyset Pagination (The “Seek Method”)

Instead of telling the …

I’ve been there. You’re staring at a dashboard in your multi-tenant app, and it’s crawling. The culprit is a query filtering across tens of thousands of records. Your first instinct? “LINQ is too slow. I’ll just write the raw SQL myself.”

It’s a tempting thought. But dropping down to raw SQL isn’t a simple performance switch. Get it wrong, and you could introduce a security hole or, ironically, make performance even worse. This exact scenario burned me once in production, and the fix wasn’t what I expected.

Let’s break down when to stick with LINQ and when (and how) to safely write your own SQL in EF Core.

First, The Big One: Security

If you take one thing away from this post, let it be this: never concatenate user input into a SQL string. SQL injection isn’t a textbook problem; it’s a real threat that I’ve seen take down production systems.

It’s easy to make this mistake, especially with C#’s slick string interpolation. This looks safe, right?

// DANGER: NEVER DO THIS.
var userId = Request.Query["userId"]; // e.g., "123"
var sql = $"SELECT * FROM Users WHERE Id = {userId}";
var users = await context.Users.FromSqlRaw(sql).ToListAsync();

This code is a wide-open door. An attacker can send 123; DROP TABLE Users; -- as the userId, and boom, your user table is gone.

The critical gotcha is that standard C# string interpolation ($"...") does not protect you. It just …