EF Core read queries: return less, inspect more

A list screen usually needs a small set of columns. Loading a complete entity graph for every row gives the application more data to move, allocate and process before it can display anything.

Before adding a cache, inspect the query and the amount of data it returns.

Project the fields the screen uses

Assume db is an EF Core context with an Invoices set, and cancellationToken comes from the calling operation. This example is a read-only list, not a billing calculation.

using System.Linq;
using Microsoft.EntityFrameworkCore;

var rows = await db.Invoices
    .Where(invoice => invoice.AccountId == accountId)
    .OrderByDescending(invoice => invoice.CreatedAt)
    .ThenByDescending(invoice => invoice.Id)
    .Select(invoice => new
    {
        invoice.Id,
        invoice.Number,
        invoice.CreatedAt,
        invoice.Total
    })
    .Take(50)
    .ToListAsync(cancellationToken);

Filtering, ordering and projection stay in the query before materialization. The ID provides a tie-breaker for equal creation timestamps. The limit prevents a growing table from silently becoming a growing response.

This is the first page only. If users need the next page, implement pagination rather than increasing the limit indefinitely. A stable order helps, but records can still change between requests; choose offset or keyset pagination according to the actual navigation requirement.

Understand what is tracked

The projection above contains scalar values and no entity instances, so there are no entities to track. Adding AsNoTracking() here is unnecessary.

When returning entity instances for a read-only operation, AsNoTracking() can avoid change-tracking work. If code needs to update those entities, keep the intended tracking behavior clear. A projection that includes an entity can still track that entity.

Inspect the database work

Measure query duration and result size with representative data. Inspect generated SQL and the database execution plan before deciding which index to add. A filter and sort in C# do not guarantee that the database can execute them cheaply.

Also look for a query executed once per displayed row. Accessing lazy-loaded relationships inside a loop can turn one list into many round trips. Projection can often fetch the required fields in the original query.

Keep sensitive values out of diagnostic logs. Query timing and shape are useful without copying customer records into a log file.

These examples use EF Core. EF6 and raw ADO.NET have different APIs. See Microsoft's efficient querying and tracking guidance.