LINQ to SQL - order by, group by and order by each group with skip and take

c# entity-framework-6 linq linq-to-sql

Question

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.

1
1
5/31/2018 6:29:50 PM

Accepted Answer

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.

2
5/31/2018 7:55:05 PM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow