Querying a Hierarchical Parent-Child Structure in LINQ

Posted by: Suprotim Agarwal , on 5/23/2010, in Category LINQ
Views: 88352
Abstract: I was recently going through the forums and found some developers struggling with queries involving Classes with One-To-Many Relationship. In this article, I will demonstrate how to query a Hierarchical Parent-Child Structure using LINQ.
I was recently going through the forums and found some developers struggling with queries involving Classes with One-To-Many Relationship. In this article, I will demonstrate how to query a Hierarchical Parent-Child Structure using LINQ.
Note: You may also want to read some more LINQ Tutorials written by me to get familiar with LINQ
To print the results, I am using a utility class called ObjectDumper which can be obtained from here. The structure of the classes representing a Parent-Child relationship is shown below. Each Department has many Employees, but one Employee belongs to only one Department
C#
public class Department
{
    public int DeptID { get; set; }
    public string DeptName { get; set; }
    public List<Employee> employee { get; set; }
 
}
 
public class Employee
{
    public int EmpID { get; set; }
    public int DeptID { get; set; }
    public string Name { get; set; }
    public int AgeInYrs { get; set; }
}
VB.NET (10.0)
Public Class Department
      Public Property DeptID() As Integer
      Public Property DeptName() As String
      Public Property employee() As List(Of Employee)
 
End Class
 
Public Class Employee
      Public Property EmpID() As Integer
      Public Property DeptID() As Integer
      Public Property Name() As String
      Public Property AgeInYrs() As Integer
End Class
Sample Data
List<Department> lists = new List<Department>()
{
    new Department()
    {
        DeptID = 1, DeptName = "Marketing", employee = new List<Employee>
        {
            new Employee() { EmpID = 9, DeptID = 1, Name = "Jack Nolas", AgeInYrs = 28 },
            new Employee() { EmpID = 5, DeptID = 1, Name = "Mark Pine" , AgeInYrs = 42 },
            new Employee() { EmpID = 3, DeptID = 1, Name = "Sandra Simte" , AgeInYrs = 38 },
            new Employee() { EmpID = 8, DeptID = 1, Name = "Larry Lo" , AgeInYrs = 31 }
        }
    },
    new Department()
    {
        DeptID = 2, DeptName = "Sales", employee = new List<Employee>
        {
            new Employee() { EmpID = 1, DeptID = 2, Name = "Sudhir Panj" , AgeInYrs = 28 },
            new Employee() { EmpID = 7, DeptID = 2, Name = "Kathy Karlo" , AgeInYrs = 43 },
            new Employee() { EmpID = 4, DeptID = 2, Name = "Dinesh Kumar" , AgeInYrs = 34 }
        }
    },
    new Department()
    {
        DeptID = 3, DeptName = "HR", employee = new List<Employee>
        {
            new Employee() { EmpID = 2, DeptID = 3, Name = "Kaff Joe" , AgeInYrs = 25 },
            new Employee() { EmpID = 6, DeptID = 3, Name = "Su Lie" , AgeInYrs = 52 },
            new Employee() { EmpID = 10, DeptID = 3, Name = "Malcolm Birt" , AgeInYrs = 41 }
        }
    }
};
Here are some common Operations on a Hierarchical Parent-Child List. Please use this Converter Tool to convert the code to VB.NET
1. List Employees in Each Department
var empInDept = lists
    .Select(emp => new
    {
        Department = emp.DeptName,
        Employee = emp.employee.Select(e => e.Name)
    });
 
ObjectDumper.Write(empInDept, 1);
Employee_Each_Department
2. Print the Average Age of Employees in each Department
var avgAgePerDept = lists
                .Select(emp => new
                {
                    Department = emp.DeptName,
                    AverageAge = emp.employee.Average(avg => (double)avg.AgeInYrs)
                });
 
ObjectDumper.Write(avgAgePerDept, 1);
Average_Age
3. List only those Employees in each Department with Age > 30
 
var empGt30 = lists
    .Select(emp => new
    {
        Department = emp.DeptName,
        Emp = emp.employee.Where(em => em.AgeInYrs > 30)
        .Select(e => new
        {
            EmployeeName = e.Name,
            Age = e.AgeInYrs
        })
    });
 
ObjectDumper.Write(empGt30, 1);
Below_30
4. Count the number of Employees in each Department
var cntEmpPerDept = lists
                .Select(emp => new
                {
                    Department = emp.DeptName,
                    NoOfEmployees = emp.employee.Count()
                });
 
ObjectDumper.Write(cntEmpPerDept, 1);
CountInEachDepartment
5. Sort and List Employees in each Department
var ordered = lists
   .Select(emp => new
   {
       Department = emp.DeptName,
       Employee = emp.employee.OrderBy(e => e.Name)
       .Select(c => new
                {
                    Name = c.Name
                })
   });
 
ObjectDumper.Write(ordered, 1);
SortBy
The entire source code of this article can be downloaded over here. I hope you liked the article and I thank you for viewing it.
Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Suprotim Agarwal, ASP.NET Architecture MVP, MCSD, MCAD, MCDBA, MCSE, is the CEO of A2Z Knowledge Visuals Pvt. He primarily works as an Architect Consultant and provides consultancy on how to design and develop .NET centric database solutions.

Suprotim is the founder and primary contributor to DotNetCurry, DNC .NET Magazine, SQLServerCurry and DevCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls. and is authoring another one at The Absolutely Awesome jQuery CookBook.

Follow him on twitter @suprotimagarwal


Page copy protected against web site content infringement by Copyscape


User Feedback

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel