Rownumber paging generates invalid SQL with DTO projection and orderby

[youclk]

I find a bug ，if use RowNumber of paging：

services.AddDbContext<EFDbContext>(
    options => options.UseSqlServer(Configuration.GetConnectionString("XXX"), x => x.UseRowNumberForPaging())
    );

At this point, the orderby fields and select the fields can't be the same：

var test = await _users.OrderBy(i => i.Id)
    .Select(i => new UserInfo
    {
        Id = i.Id
    }).Skip(0).Take(30).ToListAsync();

the translation of sql：

exec sp_executesql N'SELECT [t].[Id]
FROM (
    SELECT [i].[UserID] AS [Id], ROW_NUMBER() OVER(ORDER BY [Id] DESC) AS [__RowNumber__]
    FROM [User] AS [i]
) AS [t]
WHERE ([t].[__RowNumber__] > @__p_0) AND ([t].[__RowNumber__] <= (@__p_0 + @__p_1))',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=30

Error info ：the column name 'Id' is invalid

[smitpatel]

We probably need to unwrap AliasExpression.

[ajcvickers]

Triage: @smitpatel to document workarounds so we have more information for patch consideration.

[smitpatel]

Work-around
For queries like above not using explicit member names for projection avoids the issue.
Like following

var test = await _users.OrderBy(i => i.Id)
    .Select(i => new UserInfo
    {
        i.Id
    }).Skip(0).Take(30).ToListAsync();

If the projection is non-member expression where assigning member name is mandatory, removing skip can help generating correct query but it may cause to fetch large data. Or other option is to do the custom projection on client.

For example,
Queries like following

var test = await _users
    .Select(i => new UserInfo
    {
        DiscountedPrice = i.Price * i.Discount
    })
    .OrderBy(I => i.DiscountedPrice).Skip(0).Take(30).ToList();

// Work-arounds
// Remove skip
var test = await _users
    .Select(i => new UserInfo
    {
        DiscountedPrice = i.Price * i.Discount
    })
    .OrderBy(I => i.DiscountedPrice).Take(30).ToList();

// Remove projection
var test = await _users
    .OrderBy(I => i.Price * i.Discount).Skip(0).Take(30).AsEnumerable()
    .Select(i => new UserInfo
    {
        DiscountedPrice = i.Price * i.Discount
    }).ToList();

[ajcvickers]

Triage: The workarounds and scope of the fix together with feedback are currently not such that this meets the bar for patch.

[smithaitufe]

Please what is the state of this issue?

I am currently having similar issue. How do I resolve it?

https://aspnetcore.slack.com/archives/C18UW3ALA/p1521474152000171

[ajcvickers]

@smithaitufe This issue is fixed in the 2.1 preview1 release. So the options here are:

• Use the preview release
• Use the workarounds listed above
• Wait for the 2.1 RTM release

[ajcvickers]

@youclk If you are still using RowNumberForPaging, could you please comment on issue #13959 and let us know why?