Manish Gupta Manish Gupta - 4 months ago 12
C# Question

C# Entity Framework sorting and paging giving unexpected results

When I am using Entity Framework with

OrderBy
and
Skip
and
Take
, there are duplicate records and some records are not displaying.

This is the code:

jobs = context.Jobs.Include("Company").
OrderBy(x => x.Company.Name).
Skip((page - 1) * PageSize).
Take(PageSize).ToList();


However if I order by some
Job
property like
x => x.Title
, there aren't any issues. Issue is only when sorting with some related entity of job. In my code
Job
and
Company
have one-to-many relationship.

Please help with this.

Thanks

Answer

Your query doesn't completely define the order of resulting rows.

Say we have a table:

Id Name
1  Bar
2  Foo
3  Bar

When ordering by Name, the following result sets will be possible:

Id Name
1  Bar
3  Bar
2  Foo

and

Id Name
3  Bar
1  Bar
2  Foo

Each call may return any of this sets, so if we have page size 1 it is possible to get strange result like this:

Id Name
1  Bar // 1st set
1  Bar // 2nd set
2  Foo // 1st set

Adding some unique attribute (PK in most cases) to your key will fix this behavior.

Given key Name, Id there's only one possible result:

Id Name
1  Bar
3  Bar
2  Foo

So your query should look like this (assuming JobId is PK):

jobs = context.Jobs.Include("Company").
            OrderBy(x => x.Company.Name).
            ThenBy(x => x.JobId).
            Skip((page - 1) * PageSize).
            Take(PageSize).ToList();