How to group by multiple columns in LINQ

LINQLanguage Integrated Query is a powerful method for accessing data from different sources like 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

In LINQ, we can group data by more than one column by creating an anonymous type that contains the columns we want to group by. We can then pass this anonymous type to the GroupBy() method to group the data based on those columns.

Note: We can also group data by multiple columns using anonymous types with the group by clause.

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

Line 1–3: We import the necessary namespaces.
Line 5–10: We define a Teacher class with name, subject, and age properties.
Line 17–23: We initialize a list of Teacher objects and assign them to the teachers variable.
Line 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.
Line 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.

Code example

The code below shows how we can group the result:

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

Explanation

Line 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.

How to group by multiple columns in LINQ

The solution to multi-criteria analysis

Grouping data by multiple columns

Syntax

Code example

Code explanation

Grouping the query results

Code example

Explanation

Performance considerations

Tips for optimizing performance

Try it yourself

Conclusion