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?

1) Why are you using string for your ID values? They're clearly numeric. 2) Don't use a using block on a class-level object such as Connection. The containing class owns it, not the method, so you should implement IDisposable and dispose it accordingly. — madreflection
"I've tinkered with..." things you haven't shown here. Please provide complete information so we don't make suggestions you've already tried or we can help you understand what went wrong and how to make them work. For example, if you've tried using QueryMultiple, it would help to see what you did and maybe see why it was clunky. — madreflection
1) Its fake data. Real data is alphanumeric. 2) I would like to hear more about this. — LCaraway
Fair point on #1. There's plenty of information on #2 out there but I'll offer you this: You have a car and you let your friend borrow it. Your friend is done with it and decides to sell it. Did your friend have the right to do that? No. It's your car. You're the class; your friend is the method. — madreflection
in Dapper specifically, you could use a Tuple to return all the objects in one database call — scgough

Daniel Earwicker Daniel Earwicker · Accepted Answer · 2019-08-27T18:48:29

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.

C# - Build Hierarchical Data Structures with Dapper

1 Answers