How to Group by Multiple Columns in LINQ Queries Using C#

1. Introduction to LINQ
2. Group by Multiple Columns in LINQ Queries Using C#

This article provides a brief introduction to LINQ queries using C#. Furthermore, it discusses how to group results by multiple columns using LINQ Queries.

If you are already familiar with LINQ, you may safely skip the introduction section.

Introduction to LINQ

LINQ, which stands for Language Integrated Query, is a method that provides us with a uniform method for accessing data from different data sources such as databases, arrays, XML, and much more. It can integrate all the queries in itself.

When developers develop an application, they need some extra knowledge other than a programming language to acquire data from data sources. For example, if your data source is a database, a programmer needs knowledge of SQL.

Similarly, the programmer should know how to parse XML if the data source is an XML document. Whereas with LINQ, you only need the knowledge about LINQ to acquire data from all these data sources.

LINQ to Objects

LINQ to Objects means you can use LINQ queries to get the data from an in-memory data structure. This data structure can be user-defined as well as some DotNet-defined APIs.

The only requirement for the data structure is that it should return the collection in the IEnummerable<T> type.

Let’s consider an example of LINQ to Objects:

using System;
using System.Linq;

class MyProgram {
  static void Main() {
    string[] list = { "apple", "ball", "aeroplane", "beautiful", "ancient" };

    var starts = from w in list
                 where w.StartsWith("a") select w;
    // Print words
    foreach (var word in starts) {
      Console.WriteLine(word);
    }
  }
}

Output:

apple
aeroplane
ancient

In the above code snippet, we have made a LINQ query to access the data from a string array. In this way, this array can be replaced with any data structure.

The benefit of the LINQ query is that its syntax will remain the same whether you change the data structure at the back. The query will remain the same; this is the uniformity provided by LINQ.

LINQ to SQL

For LINQ to ADO.Net, there are 3 sub-components. But, we will be focusing on LINQ to SQL mainly.

LINQ to SQL allows us to convert the relational database into objects. It makes data operations much simpler and faster.

The process it follows is that it first connects to the database, converts LINQ queries to SQL queries, and then runs those SQL Queries. The result returned from the SQL is converted back to objects constructed by LINQ and then returned to the user.

LINQ also traces the changes in the data of the objects and automatically synchronizes those changes in the database.

When you create LINQ to SQL component in your project, it automatically creates the classes for all the database tables. After that, you need to code for the connection and database operations.

Suppose we have a table for storing the data of employees of a company. The table name is Emp and contains the fields: Id, Name, and Email.

To use it as LINQ queries, consider the following code snippet:

using System;
using System.Linq;

namespace MyLINQExample {
  class LINQExample {
    static void Main(string[] args) {
      string connectString =
          System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"]
              .ToString();
      LinqToSQLDataContext db = new LinqToSQLDataContext(connectString);
      Emp newEmp = new Emp();
      newEmp.name = "John";
      newEmp.email = "john@abccompany.com";
      newEmp.id = 3;
      // Add this new employee to the database
      db.Emps.InsertOnSubmit(newEmp);

      // To save changes in the database
      db.SubmitChanges();

      // Get the data of inserted employee
      Emp e = db.Emps.FirstOrDefault(e ⇒e.name.Equals("John"));

      Console.WriteLine("Emp Id = {0} , Name = {1}, Email = {2}", e.id, e.name, e.email);

      Console.WriteLine("\nPress any key to continue.");
      Console.ReadKey();
    }
  }
}

In the above code, we connected to the database, created a data context object, and then made our query run on the database.

Output:

Emp Id = 3, Name = John, Email = john@abccompany.com

Joins in LINQ

Like simple SQL queries, you can also join columns using LINQ queries. The join operation is performed when you need the data from different tables based on some condition.

LINQ provides the join operator that can easily join single or multiple columns. Consider we have the following two classes:

public class Student {
  public int Stu_ID { get; set; }
  public string Stu_Name { get; set; }
  public int Class_ID { get; set; }
}

public class Grade {
  public int Grade_ID { get; set; }
  public string Grade_Name { get; set; }
}

In the driver function, let’s create two lists for each class as follows:

IList<Student> students_list = new List<Student>() {
  new Student() { Stu_ID = 11, Stu_Name = "ABC", Class_ID = 1 },
  new Student() { Stu_ID = 12, Stu_Name = "DEF", Class_ID = 1 },
  new Student() { Stu_ID = 13, Stu_Name = "GHI", Class_ID = 2 },
  new Student() { Stu_ID = 14, Stu_Name = "JKL", Class_ID = 2 },
};

IList<Grade> gradeList = new List<Grade>() { new Grade() { Grade_ID = 1, Grade_Name = "Grade 1" },
                                             new Grade() { Grade_ID = 2, Grade_Name = "Grade 2" },
                                             new Grade() { Grade_ID = 3, Grade_Name = "Grade 3" } };

Now, if we wish to get the students’ and their grade names, we need to join these two tables. These tables will be joined based on Class_ID and Grade_ID, respectively.

The join query will be like this:

var joinResult =
    from s in student_list join g in gradeList on s.Class_ID equals g.Grade_ID select new {
      StuName = s.Stu_Name, GradeName = g.Grade_Name
    };

This will yield the following output:

ABC Grade 1
DEF Grade 1
GHI Grade 2
JKL Grade 2

Group by Multiple Columns in LINQ Queries Using C#

In the same way, we can also group the data based on some attribute. This is done using the GroupBy clause in the LINQ query.

The GroupBy operator returns a subset of the elements in a provided collection based on a key value. IGrouping<TKey, TElement> objects represent each group.

Furthermore, the GroupBy method supports different overload methods so that you may utilize the appropriate extension method in method syntax based on your requirements.

Consider the following code:

List<Student> student_List = new List<Student>() {
  new Student() { Stu_ID = 11, Stu_Name = "ABC", Age = 18, Subject = "Arts" },
  new Student() { Stu_ID = 12, Stu_Name = "DEF", Age = 19, Subject = "Science" },
  new Student() { Stu_ID = 13, Stu_Name = "GHI", Age = 18, Subject = "Arts" },
  new Student() { Stu_ID = 14, Stu_Name = "JKL", Age = 19, Subject = "Science" },
};

Assume that we want to get the students’ list grouped by age. The following query will do that for us:

var result = from s in student_List group s by s.Age;

foreach (var ageGroups in result) {
  Console.WriteLine("Age Group: {0}", ageGroups.Key);
  foreach (Student s in ageGroups)  // Each group has inner collection
    Console.WriteLine("Student Name: {0}", s.Stu_Name);
}

Output:

Age Group: 18
Student Name: ABC
Student Name: GHI
Age Group: 19
Student Name: DEF
Student Name: JKL

Similarly, we can group by multiple columns as well. This is done in the following way:

var result = from s in student_List group s by new { s.Age, s.Subject };
foreach (var ageGroups in groupedResult) {
  Console.WriteLine("Group: {0}", ageGroups.Key);

  foreach (Student s in ageGroups)  // Each group has inner collection
    Console.WriteLine("Student Name: {0}", s.Stu_Name);
}

This query will yield the following output:

Group: {Age= 18, Subject="Arts"}
Student Name: ABC
Student Name: GHI
Group: {Age= 19, Subject="Science"}
Student Name: DEF
Student Name: JKL