Seeing the SQL Behind Your EF Core Pagination in Rider

You write .Skip(200).Take(20) in a LINQ query, ship it, and a week later someone asks why the page-11 endpoint takes four seconds. The LINQ looked fine. The problem is that LINQ is a promise, not a plan — and the only way to know what your pagination actually costs is to read the SQL it generates and the plan the database builds around it.

Rider makes that first step cheap: it can show you the SQL a given EF Core query will produce without you running the app, and its inspections flag the pagination patterns that quietly hurt at scale. This post walks through that workflow, where it helps, and where it can mislead you.

Why Take/Skip deserves a second look

Offset pagination with Skip and Take is the default most of us reach for. EF Core translates it into OFFSET ... FETCH (SQL Server) or LIMIT ... OFFSET (PostgreSQL), and for shallow pages it is perfectly fine. Two things change as data grows:

• Deep offsets get expensive. The database still has to read and discard the skipped rows. Page 500 means scanning past thousands of rows to return twenty.
• The ordering column matters. Without an index that matches your ORDER BY, every page request can become a sort over the full table.

Neither of these is visible in the C# code. Both are visible in the generated SQL and the execution plan — which is exactly where an IDE that surfaces the SQL earns its keep.

Previewing generated SQL before you run anything

Rider's EF Core integration can display the SQL produced from a LINQ expression in the editor, so you can confirm that your Skip/Take actually translated into an offset/fetch clause rather than, say, client-side evaluation pulling the whole table into memory. The exact menu names and shortcuts vary between Rider versions, so check Help | Find Action (Ctrl+Shift+A) and search for the EF Core or SQL-related actions in your installed version rather than trusting a shortcut from a blog post — including this one.

A typical workflow looks like this:

1. Write the query, e.g. context.Orders.OrderBy(o => o.CreatedAt).Skip(page * pageSize).Take(pageSize).ToListAsync().
2. Use Rider's EF Core query inspection to view the generated SQL.
3. Confirm three things: the ORDER BY is present and deterministic, the offset/fetch clause is in the SQL (not evaluated client-side), and no unexpected extra queries appear (a sign of N+1 from lazy-loaded navigation properties).

That third point matters more than people expect. Rider's code inspections can warn when a query inside a loop or a projection risks issuing one query per row — the classic N+1 — which offset pagination tends to expose because you're suddenly loading twenty parent rows at once instead of one.

A worked example: spotting a silent full-table scan

Consider this query against an Orders table with a few million rows:

var page = await context.Orders
    .OrderBy(o => o.CreatedAt)
    .Skip(100_000)
    .Take(20)
    .ToListAsync();

In the SQL preview you should see something equivalent to ORDER BY [CreatedAt] OFFSET 100000 ROWS FETCH NEXT 20 ROWS ONLY. So far so good — the translation is correct. The problem appears when you take that SQL to the database and check the execution plan. If there is no index on CreatedAt, the plan will show a full scan plus a sort on every single page request.

The fix is a matching index:

-- Run against your database with appropriate permissions (DDL rights).
-- On a large, live table, use your platform's online index option
-- (e.g. ONLINE = ON on SQL Server Enterprise, CONCURRENTLY on PostgreSQL)
-- to avoid blocking writes.
CREATE INDEX IX_Orders_CreatedAt ON Orders (CreatedAt);

After adding it, re-check the plan: you want to see an index seek or ordered scan instead of a sort. This is the practical loop Rider enables — preview SQL in the IDE, verify the plan in the database, adjust, repeat.

What the preview can't tell you

The IDE preview shows the SQL for a particular LINQ expression shape. It does not know your row counts, your parameter values, or whether EF Core's query cache will produce a slightly different parameterized form at runtime. Treat the preview as a structural check, not a performance guarantee. The authoritative verification is always:

• Enable EF Core logging (or ToQueryString() on the IQueryable) in a running environment and compare the emitted SQL with what the IDE showed.
• Run the query with realistic parameters and inspect the actual execution plan, not the estimated one.

Also note that Rider's inspections are heuristics. A warning about missing AsNoTracking() is worth heeding for read-only pagination endpoints — change tracking twenty rows you will never update is pure overhead — but only you know whether the endpoint is truly read-only.

When to move past Skip/Take entirely

If your verification shows deep-offset pages are still slow even with a proper index, the honest answer is that offset pagination is the wrong tool for that endpoint. Keyset (cursor) pagination — filtering with WHERE CreatedAt < @lastSeen instead of skipping — keeps cost constant regardless of page depth. Rider's SQL preview is just as useful here: it lets you confirm the keyset predicate lands in the WHERE clause and uses your index.

The actionable takeaway: next time you write a paged endpoint, spend two minutes viewing the generated SQL in Rider before you commit. Check the ordering, check the offset clause, then check the real plan in the database. Pagination bugs are rarely in the C# — they're in the gap between what you think the query does and what the database actually executes.