Hierarchical Data with PetaPoco Multi-Poco Mapping

6 minute read

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

PetaPocoInfoGraphicDisplay

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:

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

WebPageView

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.