How to group by multiple columns in LINQ

Key takeaways:
LINQ allows grouping data by multiple columns using anonymous types for better multi-criteria analysis.
Grouping by multiple columns is done with the GroupBy() method or group by clause in LINQ.
The group by clause is faster, while GroupBy() offers more flexibility but may be slower.

LINQLanguage Integrated Query is a powerful method for accessing data from different sources, such as arrays, databases, XML documents, etc. With LINQ, we can merge all the queries, resulting in a more efficient technique for data retrieval. LINQ queries return results as objects, allowing an object-oriented approach to be applied to the result set and eliminating the need to convert various result formats into objects.

The solution to multi-criteria analysis

The problem of grouping data by multiple columns arises when we need to organize and analyze a data collection based on multiple criteria or attributes. For example, we have a list of customer orders and want to explore how many orders were placed by customers in each city and month. To do this, we need to group the data into two columns, the “city” and the “month,” and then count the number of orders in each group.

Without the ability to group data by multiple columns, we would have to manually sort and filter the data to achieve the same result, which can be time-consuming and error-prone. Grouping data by multiple columns allows us to organize and analyze extensive data sets more efficiently and accurately and to extract insights and patterns that would be difficult to discern otherwise.

Grouping data by multiple columns

Using LINQ, we can aggregate data based on several columns by constructing an anonymous type with the relevant columns. We can then send this anonymous type to the GroupBy() method, which will group the data by those columns.

Note: The group by clause can also be used to group data by multiple columns when using anonymous types.

Syntax

In the code above:

source: This is the data source that we want to group.
Column1, Column2, ...: These are the column names that we want to group by. We can include as many columns as we want, separated by commas.
x: This is a placeholder variable that represents each item in the data source.
new { x.Column1, x.Column2, ... }: This is an anonymous type that contains the columns we want to group by.

Code example

The following code snippet defines a Teacher class and groups its objects by multiple columns:

using System;
using System.Collections.Generic;
using System.Linq;
public class Teacher
{
    public string name { get; set; }
    public string subject { get; set; }
    public int age { get; set; }
}
public class Program
{
    public static void Main()
    {
        // Initializing List of Teacher objects
        List<Teacher> teachers = new List<Teacher>()
        {
            new Teacher { name = "Jaime Escalante", subject = "Game Development", age = 35 },
            new Teacher { name = "Sarah", subject = "Game Development", age = 35 },
            new Teacher { name = "Toni Morrison", subject = "Game Development", age = 35 },
            new Teacher { name = "Spike Lee", subject = "Web Development", age = 45 }
        };
        // Group by multiple columns
        var grouped_teachers = teachers
            .GroupBy(t => new { t.subject, t.age })
            .Select(g => new
            {
                subject = g.Key.subject,
                age = g.Key.age,
                count = g.Count(), //number of teachers in the group
                names = string.Join(", ", g.Select(t => t.name)) // teacher names
            });
        // Output
        foreach (var group in grouped_teachers)
        {
            Console.WriteLine($"Subject: {group.subject}, Age: {group.age}, Count: {group.count}, Names: {group.names}");
        }
    }
}

Code explanation

Lines 1–3: We import the necessary namespaces.
Lines 5–10: We define a Teacher class with name, subject, and age properties.
Lines 17–23: We initialize a list of Teacher objects and assign them to the teachers variable.
Lines 26–34: We group the teachers list by subject and age and then create a new object for each group containing subject, age, count, and names. The resulting objects are stored in a new variable called grouped_teachers.
Lines 37–40: The code loops through the grouped_teachers variable and outputs each group’s subject, age, count, and names properties.

Grouping the query results

We can also group the LINQ query results by multiple properties. The code below shows how we can group the results:

using System;
using System.Linq;
public class Program
{
    public static void Main()
    {
        string[] words = { "apple", "banana", "apricot", "blueberry", "banana", "cherry", "date" };
        var groupedWords = from word in words
                           group word by new { Length = word.Length, StartsFromA = word.StartsWith("a") } into wordGroup
                           select new
                           {
                               Length = wordGroup.Key.Length,
                               StartsFromA = wordGroup.Key.StartsFromA,
                               Count = wordGroup.Count(),
                               Words = string.Join(", ", wordGroup)
                           };
        foreach (var group in groupedWords)
        {
            Console.WriteLine($"Length: {group.Length}, Starts from 'a': {group.StartsFromA}, Count: {group.Count}, Words: {group.Words}");
        }
    }
}

Lines 11–18: We use a LINQ query syntax to group the words from the array words by the anonymous type { Length = word.Length, StartsFromA = word.StartsWith("a") }.
- The group by clause groups the words based on their length (Length) and whether they start with the letter “a” (StartsFromA).
- We then use select to project each group into a new anonymous type.

Performance considerations

When working with LINQ, developers can choose between using the group by clause or the GroupBy() method to group data by multiple columns. Both options produce the same results, but choosing between them can significantly impact performance:

The group by clause is faster and more efficient than the GroupBy() method because it is directly translated into SQL by the LINQ provider.
The GroupBy() method provides more advanced features and flexibility but can also be slower because it requires more processing power.

Tips for optimizing performance

There are some tips for optimizing performance when grouping data by multiple columns in LINQ:

Use the group by clause whenever possible.
Avoid unnecessary data retrieval.
Optimize the underlying database schema.
Use appropriate data types.
Use deferred execution.
Consider parallel processing.

Try it yourself

The picture below has five different cards, each showing the steps to group a collection of objects by multiple properties using LINQ. They are not in the correct order. Try fixing the sequence of steps.

Conclusion

Mastering the art of grouping data by multiple columns in LINQ can significantly enhance data analysis capabilities and maximize our productivity as developers. LINQ’s capabilities for grouping data make it a powerful tool for data analysis and manipulation and a valuable addition to any .NET developer's toolkit The .NET developer's toolkit is a collection of tools, libraries, and frameworks provided by Microsoft..

Ready to unlock the power of LINQ in C#? Check out our “Getting Started with LINQ in C#” course and master lambda expressions, common methods, and more to write cleaner, more efficient code

Frequently asked questions

Haven’t found what you were looking for? Contact Us

Can you group by multiple columns at once?

Yes, we can use multiple columns in a group by clause by creating an anonymous type to group by.

How to order by multiple columns in C#?

Use ThenBy for additional sorting after the initial OrderBy.

var sorted = data.OrderBy(x => x.Column1).ThenBy(x => x.Column2);

How to use grouping in LINQ?

Use the GroupBy method to group elements based on a key.

How do I group multiple rows into one?

Use GroupBy and aggregate values with methods like Sum, Average, or ToList.

How to sort a list of objects in C# using LINQ?

Use OrderBy or OrderByDescending to sort the list.

Free AI Mock Interviews

Coding Interview

Coding PatternsFree Interview

Gain insights and practical experience with coding patterns through targeted MCQs and coding problems, designed to match and challenge your expertise level.

System Design

YouTubeFree Interview

Learn to design a video streaming platform like YouTube by tackling functional and non-functional requirements, core components, and high-level to detailed design challenges.

Free Resources