I suggest you ...

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.

0 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    1 comment

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base