2

This is an extension of already answered question by Jon Skeet that you can find here.

The desired result is following:

A 100
A 80
B 80
B 50
B 40
C 70
C 30

considering you have following class:

public class Student
{
    public string Name { get; set; } 
    public int Grade { get; set; }
}

to get to the result (in ideal scenario) can be done with Jon Skeet's answer: 

var query = grades.GroupBy(student => student.Name)
                  .Select(group => 
                        new { Name = group.Key,
                              Students = group.OrderByDescending(x => x.Grade) })
                  .OrderBy(group => group.Students.FirstOrDefault().Grade);

However in my case I have to support paging in my query as well. This means performing SelectMany() and then do Skip() and Take(). But to do Skip() you have to apply OrderBy(). This is where my ordering breaks again as I need to preserve the order I get after SelectMany().

How to achieve this?

var query = grades.GroupBy(student => student.Name)
                  .Select(group => 
                        new { Name = group.Key,
                              Students = group.OrderByDescending(x => x.Grade) })
                  .OrderBy(group => group.Students.FirstOrDefault().Grade).SelectMany(s => s.Students).OrderBy(something magical that doesn't break ordering).Skip(s => skip).Take(t => take);

I know I could manually sort again the records when my query is materialised but I would like to avoid this and do all of it in one SQL query that is translated from LINQ.

Jakub Holovsky
  • 6,543
  • 10
  • 54
  • 98

2 Answers2

3

You can take another approach using Max instead of ordering each group and taking the first value. After that you can order by max grade, name (in case two students have the same max grade) and grade:

var query = c.Customers
    .GroupBy(s => s.Name, (k, g) => g
        .Select(s => new { MaxGrade = g.Max(s2 => s2.Grade), Student = s }))
    .SelectMany(s => s)
    .OrderBy(s => s.MaxGrade)
    .ThenBy(s => s.Student.Name)
    .ThenByDescending(s => s.Student.Grade)
    .Select(s => s.Student)
    .Skip(toSkip)
    .Take(toTake)
    .ToList();

All these methods are supported by EF6 so you should get your desired result.

arekzyla
  • 2,878
  • 11
  • 19
  • It's not gonna work as you have to have OrderBy before the Skip clause. – Jakub Holovsky May 31 '18 at 20:34
  • @JakubHolovsky and there is `OrderBy` before `Skip` but it doesn't have to be right before it. This restriction exists in SQL because for `OFFSET FETCH` clause there must be `ORDER BY` before it and for the SQL translated from the above query it's true. Just test it. It's gonna work. – arekzyla Jun 01 '18 at 00:16
  • It's indeed working. Just a small correction - the first order (`.OrderBy(s => s.MaxGrade)`) must be descending. – Ivan Stoev Jun 01 '18 at 06:17
  • 1
    Does work indeed, pretty amazing, thanks. Hope someone will understand it someday when they read it after me. – Jakub Holovsky Jun 01 '18 at 07:53
0

Just re-index your list results and remove the index before returning.

var query = grades.GroupBy(student => student.Name)
       .Select(group => 
               new { Name = group.Key, 
                    Students = group.OrderByDescending(x => x.Grade)
                   })
       .OrderBy(group => group.Students.FirstOrDefault().Grade)
       .SelectMany(s => s.Students)
       .Select((obj,index) => new {obj,index})
       .OrderBy(newindex => newindex.index)
       .Skip(s => skip).Take(t => take)
       .Select(final=> final.obj);
David
  • 231
  • 1
  • 8