Advanced LINQ in C# (GroupBy, Join, Aggregate)

LINQ is not limited to basic filtering and sorting. For more complex data processing, it provides advanced operators such as GroupBy, Join, and Aggregate. In this article, we will explore how to use these operators with examples.

GroupBy

GroupBy groups collection elements by a specified property. You can then run queries on groups to extract statistical insights.

using System;
using System.Linq;
using System.Collections.Generic;

var students = new List<(string Name, string City)>
{
    ("John", "New York"),
    ("Mary", "Chicago"),
    ("Michael", "New York"),
    ("Sarah", "Chicago"),
    ("David", "Boston")
};

// Group students by city
var groups = students.GroupBy(s => s.City);

foreach (var group in groups)
{
    Console.WriteLine($"City: {group.Key} - Student Count: {group.Count()}");
    foreach (var student in group)
        Console.WriteLine($"  {student.Name}");
}

// Output:
// City: New York - Student Count: 2
//   John
//   Michael
// City: Chicago - Student Count: 2
//   Mary
//   Sarah
// City: Boston - Student Count: 1
//   David

Join

Join works similarly to INNER JOIN in SQL, combining two collections based on a common key.

var students = new List<(int Id, string Name)>
{
    (1, "John"),
    (2, "Mary"),
    (3, "Michael")
};

var grades = new List<(int StudentId, int Score)>
{
    (1, 90),
    (2, 75),
    (3, 82)
};

// Combine students with grades using Join
var result = students.Join(
    grades,
    s => s.Id,            // outer key
    g => g.StudentId,     // inner key
    (s, g) => new { s.Name, g.Score }
);

foreach (var item in result)
    Console.WriteLine($"{item.Name} → {item.Score}");

// Output:
// John → 90
// Mary → 75
// Michael → 82

Aggregate

Aggregate performs a cumulative operation on a collection. It works like a loop, updating an accumulator value to produce a final result.

var numbers = new List<int> { 2, 3, 4, 5 };

// Multiply all numbers
int product = numbers.Aggregate((acc, n) => acc * n);
Console.WriteLine(product); // 120

// Build a comma-separated string from numbers
string text = numbers.Aggregate("Numbers:", (acc, n) => acc + " " + n);
Console.WriteLine(text);
// Output: Numbers: 2 3 4 5

Sample Application: Sales Report

In the following example, sales records are processed using GroupBy, Join, and Aggregate together.

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
}

public class Sale
{
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal Price { get; set; }
}

class Program
{
    static void Main()
    {
        var products = new List<Product>
        {
            new Product { Id = 1, Name = "Laptop" },
            new Product { Id = 2, Name = "Phone" },
            new Product { Id = 3, Name = "Tablet" }
        };

        var sales = new List<Sale>
        {
            new Sale { ProductId = 1, Quantity = 2, Price = 1000 },
            new Sale { ProductId = 2, Quantity = 5, Price = 500 },
            new Sale { ProductId = 1, Quantity = 1, Price = 950 },
            new Sale { ProductId = 3, Quantity = 3, Price = 300 }
        };

        // Join products with sales
        var report = products.Join(
            sales,
            p => p.Id,
            s => s.ProductId,
            (p, s) => new { p.Name, s.Quantity, s.Price }
        );

        // Group by product
        var groupedReport = report.GroupBy(r => r.Name);

        foreach (var group in groupedReport)
        {
            var totalRevenue = group.Aggregate(0m, (acc, r) => acc + (r.Quantity * r.Price));
            Console.WriteLine($"{group.Key} → Sales Count: {group.Count()}, Revenue: {totalRevenue} USD");
        }
    }
}

// Example Output:
// Laptop → Sales Count: 2, Revenue: 2950 USD
// Phone → Sales Count: 1, Revenue: 2500 USD
// Tablet → Sales Count: 1, Revenue: 900 USD

TL;DR

Related Articles