C# - Build Hierarchical Data Structures with Dapper

Question

I have a table of employees that looks like the following tables. Every column is apart of a composite primary key and thus also a foreign key in the child tables:

I have created the following c# models to house this data:

public class Company
{
    public string CompanyID {get; set;}
    public List<Departments> Departments {get; set;}
}

public class Departments
{
    public string CompanyID {get; set;}
    public string DepartmentID {get; set;}
    public List<Employee> Employees {get; set;}
}

public class Employees
{
    public string CompanyID {get; set;}
    public string DepartmentID {get; set;}
    public string EmplyeeID {get; Set;}
}

I then have a repository in which I want to use dapper to populate the data in its entirety. Traditionally, in a non hierarchical implementation I would just do something such as the following:

public IEnumerable<Employee> GetEmployees()
{
    IEnumerable<Employee> result = null;
    string sql = "SELECT * FROM EMPLOYEE;"
    using(IDbConnection c = Connection) //declared earlier with connection string
    {
        c.Open();
        result = c.Query<Employee>(sql);
    }

    return result;
}

what makes me unsure is the most performant way to use dapper to build my desired data structure (at the company class level) such that the single company class contains a list of departments, departments have a list of employees.

I've tinkered with multiple queries and using several loops build the structure but it just seems so clunky and i cant quite get it right.

So here is the formal question:

How do we build nested enumerable objects to represent hierarchical data structures using dapper?

Answer 1

As your employee records contain all the hierarchy information, there is no need to go back to the database in further queries. Just get the leaf employee records and then group them to form the hierarchy in memory:

var companies = employees
    .GroupBy(e => new { dept = e.DepartmentID, comp = e.CompanyID })
    .Select(g => new Department
    {
        CompanyID = g.Key.comp,
        DepartmentID = g.Key.dept,
        Employees = g.ToList()
    })
    .GroupBy(d => d.CompanyID)
    .Select(g => new Company
    {
        CompanyID = g.Key,
        Departments = g.ToList()
    });

This would work on employee records from source, not just Dapper.