Duplicate rows when using orderby, Skip() and Take() with LINQ

Question

I'm using the following code to query my database:

private const int PAGE_SIZE = 10;

public static IList<Image> GetTopImagesForUser(String connectionString, int userID, int page)
{
    dbDataContext db = new dbDataContext(connectionString);
    var images = (from p in db.Images
                  where (p.SubmitterUserIndex == userID &&
                         p.URL != "none" &&
                         p.ThumbURL != "none")
                  orderby p.Rep descending
                  select p).Skip(page * PAGE_SIZE).Take(PAGE_SIZE);
    /* snip */
    return topImages;
}

If I call this code with a page of 0, everything works the way I want it to - I get a nicely ordered list, 10 results, everything is correct.

If I call this code with a page of 1, however, rows that were in page 0 end up in page 1. I can't even begin to understand why. I've checked my database for duplicate rows, none. I've checked to make sure every row's URL and ThumbURL are not "none". That's not the problem either. I've checked to make sure page is what I expect it to be when I call this method, and it is always what I expect it to be.

What really baffles me is that the following method, which differs from the first method only in the orderby clause, works completely as expected.

public static IList<Image> GetAllImagesForUser(String connectionString, int userID, int page)
{
    dbDataContext db = new dbDataContext(connectionString);
    var images = (from p in db.Images
                  where (p.SubmitterUserIndex == userID &&
                         p.URL != "none" &&
                         p.ThumbURL != "none")
                  orderby p.SubmitTime descending
                  select p).Skip(page * PAGE_SIZE).Take(PAGE_SIZE);
    /* snip */
    return allImages;
}

Has anyone run into something like this? Is there a different form that my query should take to do what I want it to do? I'm not sure what I could be missing.

Answer 1

When you say you don't have any "duplicate rows" - do any of the returned rows have the same Rep value? If so, the ordering within that will be indeterminate.

Try

orderby p.Rep descending, p.SubmitTime

(or some other ordering which will be completely determined) to get a more predictable result.

Also, is this LINQ to SQL? If so, you should look at the generated SQL to see what's happening.

Answer 2

The Rep column you are sorting by in the first case probably contains duplicates. When you select top 10 from a list containing 100 rows with value 1, there's no guarantee which rows will be returned. Change the order by clause to include a unique column.

Answer 3

I have been baffled by similar problems in paging when not using LINQ, but just using a SELECT query (in a sproc). I think that the issue may be that the Rep field is not unique, but the SubmitTime field, being a date, may be unique.

I would suggest that you try a second field after Rep to make the Order By always return the results in the same order. It can be any field that will make the records unique; you don't have to be using it in the results.