Why an EF Core 50-row page can fetch every matching order

Find where the page limit takes effect

The example model has six mapped order properties: ID, tenant ID, customer ID, creation time, total and internal notes. The screen needs only ID, creation time and total. It is read-only and does not need an exact total count or arbitrary page jumps. A configured OrdersContext, trusted tenant/customer IDs and a cancellation token are prerequisites; the complete model and retained output are in the translation companion.

var tenantId = 1;   // From trusted authorization context, not a raw request value.
var customerId = 42; // Already authorized for this tenant and caller.
const int pageSize = 50;

var allEntities = db.Orders
    .TagWith("OrderSearch.Recent")
    .Where(o => o.TenantId == tenantId && o.CustomerId == customerId)
    .OrderByDescending(o => o.CreatedAt)
    .ThenByDescending(o => o.Id);

var entities = await allEntities.ToListAsync(cancellationToken);
var page = entities.Take(pageSize)
    .Select(o => new OrderRow(o.Id, o.CreatedAt, o.Total)).ToList();
var hasMore = entities.Count > pageSize;

Up to ToListAsync, allEntities is an IQueryable describing work for the provider. After that await, entities is an in-memory list. Its Take(50) changes what the application displays; it cannot add a row limit to the SQL that has already run. The method also asks EF to materialize full entities before selecting the three displayed fields. Projection and result limits

Do not pass a tenant or customer ID straight from the request into this example and call it authorized. Keep every real access rule in the query, or establish it through the application’s authorization path before constructing the query. A translated tenant predicate does not prove that the caller was allowed to choose that tenant.

Read the SQL before changing the LINQ

The companion pins SDK 10.0.101, .NET runtime 10.0.1, EF Core and the SQL Server provider 10.0.1, and SqlClient 6.1.1. UseSqlServer uses SQL compatibility level 160. These versions identify the reproduction; they are not a recommendation to deploy those older patches. Provider package 10.0.1

Console.WriteLine(allEntities.ToQueryString());

Observed provider output for the synthetic tenant 1 and customer 42:

DECLARE @tenantId int = 1;
DECLARE @customerId int = 42;

-- OrderSearch.Recent

SELECT [o].[Id], [o].[CreatedAt], [o].[CustomerId], [o].[InternalNotes], [o].[TenantId], [o].[Total]
FROM [Orders] AS [o]
WHERE [o].[TenantId] = @tenantId AND [o].[CustomerId] = @customerId
ORDER BY [o].[CreatedAt] DESC, [o].[Id] DESC

There is no TOP or offset/fetch limit. The SELECT includes InternalNotes even though the screen never displays it. The tenant/customer predicates and descending creation-time/ID ordering are present. These are visible properties of this translation, not observations of returned rows.

ToQueryString produces a debugging representation and does not execute this SQL. It is not an execution plan, command-duration measurement or promise that the string can be pasted directly into every SQL tool. Inspect the commands the application actually executes when diagnosing its runtime behavior. API boundary, 10.0.1 source

Move the limit before materialization, then narrow the result

The smallest change to this first-page contract is allEntities.Take(pageSize + 1) before ToListAsync. The companion translates that shape too: it adds TOP(@p) with @p = 51, while retaining all six entity columns. That isolates the row limit from the next decision: does a read-only screen need those entities at all?

Here it needs three scalar values. Keep the same authorized filter and ordering, project those values while the query is still an IQueryable, and then apply the limit:

var firstPage = allEntities
    .Select(o => new OrderRow(o.Id, o.CreatedAt, o.Total))
    .Take(pageSize + 1);

var rows = await firstPage.ToListAsync(cancellationToken);
var hasMore = rows.Count > pageSize;
var page = rows.Take(pageSize).ToList();

public sealed record OrderRow(int Id, DateTime CreatedAt, decimal Total);

Observed SQL for this projected first page:

DECLARE @p int = 51;
DECLARE @tenantId int = 1;
DECLARE @customerId int = 42;

-- OrderSearch.Recent

SELECT TOP(@p) [o].[Id], [o].[CreatedAt], [o].[Total]
FROM [Orders] AS [o]
WHERE [o].[TenantId] = @tenantId AND [o].[CustomerId] = @customerId
ORDER BY [o].[CreatedAt] DESC, [o].[Id] DESC

The provider now requests at most 51 rows and only ID, creation time and total. The extra row supports hasMore; display the first 50. The flag answers whether more rows exist without fetching the remaining history. It is not an exact total count; neither shown query adds a separate count command, and no reduction in executed command count was observed.

The unique ID resolves tied timestamps, making the ordering fully unique for this model. The flag does not retrieve the next page. This example covers only the first page; agree next-page behavior separately. Stable ordering does not freeze data while orders are added or changed between requests. Pagination guidance

OrderRow contains no entity instances, so EF does not track these projected results. Adding AsNoTracking alone to the original query would not add TOP or remove InternalNotes: tracking, selected columns and the result limit are separate decisions. Keep entity tracking where an edit path depends on it. Tracking and projections

A smaller result request is not proof of a faster search

TOP(51) bounds the result sent back; it does not prove that SQL Server examines only 51 rows. Without a useful access path, the server may still scan or sort substantial data to find those rows. The projection reduces the requested columns, but the plan, row distribution, network and materialization cost determine the practical result. Efficient querying SQL Server TOP semantics

If the application’s executed SQL is already bounded and narrowly projected, this rewrite is not the missing fix. Follow the expensive command to its plan, blocking and logical reads, or investigate other commands and work outside this query. An actual SQL Server execution plan runs the statement to collect runtime information; obtain it in an approved environment. Actual execution plans

Use a stable TagWith label to find the operation in EF command logs. Keep personal information out of tags and avoid sensitive-data logging in ordinary production diagnostics. Capture all commands for the search, not just this query, and time the complete await and endpoint separately. A command duration is not the time the user waits. Performance diagnosis, Query tags, Logging safeguards

Reproduce the translation; measure execution separately

The companion includes the configured model, all three translation shapes, a dependency lock file and the raw output. Translation mode has no database requirement. From its harness directory, with the pinned SDK/runtime installed:

dotnet restore QueryReview.csproj --locked-mode --configfile NuGet.Config
dotnet build QueryReview.csproj --configuration Release --no-restore
dotnet exec bin/Release/net10.0/QueryReview.dll

For readers with an authorized local SQL Server 2022 or later on a supported host, the companion also supplies setup.sql and an optional --execute-local mode. The script creates a fresh EFQueryReviewDemo scratch database with three synthetic 500-order histories, including another tenant with the same customer ID and tied timestamps; it refuses an existing database. The program accepts only a loopback server and that database name. This setup and execution path was not run here.

Optional execution compares two shapes: the original entity query and the bounded projection. The bounded entity variant is translated only. After one warmup for each of the two execution shapes, the optional mode alternates three samples each with fresh contexts. It records executed reader commands, fetched rows, tracked entities and elapsed time through materialization and page construction. Compare the ordered page hash and hasMore between shapes. The combined comparison cannot attribute a timing difference separately to the row limit, projection or tracking. Those synthetic samples do not establish production latency, authorization or behavior under concurrent load.

• Agree representative ordinary and long histories, busy-period demand, acceptable time to usable results, and error/timeout targets before comparing. Choose values from the screen’s needs; there is no universal threshold.
• Preserve every access rule and compare the ordered first 50 IDs, timestamps, totals and more-results flag. Check 0, 49, 50 and 51 matches; the expected flag is false, false, false and true. Include a timestamp tie across the page boundary and another tenant with the same customer ID. These are required checks, not passed results.
• Compare fetched rows and executed command count, not just the 50 displayed rows. Measure the complete awaited query and endpoint under comparable demand, keeping plans and logical reads beside elapsed time. For a faster-screen claim, also measure the search action to usable results where practical.
• Reject incorrect results. Correct results meeting the agreed targets support acceptance; correct results still missing them require diagnosis of the remaining database or endpoint work before choosing capacity.

Executed on October 10, 2026: the companion compiled with SDK 10.0.101 and ran on .NET 10.0.1 using EF Core/SQL Server provider 10.0.1 and SqlClient 6.1.1. Translation produced the three recorded SQL strings; the connection remained closed and the collector recorded zero database commands. No SQL Server setup or query was executed, and no result equality, returned-row count, tracking count, execution plan, logical reads or elapsed query time was measured. The await examples and optional SQL Server path are compiled or supplied code, not observed database results.