How to: Group Query Results (C# Programming Guide)
Grouping is one of the most powerful capabilities of LINQ. The following examples show how to group data in various ways:
By a single property.
By the first letter of a string property.
By a computed numeric range.
By Boolean predicate or other expression.
By a compound key.
In addition, the last two queries project their results into a new anonymous type that contains only the student's first and last name. For more information, see the group clause (C# Reference).
All the examples in this topic use the following helper classes and data sources.
public class StudentClass { #region data protected enum GradeLevel { FirstYear = 1, SecondYear, ThirdYear, FourthYear }; protected class Student { public string FirstName { get; set; } public string LastName { get; set; } public int ID { get; set; } public GradeLevel Year; public List<int> ExamScores; } protected static List<Student> students = new List<Student> { new Student {FirstName = "Terry", LastName = "Adams", ID = 120, Year = GradeLevel.SecondYear, ExamScores = new List<int>{ 99, 82, 81, 79}}, new Student {FirstName = "Fadi", LastName = "Fakhouri", ID = 116, Year = GradeLevel.ThirdYear, ExamScores = new List<int>{ 99, 86, 90, 94}}, new Student {FirstName = "Hanying", LastName = "Feng", ID = 117, Year = GradeLevel.FirstYear, ExamScores = new List<int>{ 93, 92, 80, 87}}, new Student {FirstName = "Cesar", LastName = "Garcia", ID = 114, Year = GradeLevel.FourthYear, ExamScores = new List<int>{ 97, 89, 85, 82}}, new Student {FirstName = "Debra", LastName = "Garcia", ID = 115, Year = GradeLevel.ThirdYear, ExamScores = new List<int>{ 35, 72, 91, 70}}, new Student {FirstName = "Hugo", LastName = "Garcia", ID = 118, Year = GradeLevel.SecondYear, ExamScores = new List<int>{ 92, 90, 83, 78}}, new Student {FirstName = "Sven", LastName = "Mortensen", ID = 113, Year = GradeLevel.FirstYear, ExamScores = new List<int>{ 88, 94, 65, 91}}, new Student {FirstName = "Claire", LastName = "O'Donnell", ID = 112, Year = GradeLevel.FourthYear, ExamScores = new List<int>{ 75, 84, 91, 39}}, new Student {FirstName = "Svetlana", LastName = "Omelchenko", ID = 111, Year = GradeLevel.SecondYear, ExamScores = new List<int>{ 97, 92, 81, 60}}, new Student {FirstName = "Lance", LastName = "Tucker", ID = 119, Year = GradeLevel.ThirdYear, ExamScores = new List<int>{ 68, 79, 88, 92}}, new Student {FirstName = "Michael", LastName = "Tucker", ID = 122, Year = GradeLevel.FirstYear, ExamScores = new List<int>{ 94, 92, 91, 91}}, new Student {FirstName = "Eugene", LastName = "Zabokritski", ID = 121, Year = GradeLevel.FourthYear, ExamScores = new List<int>{ 96, 85, 91, 60}} }; #endregion //Helper method, used in GroupByRange. protected static int GetPercentile(Student s) { double avg = s.ExamScores.Average(); return avg > 0 ? (int)avg / 10 : 0; } public void QueryHighScores(int exam, int score) { var highScores = from student in students where student.ExamScores[exam] > score select new {Name = student.FirstName, Score = student.ExamScores[exam]}; foreach (var item in highScores) { Console.WriteLine("{0,-15}{1}", item.Name, item.Score); } } } public class Program { public static void Main() { StudentClass sc = new StudentClass(); sc.QueryHighScores(1, 90); // Keep the console window open in debug mode. Console.WriteLine("Press any key to exit"); Console.ReadKey(); } }
The following example shows how to group source elements by using a single property of the element as the group key. In this case the key is a string, the student's last name. It is also possible to use a substring for the key. The grouping operation uses the default equality comparer for the type.
Paste the following method into the StudentClass class. Change the calling statement in the Main method to sc.GroupBySingleProperty().
public void GroupBySingleProperty() { Console.WriteLine("Group by a single property in an object:"); // Variable queryLastNames is an IEnumerable<IGrouping<string, // DataClass.Student>>. var queryLastNames = from student in students group student by student.LastName into newGroup orderby newGroup.Key select newGroup; foreach (var nameGroup in queryLastNames) { Console.WriteLine("Key: {0}", nameGroup.Key); foreach (var student in nameGroup) { Console.WriteLine("\t{0}, {1}", student.LastName, student.FirstName); } } } /* Output: Group by a single property in an object: Key: Adams Adams, Terry Key: Fakhouri Fakhouri, Fadi Key: Feng Feng, Hanying Key: Garcia Garcia, Cesar Garcia, Debra Garcia, Hugo Key: Mortensen Mortensen, Sven Key: O'Donnell O'Donnell, Claire Key: Omelchenko Omelchenko, Svetlana Key: Tucker Tucker, Lance Tucker, Michael Key: Zabokritski Zabokritski, Eugene */
The following example shows how to group source elements by using something other than a property of the object for the group key. In this example, the key is the first letter of the student's last name.
Paste the following method into the StudentClass class. Change the calling statement in the Main method to sc.GroupBySubstring().
public void GroupBySubstring() { Console.WriteLine("\r\nGroup by something other than a property of the object:"); var queryFirstLetters = from student in students group student by student.LastName[0]; foreach (var studentGroup in queryFirstLetters) { Console.WriteLine("Key: {0}", studentGroup.Key); // Nested foreach is required to access group items. foreach (var student in studentGroup) { Console.WriteLine("\t{0}, {1}", student.LastName, student.FirstName); } } } /* Output: Group by something other than a property of the object: Key: A Adams, Terry Key: F Fakhouri, Fadi Feng, Hanying Key: G Garcia, Cesar Garcia, Debra Garcia, Hugo Key: M Mortensen, Sven Key: O O'Donnell, Claire Omelchenko, Svetlana Key: T Tucker, Lance Tucker, Michael Key: Z Zabokritski, Eugene */
The following example shows how to group source elements by using a numeric range as a group key. The query then projects the results into an anonymous type that contains only the first and last name and the percentile range to which the student belongs. An anonymous type is used because it is not necessary to use the complete Student object to display the results. GetPercentile is a helper function that calculates a percentile based on the student's average score. The method returns an integer between 0 and 10.
//Helper method, used in GroupByRange. protected static int GetPercentile(Student s) { double avg = s.ExamScores.Average(); return avg > 0 ? (int)avg / 10 : 0; }
Paste the following method into the StudentClass class. Change the calling statement in the Main method to sc.GroupByRange().
public void GroupByRange() { Console.WriteLine("\r\nGroup by numeric range and project into a new anonymous type:"); var queryNumericRange = from student in students let percentile = GetPercentile(student) group new { student.FirstName, student.LastName } by percentile into percentGroup orderby percentGroup.Key select percentGroup; // Nested foreach required to iterate over groups and group items. foreach (var studentGroup in queryNumericRange) { Console.WriteLine("Key: {0}", (studentGroup.Key * 10)); foreach (var item in studentGroup) { Console.WriteLine("\t{0}, {1}", item.LastName, item.FirstName); } } } /* Output: Group by numeric range and project into a new anonymous type: Key: 60 Garcia, Debra Key: 70 O'Donnell, Claire Key: 80 Adams, Terry Feng, Hanying Garcia, Cesar Garcia, Hugo Mortensen, Sven Omelchenko, Svetlana Tucker, Lance Zabokritski, Eugene Key: 90 Fakhouri, Fadi Tucker, Michael */
The following example shows how to group source elements by using a Boolean comparison expression. In this example, the Boolean expression tests whether a student's average exam score is greater than 75. As in previous examples, the results are projected into an anonymous type because the complete source element is not needed. Note that the properties in the anonymous type become properties on the Key member and can be accessed by name when the query is executed.
Paste the following method into the StudentClass class. Change the calling statement in the Main method to sc.GroupByBoolean().
public void GroupByBoolean() { Console.WriteLine("\r\nGroup by a Boolean into two groups with string keys"); Console.WriteLine("\"True\" and \"False\" and project into a new anonymous type:"); var queryGroupByAverages = from student in students group new { student.FirstName, student.LastName } by student.ExamScores.Average() > 75 into studentGroup select studentGroup; foreach (var studentGroup in queryGroupByAverages) { Console.WriteLine("Key: {0}", studentGroup.Key); foreach (var student in studentGroup) Console.WriteLine("\t{0} {1}", student.FirstName, student.LastName); } } /* Output: Group by a Boolean into two groups with string keys "True" and "False" and project into a new anonymous type: Key: True Terry Adams Fadi Fakhouri Hanying Feng Cesar Garcia Hugo Garcia Sven Mortensen Svetlana Omelchenko Lance Tucker Michael Tucker Eugene Zabokritski Key: False Debra Garcia Claire O'Donnell */
The following example shows how to use an anonymous type to encapsulate a key that contains multiple values. In this example, the first key value is the first letter of the student's last name. The second key value is a Boolean that specifies whether the student scored over 85 on the first exam. You can order the groups by any property in the key.
Paste the following method into the StudentClass class. Change the calling statement in the Main method to sc.GroupByCompositeKey().
public void GroupByCompositeKey() { var queryHighScoreGroups = from student in students group student by new { FirstLetter = student.LastName[0], Score = student.ExamScores[0] > 85 } into studentGroup orderby studentGroup.Key.FirstLetter select studentGroup; Console.WriteLine("\r\nGroup and order by a compound key:"); foreach (var scoreGroup in queryHighScoreGroups) { string s = scoreGroup.Key.Score == true ? "more than" : "less than"; Console.WriteLine("Name starts with {0} who scored {1} 85", scoreGroup.Key.FirstLetter, s); foreach (var item in scoreGroup) { Console.WriteLine("\t{0} {1}", item.FirstName, item.LastName); } } } /* Output: Group and order by a compound key: Name starts with A who scored more than 85 Terry Adams Name starts with F who scored more than 85 Fadi Fakhouri Hanying Feng Name starts with G who scored more than 85 Cesar Garcia Hugo Garcia Name starts with G who scored less than 85 Debra Garcia Name starts with M who scored more than 85 Sven Mortensen Name starts with O who scored less than 85 Claire O'Donnell Name starts with O who scored more than 85 Svetlana Omelchenko Name starts with T who scored less than 85 Lance Tucker Name starts with T who scored more than 85 Michael Tucker Name starts with Z who scored more than 85 Eugene Zabokritski */
Copy and paste each method that you want to test into the StudentClass class. Add a calling statement for the method to the Main method and press F5.
When you adapt these methods to your own application, remember that LINQ requires version 3.5 or 4 of the .NET Framework, and that the project must contain a reference to System.Core.dll and a using directive for System.Linq. LINQ to SQL, LINQ to XML, and LINQ to DataSet types require additional using directives and references. For more information, see How to: Create a LINQ Project.
