NOP version: 3.80
SQL version: 11.0.3156.0 (2012)
When viewing the product list in a category that contains many products (315 in this case) on the admin site (/Admin/Category/Edit, Products tab), where all the products have the same DisplayOrder, the pages of results are not stable. We mean by this that the same product is appearing in random places and on more than one page.
The error only occurs on SQL 2012 and above.
We have tracked the issue down to the EF generated SQL for 2012 where it generated SQL like this:
SELECT
[Project1].[Id] AS [Id],
[Project1].[ProductId] AS [ProductId],
[Project1].[CategoryId] AS [CategoryId],
[Project1].[IsFeaturedProduct] AS [IsFeaturedProduct],
[Project1].[DisplayOrder] AS [DisplayOrder]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[ProductId] AS [ProductId],
[Extent1].[CategoryId] AS [CategoryId],
[Extent1].[IsFeaturedProduct] AS [IsFeaturedProduct],
[Extent1].[DisplayOrder] AS [DisplayOrder]
FROM [dbo].[Product_Category_Mapping] AS [Extent1]
INNER JOIN [dbo].[Product] AS [Extent2] ON [Extent1].[ProductId] = [Extent2].[Id]
WHERE ([Extent1].[CategoryId] = @p__linq__0) AND ([Extent2].[Deleted] <> 1) AND (@p__linq__1 = 1 OR [Extent2].[Published] = 1)
) AS [Project1]
ORDER BY [Project1].[DisplayOrder] ASC
OFFSET 0 ROWS FETCH NEXT 15 ROWS ONLY
For SQL 2012, EF uses OFFSET and FETCH.
According the MS (https://msdn.microsoft.com/en-us/library/ms188385.aspx) and StackOverflow (http://stackoverflow.com/questions/37184267/in-sql-server-2014-order-by-clause-with-offset-fetch-next-returns-weired-results), for OFFSET and FETCH to return stable results, the order by clause must be unique.
When all products have the same DisplayOrder, the order by is not unique.
So, to fix this, the ORDER BY needs to have the Product Id added on to the end.
This can be fixed in CategoryService.cs at line 422 by adding ',p.Id' to the order by.
We also believe this affects Manufacturers Products in the same way, and can be fixed in the same way.
We are happy to submit a pull request for this if you would like us to.
Thanks
Phil