Order by descending whilst paging and skip

[rippo]

Hi Mark, looks like their is a bug with order by descending when paging.

e.g.

var list = db.AuctionOnlineLots
                .FindAllBySaleId(query.SaleId);

            ApplySort();
            ApplySkip();
            ApplyTake();

...

        private void ApplySkip()
        {
            if (query.Skip > 0)
            {
                list = list.Skip(query.Skip);
            }
        }

        private void ApplyTake()
        {
            list = list.Take(query.Take);
        }

        private void ApplySort()
        {
            if (string.IsNullOrWhiteSpace(query.Sort)) return;

            if (query.SortDirection == SortDirection.Asc)
                list = list.OrderBy(db[tableName][query.Sort]);

            if (query.SortDirection == SortDirection.Desc)
                list = list.OrderByDescending(db[tableName][query.Sort]);
        }

The query it generates is something along the lines of

WITH __Data AS (

    SELECT [dbo].[auction_online_lots].[id], ROW_NUMBER() 
        OVER(ORDER BY [dbo].[auction_online_lots].[sortno] DESC) AS [_#_]
    from [dbo].[auction_online_lots] 

    WHERE [dbo].[auction_online_lots].[saleid] = 2847)

    SELECT 
        <COLS>
        FROM __Data JOIN [dbo].[auction_online_lots] ON [dbo].[auction_online_lots].[id] = __Data.[id] 
        WHERE [dbo].[auction_online_lots].[saleid] = 2847 AND [_#_] BETWEEN 3 AND 5

which is wrong as its missing the order by on the second select

I should expect this

1394 1393 1392

but get

1392 1393 1394

If skip is 0 then the correct sql is generated as its slightly different SQL to when skip is a positive number

The bit its missing on the second select query is

ORDER BY [dbo].[auction_online_lots].[sortno] DESC