Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Part 19 - Group by multiple keys in linq

Suggested Videos
Part 16 - Cast and OfType operators in LINQ
Part 17 - AsEnumerable and AsQueryable in LINQ
Part 18 - GroupBy in LINQ



In this video, we will discuss Grouping by multiple keys. In LINQ, an anonymous type is usually used when we want to group by multiple keys.



Let us understand this with an example. We will be using the following Employee class in this demo. This is the same class used in Part 18. Please watch Part 18 before proceeding.
public class Employee
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Gender { get; set; }
    public string Department { get; set; }

    public static List<Employee> GetAllEmployees()
    {
        return new List<Employee>()
        {
            new Employee { ID = 1, Name = "Mark", Gender = "Male"
                                         Department = "IT" },
            new Employee { ID = 2, Name = "Steve", Gender = "Male"
                                         Department = "HR" },
            new Employee { ID = 3, Name = "Ben", Gender = "Male"
                                         Department = "IT" },
            new Employee { ID = 4, Name = "Philip", Gender = "Male"
                                         Department = "IT" },
            new Employee { ID = 5, Name = "Mary", Gender = "Female"
                                         Department = "HR" },
            new Employee { ID = 6, Name = "Valarie", Gender = "Female"
                                         Department = "HR" },
            new Employee { ID = 7, Name = "John", Gender = "Male"
                                         Department = "IT" },
            new Employee { ID = 8, Name = "Pam", Gender = "Female"
                                         Department = "IT" },
            new Employee { ID = 9, Name = "Stacey", Gender = "Female"
                                         Department = "HR" },
            new Employee { ID = 10, Name = "Andy", Gender = "Male"
                                         Department = "IT" },
        };
    }
}

Example 1: Group employees by Department and then by Gender. The employee groups should be sorted first by Department and then by Gender in ascending order. Also, employees within each group must be sorted in ascending order by Name.


var employeeGroups = Employee.GetAllEmployees()
                                        .GroupBy(x => new { x.Department, x.Gender })
                                        .OrderBy(g => g.Key.Department).ThenBy(g => g.Key.Gender)
                                        .Select(g => new
                                        {
                                            Dept = g.Key.Department,
                                            Gender = g.Key.Gender,
                                            Employees = g.OrderBy(x => x.Name)
                                        });

foreach(var group in employeeGroups)
{
    Console.WriteLine("{0} department {1} employees count = {2}",
        group.Dept, group.Gender, group.Employees.Count());
    Console.WriteLine("--------------------------------------------");
    foreach (var employee in group.Employees)
    {
        Console.WriteLine(employee.Name + "\t" + employee.Gender
            + "\t" + employee.Department);
    }
    Console.WriteLine(); Console.WriteLine();
}

Output:
linq group by multiple keys

Example 2: Rewrite Example 1 using SQL like syntax
var employeeGroups = from employee in Employee.GetAllEmployees()
                                        group employee by new
                                        {
                                             employee.Department,
                                             employee.Gender
                                        } into eGroup
                                        orderby eGroup.Key.Department ascending, 
                                                      eGroup.Key.Gender ascending
                                        select new
                                        {
                                             Dept = eGroup.Key.Department,
                                             Gender = eGroup.Key.Gender,
                                             Employees = eGroup.OrderBy(x => x.Name)
                                        };

linq tutorial

1 comment:

  1. Hi venkat... Thanks for providing these tutorials. I hearty request you to upload some videos regarding DESIGNING PATTERNS in .NET.

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.