RC1 Strange behavoir of EF on paging

[mo-esmp]

I have a query like this:

var query = context.Payments.Include(p => p.OnlinePayment)
.Include( p => p.ShoppingCart)
.Include( p => p.Customer)
.Select(p =>
           new PaymentListDto
           {
               PaymentId = p.Id,
               CustomerName = $"{p.Customer.CompanyName} {p.Customer.FirstName} {p.Customer.LastName}",
               Amount = p.Amount,
                PaymentStatus = p.IsSuccess,
               BillNumber = p.ShoppingCart.BillNumber,
               ShoppingCartId = p.ShoppingCart.Id,
               PaymentGateway = p.OnlinePayment.PaymentGateway,
               PaymentType = p.PaidType,
               RegisterDate = p.SubmitDate
            }
        )
.OrderByDescending(p => p.RegisterDate)
.Skip(filter.PageSize * filter.PageNumber)
.Take(filter.PageSize).ToList();

when PageNumber is 0, EF uses OFFSET in generated SQL query for paging. here is TSQL:

SELECT [t].[Id], [t.Customer].[CompanyName], [t.Customer].[FirstName], [t.Customer].[LastName], [t].[Amount], [t].[IsSuccess], [t.ShoppingCart].[BillNumber], [t.ShoppingCart].[Id], [t.OnlinePayment].[PaymentGateway], [t].[PaidType], [t].[SubmitDate]
FROM [Payment] AS [t]
INNER JOIN [OnlinePayment] AS [t.OnlinePayment] ON [t].[Id] = [t.OnlinePayment].[PaymentId]
INNER JOIN [ShoppingCart] AS [t.ShoppingCart] ON [t].[Id] = [t.ShoppingCart].[PaymentId]
INNER JOIN [Customer] AS [t.Customer] ON [t].[CustomerId] = [t.Customer].[Id]
WHERE [t].[IsDeleted] = 0
ORDER BY [t].[SubmitDate] DESC
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

when PageNumber is gearter than 0, EF uses ROW_NUMBER() for paging. here is TSQL:

ELECT [t0].[Id], [t0].[CompanyName], [t0].[FirstName], [t0].[LastName], [t0].[Amount], [t0].[IsSuccess], 
[t0].[BillNumber],  **[t0].[Id] AS [c0]**, [t0].[PaymentGateway], [t0].[PaidType], [t0].[SubmitDate]
FROM (
    SELECT [t].[Id], [t.Customer].[CompanyName], [t.Customer].[FirstName], [t.Customer].[LastName], [t].[Amount], [t].[IsSuccess], [t.ShoppingCart].[BillNumber], 
**[t.ShoppingCart].[Id] AS [c0]**, [t.OnlinePayment].[PaymentGateway], [t].[PaidType], [t].[SubmitDate],
 ROW_NUMBER() OVER(ORDER BY [t].[SubmitDate] DESC) AS [__RowNumber__]
    FROM [Payment] AS [t]
    INNER JOIN [OnlinePayment] AS [t.OnlinePayment] ON [t].[Id] = [t.OnlinePayment].[PaymentId]
    INNER JOIN [ShoppingCart] AS [t.ShoppingCart] ON [t].[Id] = [t.ShoppingCart].[PaymentId]
    INNER JOIN [Customer] AS [t.Customer] ON [t].[CustomerId] = [t.Customer].[Id]
    WHERE [t].[IsDeleted] = 0
) AS [t0]
WHERE ([t0].[__RowNumber__] > 30) AND ([t0].[__RowNumber__] <= 40)

in subquery there is [t.ShoppingCart].[Id] AS [c0] and [c0] is ShoppingCart.Id and in wrapper query there is select [t0].[Id] AS [c0] and [t0].[Id] is Payment.Id and it returns wrong data.

when I change LINQ query to this

.Select(p =>
           new PaymentListDto
           {
               PaymentId = p.ShoppingCart.PaymentId,

it works fine.

SELECT [t0].[PaymentId], [t0].[CompanyName], [t0].[FirstName], [t0].[LastName],
 [t0].[Amount], [t0].[IsSuccess], [t0].[BillNumber], **[t0].[Id]**, [t0].[PaymentGateway],
 [t0].[PaidType], [t0].[SubmitDate]
FROM (
    SELECT [t.ShoppingCart].[PaymentId], [t.Customer].[CompanyName], [t.Customer].[FirstName], 
[t.Customer].[LastName], [t].[Amount], [t].[IsSuccess], [t.ShoppingCart].[BillNumber],
** [t.ShoppingCart].[Id]**, [t.OnlinePayment].[PaymentGateway], [t].[PaidType], [t].[SubmitDate], ROW_NUMBER() OVER(ORDER BY [t].[SubmitDate] DESC) AS [__RowNumber__]
    FROM [Payment] AS [t]
    INNER JOIN [OnlinePayment] AS [t.OnlinePayment] ON [t].[Id] = [t.OnlinePayment].[PaymentId]
    INNER JOIN [Customer] AS [t.Customer] ON [t].[CustomerId] = [t.Customer].[Id]
    INNER JOIN [ShoppingCart] AS [t.ShoppingCart] ON [t].[Id] = [t.ShoppingCart].[PaymentId]
    WHERE [t].[IsDeleted] = 0
) AS [t0]
WHERE ([t0].[__RowNumber__] > 20) AND ([t0].[__RowNumber__] <= 30)

[smitpatel]

This no longer reproduces. Verified in RC2. We use OFFSET/FETCH for any PageNumber for both of the queries written above. @rowanmiller - Anything to do here? or close?

[rowanmiller]

You can close