EF Core Skip/Take pagination changes SQL translation across SQL Server compatibility boundaries

Answer first

No. EF Core does not expose a provider-agnostic API to detect whether OFFSET/FETCH translation is available for the current connection. Availability is a provider and database configuration concern, not a capability EF Core advertises. For SQL Server you must inspect the database compatibility level per connection.

There is no universal dataset size or offset depth where you must switch from Skip/Take to keyset pagination. The threshold is provider and workload specific and must be measured. As a planning guideline, performance degradation from deep OFFSET becomes noticeable once skipped rows reach the low thousands and grows with page depth even for constant page size. EF Core has no built-in keyset/seek pagination primitive; it must be implemented manually with a WHERE predicate on a stable ordered column.

If you must support both modern and legacy SQL Server compatibility levels, centralize the paging contract in the repository layer but keep the SQL generation decision visible. A repository can expose a single pagination API while allowing per-query or per-provider strategies for offset vs keyset. Hiding provider specifics completely can obscure the need for different SQL shapes across compatibility levels.

Confirmed facts vs likely explanation

Confirmed behavior

• EF Core translates Skip/Take to OFFSET ... ROWS FETCH NEXT ... ROWS ONLY only when SQL Server compatibility level is 110 or higher, i.e., SQL Server 2012 and later compatibility.
• On older compatibility levels, e.g., 100 for SQL Server 2008, EF Core generates alternative patterns such as ROW_NUMBER() based queries.
• EF Core does not provide a built-in keyset/seek pagination primitive. Offset pagination forces the database to process and discard skipped rows.
• No provider-agnostic API exists in EF Core to detect OFFSET/FETCH support. Detection requires querying the database.

Likely explanation

• Deep Skip values increase latency linearly with page depth because the engine must scan, sort and discard rows before returning the page.
• Keyset pagination avoids the discard work by filtering on the last seen key, but it requires a stable, unique ordering column and breaks if the underlying data changes between pages.
• Compatibility level detection must be done per connection, not once at startup, because different databases or tenants can have different settings.
• Repository-level abstraction can be useful for consistency, but it may hide provider-specific optimizations needed for different compatibility levels.

Steps needed for this case

1. Verify current translation.
2. Check compatibility level per connection:

SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();

1. Enable EF Core logging for the same LINQ query and compare generated SQL across compatibility levels.
2. Measure pagination cost at representative Skip depths, e.g., 100, 1000, 10000, with both offset and manual keyset predicates on your actual schema and indexes.
3. Decide the paging strategy per provider/compatibility level. Keep the public contract stable in the repository, but allow the implementation to choose offset vs keyset based on measured thresholds and compatibility.

One diagnostic detail that changes the recommendation: is your pagination ordering column stable and unique and indexed? Keyset pagination is only viable with a reliable seek column. If not, offset remains the safer default even on modern compatibility levels.