Here we will see how we can sort rows in a group using LINQ.Also we will select only the top n rows in a group.
This is useful in scenario where we are working on data which consists of different groups.For Sorting and selecting top n rows in a group using in LINQ in C# we will use OrderBy,ThenBy and Where operators of LINQ.
For example there could be group of students in different grades in a school.If we want to find the top 3 students who have scored the maximum marks in a grade then we need to group ,sort and select the top three students:
We have defined the student class as:
public class Student
{
public string Grade
{
get;
set;
}
public string Name
{
get;
set;
}
public int TotalMarks
{
get;
set;
}
}
We have added a sample list of students as:
List<Student> students = new List<Student>()
{
new Student() {Grade = "1", Name = "Amit", TotalMarks = 200},
new Student() {Grade = "2", Name = "Greg", TotalMarks = 150},
new Student() {Grade = "1", Name = "Marc", TotalMarks = 100},
new Student() {Grade = "2", Name = "Rohit", TotalMarks = 125},
new Student(){Grade = "1", Name = "John", TotalMarks = 98},
new Student() {Grade = "1", Name = "Priya", TotalMarks = 95},
new Student() {Grade = "2", Name = "Anthony", TotalMarks = 120}
};
To perform the sorting and grouping we need to follow the below steps:
We perform sorting in LINQ using the orderby operator as:
students.OrderBy(x => x.Grade)
this will order the students based on grade.So we will have a list of grade 1 students followed by a list of grade 2 students.
To sort by multiple columns we use the ThenBy operator as:
students.OrderBy(x => x.Grade).ThenByDescending(x => x.TotalMarks)
this will order the students in a particular grade based on total marks.So we will have list of students in grade 1 sorted by total marks followed by grade 2 students.
To select only the first n rows we will use the where operator:
students.OrderBy(x => x.Grade).ThenByDescending(x => x.TotalMarks)
.Where(x =>
{
if (initialGrade == x.Grade) { count++; } else { count = 1; } initialGrade = x.Grade; if (count <=3) return true;
else
return false;
;} );
Here we are ensuring that we select only the top 3 students in a grade.
Following is the complete code of the application:
public class Student
{
public string Grade
{
get;
set;
}
public string Name
{
get;
set;
}
public int TotalMarks
{
get;
set;
}
}
List<Student> students = new List<Student>()
{
new Student() {Grade = "1", Name = "Amit", TotalMarks = 200},
new Student() {Grade = "2", Name = "Greg", TotalMarks = 150},
new Student() {Grade = "2", Name = "Rohit", TotalMarks = 125},
new Student() {Grade = "1", Name = "Marc", TotalMarks = 100},
new Student(){Grade = "1", Name = "John", TotalMarks = 98},
new Student() {Grade = "1", Name = "Priya", TotalMarks = 95},
new Student() {Grade = "2", Name = "Anthony", TotalMarks = 120}
};
string initialGrade=students[0].Grade;
int count=0;
var topSellingItems = students.OrderBy(x => x.Grade).ThenByDescending(x => x.TotalMarks)
.Where(x =>
{
if (initialGrade == x.Grade) { count++; } else { count = 1; } initialGrade = x.Grade; if (count <=3) return true;
else
return false;
;} );
//GroupBy<Brand, string>(x => x.Name).SelectMany(x => x).OrderBy(x=>x.Qty);
foreach(var item in topSellingItems)
{
Console.WriteLine("name {0} ,grade {1} marks{2}",item.Name, item.Grade, item.TotalMarks);
}
On executing above program we get the following output:
Leave a Reply