TL;DR – Sample ASP.NET MVC3 application demonstrating how to load hierarchical table data with a single SQL SELECT statement using PetaPoco’s Multi-Poco mapping feature.
This blog post can be forked on GitHub: tawman / PetaPocoHierarchy.
PetaPoco – A Tiny ORM-ish Thing
We are using PetaPoco on a current project implementation since our data management needs do not rise to the level of NHibernate. I learn something new each time I think we reached the limits of what PetaPoco can achieve for us. The most recent scenario involved loading hierarchical data without introducing a SELECT N+1 problem. I will demonstrate how to use the PetaPoco Multi-Poco Mapping Feature to load hierarchical table data with a single SELECT statement.
Multi-Poco Mapping Feature
The PetaPoco – Mapping One-to-Many and Many-to-One Relationships blog article described using the Multi-Poco Mapping Feature for loading one-to-many relationships, but I found the same technique works well with hierarchical data.
Organization Hierarchical Data Example
I built a simple ASP.NET MVC3 website involving an organization hierarchy modeled as a single table with a self-referencing column for the parent organization. The site contains a single page and table to display the hierarchical organization tree.
The DDL used to generate the table in SQL Server 2008 R2 Express is shown in the following gist:
The following table illustrates the sample data loaded in the Organization table and used for this demo, but I replaced the Guid values with integers to make the parent-child relationships easier to identify:
| Id | ParentId | OrganizationCode | Name |
|---|---|---|---|
| 1 | NULL | US | United States |
| 2 | 1 | MW | Midwest |
| 3 | 1 | NE | Northeast |
| 4 | 1 | NW | Northwest |
| 5 | 1 | SE | Southeast |
| 6 | 1 | SW | Southwest |
| 7 | 2 | MI | Michigan |
| 8 | 2 | IN | Indiana |
| 9 | 2 | IL | Illinois |
| 10 | 2 | OH | Ohio |
| 11 | 7 | WC | Wayne |
| 12 | 7 | WA | Washtenaw |
| 13 | 11 | DE | Detroit |
| 14 | 11 | CA | Canton |
| 15 | 11 | PM | Plymouth |
| 16 | 12 | AA | Ann Arbor |
| 17 | 12 | YP | Ypsilanti |
| 18 | 16 | UM | University of Michigan |
| 19 | 17 | EMU | Eastern Michigan University |
The website uses the following Organization POCO object to store the hierarchical data from the Organization table:
Loading the data from SQL Server is accomplished with a single CTE query, but processed by PetaPoco with a custom helper method to transform the query results. The Organization Repository GetAll() method invokes the PetaPoco Multi-Poco Mapping feature as follows:
The CTE query prepares the Organization data for processing by the custom Multi-Poco Mapping Helper OrganizationParentRelator().BuildHierarchy with the following query result set (only last 4 of Guid shown):
| Id | Parent | Code | Name | Id | Parent | Code | Name | Level | LinkId |
|---|---|---|---|---|---|---|---|---|---|
| 5D6B | 26A5 | SW | Southwest | 26A5 | NULL | US | United States | 1 | 5D6B |
| 5D6B | 26A5 | SW | Southwest | 5D6B | 26A5 | SW | Southwest | 0 | 5D6B |
| FACC | BF51 | WC | Wayne | 26A5 | NULL | US | United States | 3 | FACC |
| FACC | BF51 | WC | Wayne | 88BB | 26A5 | MW | Midwest | 2 | FACC |
| FACC | BF51 | WC | Wayne | BF51 | 88BB | MI | Michigan | 1 | FACC |
| FACC | BF51 | WC | Wayne | FACC | BF51 | WC | Wayne | 0 | FACC |
| BF51 | 88BB | MI | Michigan | 26A5 | NULL | US | United States | 2 | BF51 |
| BF51 | 88BB | MI | Michigan | 88BB | 26A5 | MW | Midwest | 1 | BF51 |
| BF51 | 88BB | MI | Michigan | BF51 | 88BB | MI | Michigan | 0 | BF51 |
| 26A5 | NULL | US | United States | 26A5 | NULL | US | United States | 0 | 26A5 |
| 3661 | FACC | PM | Plymouth | 26A5 | NULL | US | United States | 4 | 3661 |
| 3661 | FACC | PM | Plymouth | 88BB | 26A5 | MW | Midwest | 3 | 3661 |
| 3661 | FACC | PM | Plymouth | BF51 | 88BB | MI | Michigan | 2 | 3661 |
| 3661 | FACC | PM | Plymouth | FACC | BF51 | WC | Wayne | 1 | 3661 |
| 3661 | FACC | PM | Plymouth | 3661 | FACC | PM | Plymouth | 0 | 3661 |
| D24E | 88BB | IL | Illinois | 26A5 | NULL | US | United States | 2 | D24E |
| D24E | 88BB | IL | Illinois | 88BB | 26A5 | MW | Midwest | 1 | D24E |
| D24E | 88BB | IL | Illinois | D24E | 88BB | IL | Illinois | 0 | D24E |
| DCAC | BF51 | WA | Washtenaw | 26A5 | NULL | US | United States | 3 | DCAC |
| DCAC | BF51 | WA | Washtenaw | 88BB | 26A5 | MW | Midwest | 2 | DCAC |
| DCAC | BF51 | WA | Washtenaw | BF51 | 88BB | MI | Michigan | 1 | DCAC |
| DCAC | BF51 | WA | Washtenaw | DCAC | BF51 | WA | Washtenaw | 0 | DCAC |
| FD9B | DCAC | YP | Ypsilanti | 26A5 | NULL | US | United States | 4 | FD9B |
| FD9B | DCAC | YP | Ypsilanti | 88BB | 26A5 | MW | Midwest | 3 | FD9B |
| FD9B | DCAC | YP | Ypsilanti | BF51 | 88BB | MI | Michigan | 2 | FD9B |
| FD9B | DCAC | YP | Ypsilanti | DCAC | BF51 | WA | Washtenaw | 1 | FD9B |
| FD9B | DCAC | YP | Ypsilanti | FD9B | DCAC | YP | Ypsilanti | 0 | FD9B |
| 18F8 | FACC | DE | Detroit | 26A5 | NULL | US | United States | 4 | 18F8 |
| 18F8 | FACC | DE | Detroit | 88BB | 26A5 | MW | Midwest | 3 | 18F8 |
| 18F8 | FACC | DE | Detroit | BF51 | 88BB | MI | Michigan | 2 | 18F8 |
| 18F8 | FACC | DE | Detroit | FACC | BF51 | WC | Wayne | 1 | 18F8 |
| 18F8 | FACC | DE | Detroit | 18F8 | FACC | DE | Detroit | 0 | 18F8 |
| 88BB | 26A5 | MW | Midwest | 26A5 | NULL | US | United States | 1 | 88BB |
| 88BB | 26A5 | MW | Midwest | 88BB | 26A5 | MW | Midwest | 0 | 88BB |
| 947A | BB56 | UM | University of Michigan | 26A5 | NULL | US | United States | 5 | 947A |
| 947A | BB56 | UM | University of Michigan | 88BB | 26A5 | MW | Midwest | 4 | 947A |
| 947A | BB56 | UM | University of Michigan | BF51 | 88BB | MI | Michigan | 3 | 947A |
| 947A | BB56 | UM | University of Michigan | DCAC | BF51 | WA | Washtenaw | 2 | 947A |
| 947A | BB56 | UM | University of Michigan | BB56 | DCAC | AA | Ann Arbor | 1 | 947A |
| 947A | BB56 | UM | University of Michigan | 947A | BB56 | UM | University of Michigan | 0 | 947A |
| AB85 | 26A5 | NW | Northwest | 26A5 | NULL | US | United States | 1 | AB85 |
| AB85 | 26A5 | NW | Northwest | AB85 | 26A5 | NW | Northwest | 0 | AB85 |
| 24D5 | FACC | CA | Canton | 26A5 | NULL | US | United States | 4 | 24D5 |
| 24D5 | FACC | CA | Canton | 88BB | 26A5 | MW | Midwest | 3 | 24D5 |
| 24D5 | FACC | CA | Canton | BF51 | 88BB | MI | Michigan | 2 | 24D5 |
| 24D5 | FACC | CA | Canton | FACC | BF51 | WC | Wayne | 1 | 24D5 |
| 24D5 | FACC | CA | Canton | 24D5 | FACC | CA | Canton | 0 | 24D5 |
| B338 | 88BB | IN | Indiana | 26A5 | NULL | US | United States | 2 | B338 |
| B338 | 88BB | IN | Indiana | 88BB | 26A5 | MW | Midwest | 1 | B338 |
| B338 | 88BB | IN | Indiana | B338 | 88BB | IN | Indiana | 0 | B338 |
| F352 | FD9B | EMU | Eastern Michigan University | 26A5 | NULL | US | United States | 5 | F352 |
| F352 | FD9B | EMU | Eastern Michigan University | 88BB | 26A5 | MW | Midwest | 4 | F352 |
| F352 | FD9B | EMU | Eastern Michigan University | BF51 | 88BB | MI | Michigan | 3 | F352 |
| F352 | FD9B | EMU | Eastern Michigan University | DCAC | BF51 | WA | Washtenaw | 2 | F352 |
| F352 | FD9B | EMU | Eastern Michigan University | FD9B | DCAC | YP | Ypsilanti | 1 | F352 |
| F352 | FD9B | EMU | Eastern Michigan University | F352 | FD9B | EMU | Eastern Michigan University | 0 | F352 |
| 4EE6 | 26A5 | SE | Southeast | 26A5 | NULL | US | United States | 1 | 4EE6 |
| 4EE6 | 26A5 | SE | Southeast | 4EE6 | 26A5 | SE | Southeast | 0 | 4EE6 |
| FCFC | 88BB | OH | Ohio | 26A5 | NULL | US | United States | 2 | FCFC |
| FCFC | 88BB | OH | Ohio | 88BB | 26A5 | MW | Midwest | 1 | FCFC |
| FCFC | 88BB | OH | Ohio | FCFC | 88BB | OH | Ohio | 0 | FCFC |
| 22E8 | 26A5 | NE | Northeast | 26A5 | NULL | US | United States | 1 | 22E8 |
| 22E8 | 26A5 | NE | Northeast | 22E8 | 26A5 | NE | Northeast | 0 | 22E8 |
| BB56 | DCAC | AA | Ann Arbor | 26A5 | NULL | US | United States | 4 | BB56 |
| BB56 | DCAC | AA | Ann Arbor | 88BB | 26A5 | MW | Midwest | 3 | BB56 |
| BB56 | DCAC | AA | Ann Arbor | BF51 | 88BB | MI | Michigan | 2 | BB56 |
| BB56 | DCAC | AA | Ann Arbor | DCAC | BF51 | WA | Washtenaw | 1 | BB56 |
| BB56 | DCAC | AA | Ann Arbor | BB56 | DCAC | AA | Ann Arbor | 0 | BB56 |
The custom helper is supplied as a parameter to the PetaPoco Multi Fetch call:
public List <TRet> Fetch<T1, T2, TRet>(Func <T1, T2, TRet> cb, string sql, params object [] args) { return Query<T1, T2, TRet>(cb, sql, args).ToList(); }
The repository query is structured such that each resulting row of data contains an Organization (T1) and Parent Organization (T2). The query will return multiple rows for the same Organization equal to its depth in the hierarchy. The rows are sorted by Organization Hierarchy Level in descending order.
The OrganizationParentRelator.BuildHierarchy(organization, parent) helper method will push the parent organization upward as each Organization row is processed and then return the hierarchical representation of the Organization (TRet) when no more parents are present:
Resulting Organization Structure Displayed Online
Conclusion
I hope you found this blog post helpful and be sure to check out the full source code on GitHub: tawman / PetaPocoHierarchy. The PetaPoco project page over on Topten Software has more examples under the blog posts sections for using the Multi-Poco Mapping feature in other ways.
