Limit/Offset alters select statement by adding row_num

[mark-at-tusksoft]

Using SqlServerCompiler and running queries against SqlServer2017 (composing queries then feeding resulting queries as sql to EF context).

When I compile a query using .ForPage(), it wraps my intended query as a subquery with an extra ROW_NUMBER() column for ordering and the outer query uses a select * which brings along that extra row_num column. Since I have to specify the structure returned (in this case long because I am only selecting Ids) this extra column mutates the expected structure.

If this is intended behavior, that's fine, but it needs to be in bold, red font on the docs for Limit and Offset because this is an unexpected side-effect.

Edit: It appears that this happens with Limit().Offset() as well.

[mark-at-tusksoft]

Adding yet another sub-query fixes this.

var pagedQuery = new Query().From(query.ForPage(request.Page, request.PageSize).Select("pg.Id"), "out");
var compiledQuery = _compiler.Compile(pagedQuery.Select("out.Id"));

Perhaps a boolean on Query.Offset() and Query.ForPage() to apply the original select instead of *? Should help keep the nesting down. Or since the equivalence of the select once nested is not guaranteed, a raw select override param. I'm pretty much spitballing at this point.

[jongibler]

If you set that to FALSE it will use offset paging

[ahmad-moussawi]

I will close this now, since setting UseLegacyPagination = false will help in this case, if this still an issue for you, feel free to reopen