CODEWITHSUNDEEP
c#linqc#-4.0
Vishal Anand
Vishal Anand

Reputation: 1471

LINQ GroupBy then OrderBy Behaviour

I have a scenario where I had to first group by and then sort each group. I came up with following working query :

applicants = context.Applicants
                    .OrderBy(app => app.Id).Skip((pageNumber - 1)*defaultPageSize)
                    .Take(defaultPageSize)
                    .GroupBy(app => app.PassportNumber)
                    .Select(g => g.OrderBy(d => d.IsEndorseChild)).ToList()
                    .SelectMany(grouping => grouping)
                    .ToList();

But this doesn't work :

applicants = context.Applicants
                    .OrderBy(app => app.Id).Skip((pageNumber - 1)*defaultPageSize)
                    .Take(defaultPageSize)
                    .GroupBy(app => app.PassportNumber)
                    .Select(g => g.OrderBy(d => d.IsEndorseChild))
                    .SelectMany(grouping => grouping)
                    .ToList();

First query produces result(these are the Id of the applicants returned) like:
7, 10, 8, 2, 9, 6, 5, 3, 4, 1
but second query produces:
7, 10, 8, 2, 9, 6, 3, 4, 5, 1

I am not sure why is this happening and what is the difference between the queries.
What exactly am I missing here ?

EDIT : Example Input and Expected Output :

Applicant 1 => ID : 1
               IsEndorsed : 0
               Passport : ABC123

Applicant 2 => ID : 2
               IsEndorsed : 1
               Passport : ABC123


Applicant 3 => ID : 3
               IsEndorsed : 0
               Passport : ABC1234

Applicant 4 => ID : 4
               IsEndorsed : 0
               Passport : A1234

Applicant 5 => ID : 5
               IsEndorsed : 1
               Passport : PQR123
Applicant 6 => ID : 6
               IsEndorsed : 1
               Passport : PQR123
Applicant 7 => ID : 7
               IsEndorsed : 0
               Passport : PQR123


Expected output  : (Grp by Passport Number and in each group IsEndorsed = 0 is at top)
----------------------------------------------

Applicant 1 => ID : 1
               IsEndorsed : 0
               Passport : ABC123

Applicant 2 => ID : 2
               IsEndorsed : 1
               Passport : ABC123

Applicant 3 => ID : 3
               IsEndorsed : 0
               Passport : ABC1234

Applicant 4 => ID : 4
               IsEndorsed : 0
               Passport : AX1234

Applicant 7 => ID : 7
               IsEndorsed : 0
               Passport : PQR123

Applicant 5 => ID : 5
               IsEndorsed : 1
               Passport : PQR123
Applicant 6 => ID : 6
               IsEndorsed : 1
               Passport : PQR123

Upvotes: 3

Views: 403

Answers (1)

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174369

You are ordering by a boolean value. As soon as two of the values within one group have the same value of IsEndorseChild their order relative to each other is generally undefined.

This OrderBy is actually being translated to a SQL ORDER BY and the result therefore also depends on the ORDER BY implementation of the database you are using. It could either be stable or unstable.
Unstable means that it can return rows with the same ordered value in any order.
Stable means that it will return them in the same order as they were input.

SQL Server for example has an unstable sort: Is SQL order by clause guaranteed to be stable ( by Standards)

So, my conclusion is, that the ToList call - the only difference in both of your queries - has no impact on the result. The difference is simply caused by the unstable sort.

Upvotes: 8

Related Questions