INode Interface and ToTree Methods - lobodava/artisan-orm GitHub Wiki

Hierarchical data — folder trees, organisation charts, comment threads, BOMs — is naturally stored in SQL as a flat table with Id / ParentId (or via hierarchyid). Artisan.Orm has built-in support for reading such tables and re-assembling them into a tree of objects in one step.

Background reading. This page is the API reference. For the long-form story of the Id-ParentId + hierarchyid combined approach — including the schema design, the recursive CTE, and the trade-offs — see the re-published article: Combination of Id-ParentId and HierarchyId Approaches to Hierarchical Data.

public interface INode<T> where T : class
{
    int       Id        { get; set; }
    int?      ParentId  { get; set; }
    IList<T>? Children  { get; set; }
}

Implement this interface and provide a registered or explicit reader mapper for the ReadTo* path. ParentId == null marks a root; the unsorted builder also treats a node whose parent is absent from the supplied rows as a root. Tree construction does not validate a database schema or repair cycles.

using Artisan.Orm;
using Microsoft.Data.SqlClient;
using System.Collections.Generic;

public class Folder : INode<Folder>
{
    public int        Id        { get; set; }
    public int?       ParentId  { get; set; }
    public string     Name      { get; set; } = null!;
    public IList<Folder>? Children { get; set; }
}

[MapperFor(typeof(Folder), RequiredMethod.CreateObject)]
public static class FolderMapper
{
    public static Folder CreateObject(SqlDataReader dr) => new Folder
    {
        Id = dr.GetInt32(0),
        ParentId = dr.IsDBNull(1) ? null : dr.GetInt32(1),
        Name = dr.GetString(2)
    };
}

This illustrative model/mapper matches the three-column query below. The repository's existing Folder model has additional hierarchy fields and its own mapper; use that mapper with its matching procedures.

create procedure dbo.GetFolderTree
    @RootFolderId int
as
begin
    set nocount on;

    with cte as
    (
        select Id, ParentId, [Name], 0 as Lvl
        from   dbo.Folders
        where  Id = @RootFolderId

        union all

        select f.Id, f.ParentId, f.[Name], cte.Lvl + 1
        from   dbo.Folders  f
        inner join cte on cte.Id = f.ParentId
    )
    select Id, ParentId, [Name]
    from   cte
    order by Lvl, ParentId, Id;     -- level order, not depth-first pre-order
end;

public Folder? GetFolderTree(int rootFolderId)
{
    return GetByCommand(cmd =>
    {
        cmd.UseProcedure("dbo.GetFolderTree");
        cmd.AddIntParam("@RootFolderId", rootFolderId);

        // This query is ordered by level, so use the order-independent builder.
        return cmd.ReadToTree<Folder>(hierarchicallySorted: false);
    });
}

The result links each returned node to its returned parent. The builder allocates a parent's Children when needed; leaf collections can remain null. The repository method belongs inside a RepositoryBase subclass with an explicit connection-string constructor.

Both come in repository, command, and reader layers. Repository and command APIs have async siblings; reader-level tree materialization is synchronous:

// RepositoryBase level
public T? ReadToTree<T>     (string sql, bool hierarchicallySorted = false, ...);
public IList<T> ReadToTreeList<T>(string sql, bool hierarchicallySorted = false, ...);
public Task<T?> ReadToTreeAsync<T>(...);
public Task<IList<T>> ReadToTreeListAsync<T>(...);

// SqlCommand level — when you need to set parameters via Action<SqlCommand>
public T? ReadToTree<T>(this SqlCommand cmd, bool hierarchicallySorted = false);

// SqlDataReader level — for reading a tree from a multi-result-set reader
public T? ReadToTree<T>(this SqlDataReader dr, bool getNextResult = true,
                                               bool hierarchicallySorted = false);

<T> must satisfy where T : class, INode<T>.

• false (default): the builder collects all nodes into a dictionary keyed by Id, then a second pass walks the dictionary linking each node to its parent's Children. Two passes, allocates a Dictionary<int, T>. Works with any row order.

• true: the builder requires depth-first pre-order: a parent followed by its complete subtree before the next sibling subtree. It uses a stack instead of a dictionary. This can reduce allocation, but requires the query to guarantee that order.

For a single user's hierarchy, ORDER BY Hid supplies depth-first order. For several independent users, use ORDER BY UserId, Hid and unique application IDs. A path string is suitable only if its encoding and collation preserve complete subtree ordering; arbitrary path text is insufficient.

ORDER BY Lvl, ParentId, Id is breadth-first and does not satisfy this requirement. For example, it can return two sibling folders before returning the first sibling's children. The example above therefore uses hierarchicallySorted: false. A clustered index does not replace an explicit ORDER BY.

If the order isn't guaranteed, leave hierarchicallySorted at false.

ReadToTree<T> returns the first root found (or null for an empty result set). If your query may yield multiple top-level nodes — e.g., "all folders for several users" — use ReadToTreeList<T>:

// Returns every root relative to the rows supplied by this query.
IList<Folder> roots = repo.ReadToTreeList<Folder>("dbo.GetAllUserFolders");

If you already have a flat IList<T> from somewhere — say, you read it once with ReadToList and want to build the tree separately — call the same algorithm directly:

IList<Folder> flat = repo.ReadToList<Folder>("dbo.GetAllFolders");

Folder? root = flat.ToTree(hierarchicallySorted: false);
// or
IList<Folder> forest = flat.ToTreeList(hierarchicallySorted: false);

This is helpful when you want to apply C# filters / transformations on the flat list first, then build the tree from the survivors. The two calls above are alternatives. Tree construction mutates the existing objects and appends child links; use fresh nodes or clear existing child collections before rebuilding the same list.

For very deep or very wide hierarchies, the SQL Server hierarchyid type pairs nicely with INode<T>:

• Keep Id / ParentId as the primary application contract (works in any DB engine, easy to debug).
• Add a hierarchyid column for subtree queries using Hid.IsDescendantOf(@RootHid) and appropriate hierarchical indexes.
• order by the hierarchyid column to get pre-order rows that satisfy hierarchicallySorted: true.

The full design — schema, indexing strategy, and how the two representations stay in sync — is covered in the re-published article: Combination of Id-ParentId and HierarchyId Approaches to Hierarchical Data.

The sample uses dbo.SaveFolder with @WithHidReculc, exposed in C# as SaveFolder(folder, withHidReorder: true). There is no separate deployed SaveFolderWithHidReculc procedure. Its helper is named dbo.RecalcSubFolderHids; the source filename retains the historical ReculcSubFolderHids.sql spelling.

Three SQL corrections are included in the source being prepared for 4.1.0:

• The old parent's path prefix comes from @OldParentHid, not the integer @OldParentId.
• The initial ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY Name, Id) numbers old and new sibling groups independently after a move.
• dbo.GetHidCode uses varchar(1784) for its return value and hexadecimal conversion. A hierarchyid can occupy 892 bytes, so 1000 characters can truncate its code and collapse distinct long paths. This capacity applies to HidCode, not the separate human-readable HidPath buffers.

Regression tests exercise a move between nonempty sibling groups with descendants and complete distinct long hierarchy codes. See FolderSqlRegressionTest and the hierarchyid size limit.

The current Folders table obtains IDs from the dbo.FolderId sequence. The sample still requires valid non-root input, parent/user consistency, prevention of cycles, and suitable concurrency control. It does not implement every such validation. Its path buffers and default recursive-CTE limit also constrain very deep trees. A caller-owned transaction must choose the necessary isolation; the procedure only sets SERIALIZABLE when starting its own transaction.

These are SQL sample changes, not a NuGet migration. Update the affected test-database objects explicitly before running the tests. Publishing the whole Database project runs a folder generator that truncates sample folder data. See 4.1.0 migration guidance.

Tests/DAL/Folders/Repository.cs exercises the full set — GetFolderTree, FindFoldersWithParentTree, list-to-tree conversion utilities — against the seeded dbo.Folders table. Worth a read when you adopt this for a new entity.

See also:

• Read Methods Understanding
• Mappers — INode<T> types still need a [MapperFor] mapper for CreateObject