Optimizing EF Core Query Performance: A Checklist from SQL Translation and Projection to Keyset Pagination

EF Core query performance problems often do not mean that the database has "suddenly become slow." Instead, a query may have silently switched to in-memory execution, loaded unnecessary columns and relationships, or retrieved an entire collection before applying pagination and statistics. An effective optimization sequence first confirms the generated SQL and the volume of data involved, then addresses tracking, projection, pagination, indexes, and application-layer algorithms.

This article provides a reusable diagnostic framework without citing record counts, timings, or results from any specific project. The examples focus on query shape and decision principles. Before production deployment, validate them against the same database engine, a data distribution close to production, and representative parameters.

Why Do EF Core Queries Run in Memory?

When a query leaves the IQueryable pipeline too early, subsequent filtering may be performed row by row in the application, causing the database to return far more data than the screen requires.

Common transition points include AsEnumerable(), ToList(), and custom methods. These are not inherently wrong, but their position determines where subsequent operations execute. If the collection is materialized before Where, sorting, or pagination is applied, the database cannot translate those conditions into SQL.

Begin the diagnosis with three questions:

1. Do Where, Select, OrderBy, and pagination occur before or after materialization?
2. Can the current EF Core provider translate the methods used in the predicates?
3. Does the generated SQL contain the expected filters, ordering, and row limit?

Dynamic column requirements should not be implemented by using reflection to read each row directly. If a field is part of the model, construct the query with a translatable expression or an approach based on EF.Property, then validate it against a real database. Successful compilation proves only that the program's syntax is valid; it does not prove that the database provider can translate the expression.

Make "inspect the SQL" part of code review. Reviewers should check not only whether the LINQ is concise, but also whether the SQL columns, predicates, ordering, joins, and limits match the intent. If a query is called by a high-traffic endpoint, retain execution plans for representative parameters as well, so small development datasets do not lead to incorrect conclusions.

Why Should Read-Only Queries Use AsNoTracking?

Read-only scenarios do not require change tracking. Explicitly using AsNoTracking reduces entity snapshots and tracking-management work, makes the query's purpose clearer, and lowers unnecessary memory consumption.

Tracking is appropriate for a workflow that retrieves entities, modifies their properties, and then saves them through the same context. Lists, reports, exports, menus, and most query endpoints only read data and do not need every entity to enter the change tracker.

Do not treat AsNoTracking as a cure-all that hides over-fetching. If a query still reads many columns, complete relationships, and unnecessary rows, removing tracking improves only one part of the problem. The correct sequence is to reduce the result set and projection first, then choose the tracking mode.

A team can explicitly separate queries from commands in the data-access layer so read-only paths do not track by default and update paths deliberately opt into tracking. This is more consistent than relying on developers to remember the setting every time, but care is still required to ensure that a global default does not cause some update workflows to lose expected behavior.

How Can Projection Prevent Over-Fetching?

A projection should select only the columns actually needed by the screen or calculation. Let the database filter and aggregate first, then return a compact result instead of loading complete entities and discarding most of their data afterward.

If an interface needs only a name, status, and date, it should not load every column and multiple relationship levels merely for convenience. Using Select to project into a dedicated data model can reduce data transfer, materialization cost, and serialization overhead at the same time, while making the query's intent easier to review.

"Retrieve a list only to count it" is a classic antipattern. If the requirement is simply to count records that meet a condition, push Count, Any, Sum, or grouped aggregation down to the database. If a screen needs several independent statistics, first confirm how the database connection and context are used, then evaluate safe parallelism. Do not arbitrarily run multiple queries concurrently on the same context, which is not thread-safe.

Review projection design at the following levels:

• Rows: Can filters be applied earlier?
• Columns: Does the query select only what the output actually requires?
• Relationships: Can a single projection produce the result without loading the entire object graph?
• Aggregates: Can the database calculate them first and return a small result?
• Enrichment data: Can pagination happen first so only items on the current page are enriched?

Projection must also account for row inflation caused by multiple collection relationships. When one query joins multiple one-to-many collections, result rows may form repeated combinations. In that situation, compare split queries, staged reads, or a redesigned output model and measure them with the actual data distribution, rather than assuming that a single query is always faster.

How Should You Choose Between Keyset and Offset Pagination?

Offset pagination is suitable for interfaces that must jump to an arbitrary page. Keyset pagination is suitable for sequential navigation through large or continuously changing datasets, where performance and result stability are usually easier to control.

Offset pagination is commonly implemented by skipping a number of preceding rows and taking a fixed number of rows. On later pages, the database may need to scan or sort more data. When preceding rows are inserted or deleted, users may also see duplicate or missing items. Its advantages are conceptual simplicity and direct support for page numbers.

Keyset pagination instead uses the last observed sort key as the condition for the next page. For example, sort by creation time and an identifier, then retrieve rows whose key is "less than the last key on the previous page." This avoids repeatedly skipping a large number of preceding rows and is particularly suitable for activity feeds, event logs, and infinite scrolling.

Keyset pagination must satisfy all of the following design requirements:

1. Stable and unique ordering: A date that may contain duplicate values is insufficient by itself; add a unique field as the secondary sort key.
2. Consistent predicate direction: For descending order, the comparison used to retrieve the next page must match the sort direction.
3. An index that supports the ordering: The column order in a composite index must support the filtering and ordering shape.
4. A cursor derived from the original order: If a page is reordered in memory after retrieval, do not use the final displayed item directly as the cursor for the next page.
5. Paginate before enrichment: Retrieve names, statistics, or other enrichment data only for items on the current page.

Having a "pagination parameter" does not mean pagination actually occurs in the database. Confirm that the SQL contains the corresponding ordering, predicate, and row limit. If the application retrieves all data before slicing it, the interface may display only one page while the backend still pays the cost of a full read.

How Should You Diagnose an Index That Is Not Being Used?

Index diagnosis must begin with the actual query predicates and execution plan. Pay particular attention to functions applied to columns, cross-column OR conditions, type conversions, and composite-index order that may prevent efficient search access.

When a predicate first applies string replacement, date conversion, or a calculation to a data column, the database may be unable to seek directly through the original index. Instead of calculating the value on every query, consider storing the normalized result in an indexable column or performing normalization when data is written. The implementation depends on database capabilities; validate read and write costs as well as consistency before making the change.

An OR across multiple columns may also make it difficult for the optimizer to choose an effective path. Compare the following options:

• Let each predicate use an appropriate index, then combine the identifiers.
• Redesign a normalized, searchable column.
• Split searches with different purposes into explicit modes instead of making one endpoint handle every condition.
• Use database-supported full-text or specialized search capabilities, after first confirming consistency requirements.

A composite index is not simply a collection of every commonly used column. Column order must be arranged according to equality predicates, range predicates, and sorting. The same columns in a different order may support entirely different queries. Indexes also increase write and storage costs, so every new index should correspond to a specific query and execution plan.

Do not assess index value using a small development table. Data volume, value distribution, frequently used parameters, and cache state can all change the plan. Validation should include representative high-selectivity and low-selectivity parameters, and should confirm that the plan still behaves as expected after statistics are updated.

How Does the DbContext Lifecycle Affect Performance and Stability?

A DbContext should have a clear, short-lived unit-of-work lifecycle, with creation and disposal managed by dependency injection. It is not suitable for sharing across multiple threads or for use as a process-wide, long-lived object.

Creating a context manually for every query may appear to avoid state contamination, but it can make configuration, transactions, and testing difficult to keep consistent. Conversely, retaining the same context for a long time accumulates tracking state and increases the risk of incorrect concurrent use. A typical web request can align the lifecycle with one unit of work, while background processing should create a separate scope for each unit of work.

When using context pooling, reusable instances are safely reset; this does not mean business state may persist across requests. Any state that varies by tenant, user, or request must have an explicit setup and cleanup mechanism. Database connection settings should also be managed through an authoritative configuration source and deployment process, rather than stored in a custom static cache without synchronization protection.

Lifecycle checklist:

• Every unit of work has clear creation and disposal boundaries.
• The same DbContext is not shared across concurrent operations.
• Query cancellation propagates downward so database resources are not consumed after a request is aborted.
• A transaction scope encloses only necessary operations and does not include external calls.
• Connection-pool capacity, context-pool capacity, and database limits are planned together.
• Deployment and restart behavior after configuration changes has a validation procedure.

How Can You Establish a Repeatable Performance Optimization Process?

A repeatable optimization process first establishes a reproducible case, then examines SQL, data volume, the execution plan, and application cost in sequence. Change only one major factor at a time and retain before-and-after evidence.

Follow these steps:

1. Fix the test case: Record the query entry point, representative parameters, data distribution, and timeout conditions.
2. Capture the SQL: Confirm that filtering, ordering, aggregation, and pagination are actually pushed down.
3. Inspect data volume: Compare the number of returned rows and columns with what the screen actually needs.
4. Review the plan: Identify scans, sorts, joins, and index usage.
5. Narrow the query: Address premature materialization, projection, relationships, and pagination first.
6. Adjust indexes: Make indexes serve a confirmed query shape instead of guessing which columns may help.
7. Inspect the application layer: Find linear searches inside hot loops, repeated conversions, and serialization costs.
8. Run regression validation: Confirm correct results, stable ordering, controlled memory use, and no newly introduced query explosion.

At minimum, measure database execution, data transfer, object materialization, and response serialization separately. Endpoint duration alone cannot identify the layer responsible for an improvement and may mistake cache warm-up for program optimization. Before-and-after versions should be compared with the same data, parameters, and test procedure.

Application-layer data structures can also amplify cost. If a hot loop repeatedly uses a List for membership checks, growth in the data may create quadratic work. Using a HashSet or a prebuilt lookup dictionary can usually express the intent more clearly. However, this kind of improvement should come after "do not retrieve unused data," so the team does not merely accelerate full-dataset processing that should never have existed.

Pre-Production Checklist for EF Core Query Performance

Before production deployment, demonstrate that the query performs the necessary work in the database, returns only required data, uses stable ordering and appropriate indexes, and passes regression tests against representative data distributions.

SQL and data volume

• All primary filters occur before materialization.
• Custom expressions have been confirmed as translatable by the current database provider.
• SQL selects only required columns and does not load a complete object graph without a purpose.
• Statistics use database-side aggregation instead of loading lists merely to count them.
• Loops have been checked for additional queries.

Pagination and indexes

• Sort keys are stable, and duplicate values are disambiguated with a unique field.
• The keyset cursor direction matches the ordering and is derived from the original order.
• Enrichment is performed only for data on the current page.
• The execution plan uses the expected indexes, and representative parameters have been validated.
• The write and storage costs of new indexes have been included in the assessment.

Lifecycle and regression testing

• Read-only paths use an appropriate no-tracking mode.
• A DbContext is not shared across concurrent operations.
• Cancellation, timeouts, and transaction-boundary behavior are explicit.
• Tests cover empty data, duplicate sort keys, large result sets, and continuously inserted data.
• Before-and-after optimization evidence is reproducible, and conclusions are not based on a single cached result.

Conclusion: Fix the Query Shape Before Micro-Optimizing

The priority in EF Core query performance is to keep computation in the right place: the database filters, sorts, and aggregates, while the application receives only the necessary results and handles business presentation.

First confirm that the IQueryable pipeline is not terminated prematurely. Next, use projection to reduce columns and relationships, design stable pagination, and verify indexes with execution plans. Only then address tracking details, context pooling, and application-layer data structures. This sequence avoids micro-optimizing an incorrect query shape and allows every improvement to be validated against the same case.

Suggested internal links: When data scale and distribution already require a horizontal-scaling decision, continue with "MongoDB Sharding Decision Guide." If the optimization involves release and rollback procedures, read "Building an Auditable CI/CD Release Process" to establish change quality gates.