Unpredictable order of category products in admin

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

I noticed the same thing happening with specification attributes which I mentioned in this post. I thought it had been fixed in the 3.7 release as part of this work item but it looks like there are probably other service methods that have the same issue.

Hi Pete,

Yes, it is the same issue, just in a different area. (I searched the forums, but didn't find your post initially)

The only areas I could find that were affected were category products and manufacturer products.

The issue is rooted in the version of SQL server, as it is only when Entity Framework against SQL 2012 and above uses OFFSET and FETCH to do the paging that the issue occurs.

Thanks a lot! Please see this commit