C# Entity Framework sorting and paging giving unexpected results

Question

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 1

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();

C# Entity Framework sorting and paging giving unexpected results

Sign up