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 a follow-up to a query that Jon Skeet previously responded to, which you may locate here.

The intended outcome is as follows:

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

if you have the following class:

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

With Jon Skeet's response, it is possible to reach the outcome (in the best case scenario)

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

But in my situation, I also need to support paging in my query. Performing SelectMany(), followed by Skip(), and Take() is what is meant by this. However, you must use OrderBy() in order to execute Skip(). As I need to maintain the order I get after SelectMany(), here is where my ordering falls down once again.

How can this be done?

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

When my query is materialised, I am aware that I could manually reorder the data, but I would want to avoid this and complete everything with a single SQL query that is translated from LINQ.

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

Accepted Answer

You can take another approach usingMax rather than sorting each group and selecting the highest value. Then, if two students have the same maximum grade, you may sort by name, grade, and maximum 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

ZZZ_tmp


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