Before You Cache That .NET Endpoint, Inspect the Query

A slow endpoint can make Redis look like the obvious next step.

Put the result in a cache. Avoid another database call. Return the response faster.

That can be a sensible approach. But before introducing another component, I would ask a more basic question:

Is the endpoint doing unnecessary work?

With EF Core, a query can look perfectly reasonable in C# while retrieving too many columns, loading too many rows, or making more database trips than expected. Microsoft recommends inspecting generated SQL and database round trips when investigating performance.

Let’s look at a few places worth checking before reaching for a cache.

1. Fetch the response, not the entire entity

Imagine a customer directory that displays only an identifier and a name.

This query retrieves customer entities:

var customers = await db.Customers
    .OrderBy(c => c.Id)
    .Take(20)
    .ToListAsync(cancellationToken);

If the entity contains many other mapped properties, the query may retrieve columns the response never uses.

Instead, describe the response directly:

public sealed record CustomerSummary(
    int Id,
    string Name);

var customers = await db.Customers
    .OrderBy(c => c.Id)
    .Select(c => new CustomerSummary(
        c.Id,
        c.Name))
    .Take(20)
    .ToListAsync(cancellationToken);

This is projection: selecting the shape of data you need rather than loading an entire entity. Microsoft recommends using Select to avoid retrieving unnecessary columns.

There is also a useful tracking detail here. This projection contains scalar values inside a response record, not entity instances. EF Core does not track entities when the result contains no entity instances. Adding AsNoTracking() is therefore unnecessary for this particular result shape.

2. Keep filtering and limiting in the database query

Compare these two examples.

First:

var customers = await db.Customers
    .ToListAsync(cancellationToken);

var activeCustomers = customers
    .Where(c => c.IsActive)
    .OrderBy(c => c.Id)
    .Take(20)
    .ToList();

Then:

var activeCustomers = await db.Customers
    .Where(c => c.IsActive)
    .OrderBy(c => c.Id)
    .Select(c => new CustomerSummary(
        c.Id,
        c.Name))
    .Take(20)
    .ToListAsync(cancellationToken);

In the first example, the application materialises all customers before filtering and limiting the result.

In the second, those operations remain part of the database query.

For a small development database, the difference may not feel important. But retrieving an unbounded result set makes the amount of work depend on how much the table grows. EF Core’s performance guidance recommends limiting result sizes rather than assuming the data will remain small.

The practical question is not just “Does this return 20 records?”

It is “How much data did we retrieve to produce those 20 records?”

3. Watch for queries hiding inside loops

Consider this illustrative example:

var customers = await db.Customers
    .OrderBy(c => c.Id)
    .Take(20)
    .ToListAsync(cancellationToken);

foreach (var customer in customers)
{
    Console.WriteLine(customer.Orders.Count);
}

If lazy loading is enabled and the orders have not already been loaded, accessing Orders can trigger another query for each customer.

That is the N+1 problem: one query retrieves the parent records, followed by additional queries for their related data. Microsoft warns that lazy loading can make these extra round trips easy to introduce accidentally.

If the response only needs an order count, express that requirement in the query:

public sealed record CustomerOrderSummary(
    int Id,
    string Name,
    int OrderCount);

var customers = await db.Customers
    .OrderBy(c => c.Id)
    .Select(c => new CustomerOrderSummary(
        c.Id,
        c.Name,
        c.Orders.Count()))
    .Take(20)
    .ToListAsync(cancellationToken);

For a supported relational translation, EF Core can calculate that count in SQL instead of loading each customer’s orders into application memory.

Inspect the generated SQL for your actual provider and query. Do not assume that a cleaner LINQ expression automatically means a cheaper execution plan.

4. Use tracking when you need it

If you are loading entities only to display them, change tracking may be unnecessary:

var customers = await db.Customers
    .AsNoTracking()
    .OrderBy(c => c.Id)
    .Take(20)
    .ToListAsync(cancellationToken);

AsNoTracking() avoids setting up normal change tracking for those entity instances. It is useful when you do not intend to modify them and persist those modifications through SaveChangesAsync().

But it is not a universal speed switch.

If the operation loads an entity, changes it, and saves it, tracking may be exactly what you need. Tracking also provides identity resolution, allowing repeated references to the same entity to share an instance. Ordinary queries using AsNoTracking() do not provide that behaviour.

Choose based on the operation, not a blanket rule.

5. Check what happens on later pages

Offset pagination is straightforward:

var customers = await db.Customers
    .OrderBy(c => c.Id)
    .Skip(offset)
    .Take(20)
    .Select(c => new CustomerSummary(
        c.Id,
        c.Name))
    .ToListAsync(cancellationToken);

But the database still has to process the skipped entries. As the offset grows, that work can become significant.

For a screen that only needs the next page, consider using the last identifier from the previous result:

var customers = await db.Customers
    .Where(c => c.Id > lastSeenId)
    .OrderBy(c => c.Id)
    .Take(20)
    .Select(c => new CustomerSummary(
        c.Id,
        c.Name))
    .ToListAsync(cancellationToken);

This is keyset pagination. With an appropriate index, it lets the database seek beyond the previous position rather than process an increasingly large offset.

The tradeoff matters: it does not directly support jumping to an arbitrary page number. Also, the ordering must be fully unique. If you sort by a timestamp, for example, include a unique identifier to distinguish records sharing that timestamp.

6. Measure before choosing the next tool

Before and after changing a query, compare:

1. Endpoint latency under representative load.
2. Database command count per request.
3. Rows and columns retrieved.
4. Query execution plans and index usage.
5. Application memory allocations where relevant.

EF Core’s documentation explicitly recommends benchmarking alternatives rather than assuming an optimisation will help your workload.

Caching may still be the right next step. An efficient query can remain expensive when it runs frequently enough.

But that is a stronger reason to introduce caching than discovering that the endpoint was loading thousands of records to return a small list.

Start by understanding the work.

Then decide which work to optimise, which to avoid, and which is worth caching.