This is not the typical question to get the “Use .Limit(…)” answer
I have the following problem.
var expression = connection.From<Dto>();
expression = expression.Where(x => x.UserId == request.UserId).Limit(pagingInfo.Skip, pagingInfo.PageSize));
expression = expression.OrderByDescending(x => x.CreatedOn);
Now assume that those Dtos are all created at the same time and table looks like this (for obvious reasons I have cut down guids to the first 8 chars)
9E86BA04 name2 NULL user1 2017-06-25 23:52:59.323 52DE4F06 name2 NULL user1 2017-06-25 23:52:59.323 4885EB18 name3 NULL user1 2017-06-25 23:52:59.323 0E984FF2 name4 NULL user1 2017-06-25 23:52:59.323
Given the same user id, when page = 1, page size = 2 (i.e. Skip = 0, Rows = 2) query is
SELECT TOP 2 * (actual columns here)
FROM "schema"."dtos"
WHERE ("user_id" = 'user1')
ORDER BY "created_on" DESC
This returns
4885EB18 name3 NULL user1 2017-06-25 23:52:59.323
0E984FF2 name4 NULL user1 2017-06-25 23:52:59.323
But I expected the other two rows to be returned (i.e. the first two from the table). I know why this is happening - it has to do with how SQL Server treats TOP queries but I expected ORMLite to have this covered.
SELECT * FROM (SELECT (actual columns here), ROW_NUMBER() OVER (
ORDER BY "created_on" DESC) As RowNum
FROM "schema"."dtos"
WHERE ("user_id" = 'AD0CED1D-788E-452D-8C4E-B86EBC4DF587')) AS RowConstrainedResult WHERE RowNum > 2 AND RowNum <= 4
This returns
52DE4F06 name2 NULL user1 2017-06-25 23:52:59.323 4885EB18 name3 NULL user1 2017-06-25 23:52:59.323
Any ideas on how to sort out this randomness?
Thanks
Yannis