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

Entity Framework Core translates Skip and Take into provider-specific SQL, but the exact translation depends on the database compatibility level. On SQL Server, a Skip/Take query typically becomes OFFSET ... ROWS FETCH NEXT ... ROWS ONLY on SQL Server 2012 or a compatible compatibility level; older instances may produce a different SQL shape. This creates a configuration boundary where the same LINQ expression can generate structurally different queries depending on server settings rather than application code.

When paginating large datasets, deep Skip values force the database to process and discard skipped rows, making latency grow with page depth even when the page size stays constant. EF Core does not provide a built-in keyset or seek pagination primitive, so avoiding this performance cliff requires manually filtering on a stable ordered column. The transition point between offset pagination and a manual keyset approach is not enforced by the framework, leaving developers to decide where the trade-off occurs.

Does EF Core expose any provider-agnostic way to detect whether OFFSET/FETCH translation is available for the current connection? At what dataset size or offset depth should a codebase switch from Skip/Take to a manual keyset predicate, and how does that threshold vary across providers? If an application must support both modern and legacy SQL Server compatibility levels, should paging logic be centralized in the repository layer or handled per query?