Faster Entity Framework Core Queries Without Guesswork

Entity Framework Core lets .NET developers work with relational data using familiar C# objects and LINQ expressions. That productivity is valuable, but a query that looks tidy in an application can still produce excessive SQL, retrieve far too many rows, or trigger a stream of unexpected database calls. Performance problems usually appear later, when realistic data volumes and real users expose the difference between a convenient query and an efficient one.

The strongest improvements come from understanding the complete path from LINQ to SQL Server or another relational database. Query shape, indexes, tracking behaviour, relationships, pagination and measurement all influence response times. For Australian applications serving customers from Brisbane to Perth, a few inefficient queries can also increase cloud costs and make network latency much more noticeable.

Start With The SQL Shape

EF Core translates most LINQ expressions into SQL, but it does not execute C# in the database. The provider builds an expression tree, converts the supported parts into SQL, sends that SQL to the server, and materialises the returned data into .NET objects. This distinction matters because operations such as Where, Select, OrderBy, and Take can usually be translated efficiently, while custom methods or premature materialisation may move work into application memory.

Calling ToListAsync() too early is a common mistake. Once the query has been materialised, later filtering happens in the application rather than in SQL Server. A query such as context.Orders.ToListAsync() followed by a C# filter may load every order before selecting the few required records. Keep the query as an IQueryable until the database has received all practical filters, sorting rules, projections and limits.

A useful habit is to inspect generated SQL with query.ToQueryString() during development. Logging can reveal a missing predicate, an accidental cross join, or a query that selects every column from several tables. The SQL is not merely an implementation detail; it is evidence of what the database has actually been asked to do.

Select Only What The Screen Needs

Entity queries often retrieve complete rows even when a page needs only a few fields. Loading a full Customer entity for a table displaying a name, suburb and account status wastes database I/O, network bandwidth and object materialisation time. Projection with Select creates a smaller result set and makes the required shape explicit.

For example, a dashboard can use a projection such as context.Customers.Where(c => c.IsActive).Select(c => new CustomerSummary { Id = c.Id, Name = c.Name, Suburb = c.Suburb }).ToListAsync(). The database can then return only those columns. This is especially useful for a service used across Australia, where a request travelling between an application region in Sydney and users in Perth already has unavoidable network distance.

Projection also prevents a subtle form of over-fetching in APIs. Returning entities directly can expose fields that were never intended for a client and can create circular reference problems during serialisation. A deliberately shaped DTO gives the endpoint a stable contract and reduces the amount of JSON sent to browsers and mobile devices. The same principle applies to an e-commerce site for a small Australian business: a product listing rarely needs the entire inventory, audit and supplier record.

Build Indexes Around Real Filters

An index can transform a slow lookup into a fast seek, but indexes must match the way an application searches and sorts. If users commonly request active invoices for a particular customer ordered by date, an index involving customer ID, active status and invoice date may be more useful than several unrelated single-column indexes. The ideal order depends on selectivity, sort requirements and the database engine’s execution plan.

Start by examining the predicates generated by important EF Core queries. Columns used regularly in equality filters, joins, range conditions and ordering deserve attention. Foreign keys are commonly indexed, although the exact behaviour depends on the database provider and migration configuration. SQL Server indexes can also include non-key columns, allowing a frequently used projection to be served directly from the index without looking up the base table.

Indexes have a cost. Every insert, update and delete must maintain them, and each one consumes storage. A heavily indexed table may make reads faster while making writes slower, which matters for systems processing orders throughout the day. Use migrations to manage schema changes, review actual execution plans, and remove indexes that no longer support real workloads rather than adding one for every property in an entity.

Control Tracking And Entity Materialisation

EF Core tracks queried entities by default, which is helpful when an application intends to modify them and call SaveChangesAsync(). Tracking also maintains identity resolution, so repeated references to the same entity can be represented consistently. For read-only screens, however, change tracking adds memory use and CPU work without providing a benefit.

Use AsNoTracking() for queries that only display or serialise data. A reporting endpoint returning thousands of records can gain a meaningful improvement because EF Core does not need to create snapshots or place every entity in its change tracker. AsNoTrackingWithIdentityResolution() is an alternative when a read-only query contains repeated related entities and object identity still matters.

Tracking should be chosen deliberately rather than applied everywhere. A common pattern is to use a no-tracking projection for a list page, then load one specific entity in a tracked query when the user submits an edit. This keeps read-heavy traffic lean while preserving normal update workflows. It also helps applications hosted on modest App Service plans, where unnecessary allocations can contribute to garbage collection and inconsistent response times.

Load Relationships Without The N Plus One Trap

Related data is one of the easiest ways to create poor query performance. A loop that loads each customer’s orders separately can turn one request into hundreds of database round trips. This is the N plus one problem: one query retrieves the parent rows, then one additional query runs for each parent.

Eager loading with Include can solve some cases, but joining several collections may produce a cartesian explosion. A customer with many orders and many addresses can cause repeated combinations of rows to be sent from the database. AsSplitQuery() can divide a complex include graph into multiple SQL statements, reducing duplicated data at the cost of additional round trips. The right choice depends on result size and network conditions.

Projection is often the cleanest option. Instead of loading a full entity graph, select exactly the related values required by the response, such as a customer name and the latest three order dates. Avoid lazy loading in request-heavy web applications unless its behaviour is carefully understood; a property access inside a Razor view or serialiser can quietly trigger database calls. This is particularly risky for a site serving regional users over variable NBN connections, where extra round trips are visible to customers.

For a practical example, an online catalogue might need a product title, price and stock indicator while a separate creative business site, such as this quilting business site, may need a pattern name and preview image rather than every related record. Designing the projection around the page keeps the query focused.

Paginate Consistently On Large Tables

Returning every matching row does not scale, even when the query itself is well indexed. Pagination limits the amount of data processed and transferred. Offset pagination with Skip and Take is simple and works well for many administrative screens, but large offsets can become expensive because the database still has to locate and discard earlier rows.

Always pair pagination with a stable ordering. Ordering only by a timestamp can produce inconsistent pages when multiple records share the same value. Add a unique tie-breaker, such as an ID, so the ordering is deterministic: OrderByDescending(x => x.CreatedAt).ThenByDescending(x => x.Id). The supporting index should reflect that filter and ordering where possible.

Keyset pagination, also called seek pagination, is preferable for large feeds or frequently changing data. Instead of asking for page 500 with Skip, the client sends the last seen timestamp and ID, and the next query requests rows after that key. This avoids scanning a growing number of earlier records and prevents records shifting between pages as new transactions arrive. It suits activity feeds, order histories and public listings where users move forward through results.

Make String Searches And Filters Sargable

String searches can become expensive when they prevent the database from using an index. A predicate such as x.Name.StartsWith(searchTerm) may be index-friendly in some providers, while x.Name.Contains(searchTerm) commonly requires a scan. Applying a function to the indexed column, such as transforming it to lower case inside the predicate, can have the same effect.

Use database-appropriate collations, computed columns or full-text search when users need case-insensitive, linguistic or substring matching. Do not assume that a C# method has the same performance characteristics after translation. EF Core may translate it into SQL, translate it inefficiently, or reject it and require client-side evaluation depending on the provider and version.

Validate search input and avoid constructing raw SQL by string concatenation. When a query genuinely needs provider-specific features, EF Core’s parameterised FromSql APIs can be appropriate, provided the SQL remains carefully reviewed. Parameters protect against injection, while a sensible maximum search length and result limit protects the database from accidentally expensive requests.

Filtering also benefits from matching data types. Comparing a DateTime column to a calculated range is generally more useful than converting the column to a formatted string. Store and compare UTC values consistently, then apply Australian display time zones at the presentation boundary. This avoids confusing date boundaries for users in Melbourne, Adelaide or Darwin and keeps the database predicate indexable.

Measure With Realistic Workloads

Performance tuning should begin and end with measurement. Record query duration, database CPU, logical reads, allocated memory and returned row counts. EF Core’s logging and diagnostic events can show slow commands, while SQL Server Query Store can reveal execution plans and regressions over time. A local developer database with a few dozen rows cannot expose the same problems as production-scale data.

Use TagWith to label important queries, making them easier to identify in database monitoring tools. Benchmark representative queries with realistic distributions: a table containing one million orders behaves differently from one containing a few hundred. Test both warm and cold caches, and include concurrent requests rather than relying on a single stopwatch measurement.

The Australian operating environment adds practical considerations. A service deployed in an Australian cloud region may still serve users across long distances, and a business dealing with peak traffic around a local promotion or end-of-financial-year activity can experience very different load from an ordinary weekday. Check whether a slow result comes from SQL execution, connection pooling, serialisation, application code or network latency before changing the query.

For developers who enjoy exploring the broader craft of web development, Kilt and Code offers a useful home for practical software writing alongside focused EF Core work. The same disciplined approach applies: inspect the behaviour, form a measurable hypothesis, make one change, and compare the result.

Apply these techniques to the queries that matter most: inspect their generated SQL, project narrow results, add evidence-based indexes, disable tracking for read-only work, and choose a relationship-loading strategy deliberately. Then test with production-shaped data and monitor the result after deployment. Small changes to query shape can reduce database load, improve API response times and create a noticeably smoother experience for users from Sydney to the far west coast.