pagination bug mssql
We have a view:
CREATE VIEW StockInformation AS SELECT dbo.Product.Code AS ItemCode, dbo.Warehouse.Code AS WarehouseCode, dbo.StockIndicator.Code AS StockIndicatorCode, dbo.Stock.StockOnShelve, dbo.Stock.StockAvailable,
dbo.Stock.StockPurchaseBackorder, dbo.Stock.StockFirstPurchaseDeliveryDate
FROM dbo.Stock INNER JOIN
dbo.Warehouse ON dbo.Stock.WarehouseID = dbo.Warehouse.WarehouseID INNER JOIN
dbo.StockIndicator ON dbo.Stock.StockIndicatorID = dbo.StockIndicator.StockIndicatorID RIGHT OUTER JOIN
dbo.Product ON dbo.Stock.ProductID = dbo.Product.ProductID
and the following query:
var stocks = con.Select<StockInformation>(con.From<StockInformation>().OrderBy(x=> x.ItemCode).Limit(pageSize * pageNumber, pageSize));
The first page results into query:
SELECT TOP [pageSize] ... FROM StockInformation.
The second query looks like this:
SELECT * FROM (SELECT "ItemCode", "WarehouseCode", "StockIndicatorCode", "StockOnShelve", "StockAvailable", "StockPurchaseBackorder", "StockFirstPurchaseDeliveryDate", ROW_NUMBER() OVER (ORDER BY "StockInformation"."ItemCode") As RowNum
FROM "StockInformation") AS RowConstrainedResult WHERE RowNum > 1000 AND RowNum <= 2000
The problem is that actually the view isn't ordered by itemcode (since views can't be ordered). So some items go missing and some are displayed twice.

If you have MSSQL 2012+ you can use `SqlServer2012Dialect.Provider` and it will use a more optimal paging query.
Please submit any bugs/issues to our issue tracker instead:
-
It needs to use a windowing function for old versions of MSSQL. If you have MSSQL 2012+ you can use `SqlServer2012Dialect.Provider` and it will use a more optimal paging query.