Fix Slow EF Core Queries with Projections

I’ve seen this happen a dozen times. An API endpoint is dog-slow, taking 2-3 seconds to respond. Everyone blames the network or the database. We check the query execution plan, and it’s fine—the database returns data in 50 milliseconds. So what’s the problem?

The problem is the 900KB of data we’re pulling from the database, serializing, and then throwing away for every single request. The culprit is a silent performance killer in Entity Framework Core: fetching full entities when you only need a few properties.

The Default Behavior is a Trap

It’s easy to write code like this. It’s clean, it’s simple, and it works.

var orders = await _dbContext.Orders
    .Where(o => o.Status == "Active")
    .ToListAsync();

But under the hood, EF Core is trying to be helpful by generating a SELECT * style query. If your Orders table has a few large NVARCHAR(MAX) columns for notes, addresses, or serialized metadata, you’re in for a bad time.

Here’s the SQL it probably generates:

SELECT [o].[Id], [o].[CustomerId], [o].[OrderDate], [o].[Status], 
       [o].[Notes], [o].[ShippingAddress], [o].[BillingAddress],
       [o].[InternalComments], [o].[AuditData], [o].[SerializedMetadata]
FROM [Orders] AS [o]
WHERE [o].[Status] = 'Active'

If your API only needs to display the order ID, customer name, and total, you just paid a heavy price in network I/O and memory allocation for nothing. That SerializedMetadata column alone could be megabytes.

The Fix: Project Only What You Need

The solution is to tell EF Core exactly what data you need using a projection. By using .Select() to shape the data into a Data Transfer Object (DTO), you take control of the generated SQL.

Let’s define a simple DTO for our API response:

public record OrderSummaryDto(
    int Id,
    string CustomerName,
    decimal TotalAmount,
    DateTime OrderDate
);

Now, let’s rewrite the query to use it:

public async Task<List<OrderSummaryDto>> GetActiveOrderSummariesAsync()
{
    return await _dbContext.Orders
        .Where(o => o.Status == "Active")
        .Select(o => new OrderSummaryDto(
            o.Id,
            o.Customer.Name, // We can even pull from related tables
            o.TotalAmount,
            o.OrderDate
        ))
        .AsNoTracking() // A must-have for read-only queries
        .ToListAsync();
}

The SQL generated by this is beautiful. It’s lean, targeted, and only touches the columns it absolutely needs to.

SELECT [o].[Id], [c].[Name] AS [CustomerName], 
       [o].[TotalAmount], [o].[OrderDate]
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
WHERE [o].[Status] = 'Active'

We went from pulling ten columns to four and added a necessary JOIN that EF Core figured out for us. No more over-fetching.

The Real-World Impact: Benchmark Results

Talk is cheap. I ran these patterns against a database with 10,000 orders, each with large text fields to simulate a real production table. The results are eye-opening.

There are a few weird things in those results that are worth pointing out.

The Fastest: SelectSpecificColumns

This approach was a surprise winner. Instead of projecting to a DTO, we project to a new Order entity, but only populate the fields we need.

// The fastest method in the benchmark (11.76ms)
var orders = await _context.Orders
    .Where(o => o.Status == "Active")
    .Select(o => new Order
    {
        Id = o.Id,
        TotalAmount = o.TotalAmount,
        OrderDate = o.OrderDate,
        // We skip all the big string properties!
    })
    .AsNoTracking()
    .ToListAsync();

This is lightning fast because it results in a simple SELECT on a single table with no JOINs and avoids pulling the large text columns.

The Slowest: The Production Nightmare

The ComplexProjection benchmark simulates a common mistake: trying to build a deeply nested object graph in a single query.

// This query is a performance bomb (436ms)
var details = await _context.Orders
    .Take(100) // Even with a Take(100), it's slow
    .Select(o => new OrderDetailsDto
    {
        Id = o.Id,
        CustomerEmail = o.Customer.Email,
        Items = o.Items.Select(i => new OrderItemDto
        {
            ProductName = i.Product.Name,
            // Imagine more nesting here...
        }).ToList()
    })
    .ToListAsync();

This looks innocent, but it generates a massive SQL query with multiple LEFT JOINs that can lead to a Cartesian explosion, blowing up your memory usage (154 MB!) and slowing the query to a crawl. I’ve seen queries like this bring production servers to their knees.

Here’s My Rule of Thumb

After getting burned by this a few times, I follow a simple pattern.

1. For Write Operations: Use Full Entities

If you need to update or delete an entity, you need EF Core’s change tracking. Fetch the full, tracked entity.

// Good: We need the full entity to update it
var order = await _dbContext.Orders
    .Include(o => o.Items)
    .FirstAsync(o => o.Id == orderId);

order.Status = "Shipped"; // Make a change
await _dbContext.SaveChangesAsync(); // Save it

2. For Read Operations: Use Projections

If you’re just reading data to display in an API or a report, always use a projection to a DTO or an anonymous type. And always add .AsNoTracking().

// Good: Read-only query for an API
var summaries = await _dbContext.Orders
    .Where(o => o.Status == "Active")
    .Select(o => new OrderSummaryDto(...))
    .AsNoTracking()
    .ToListAsync();

This separation is clean and keeps your read path highly optimized while your write path maintains the consistency you need.

The Final Takeaway

Here’s when I use projections and when I’m careful.

Use Projections When:

• You’re building a GET endpoint in an API.
• You’re creating a report or dashboard.
• You only need a subset of columns from a table.
• You need data from related tables but don’t want the entire object graph.

Avoid (or Be Careful With) Projections When:

• You need to update or delete the data you’re fetching. Just get the full entity.
• Your projection gets deeply nested (more than one or two levels of navigation properties). The generated SQL can become monstrous. Always log and review the SQL for complex projections before they hit production.

Stop letting your ORM fetch the entire database behind your back. A simple .Select() is often the difference between an endpoint that scales and one that falls over.

References

• Microsoft Docs: Query Data with EF Core
• Microsoft Docs: Tracking vs. No-Tracking Queries
• Microsoft Docs: Loading Related Data

FAQ