Category Model and Trees Mapping In NHibernate

As a rule, I have to build tree structure for categories, and we will take a look at them. Let’s start with the clean domain model…

Domain model

Here is the model I’d like to present you:

public class Category
{
    private ISet; childCategories;
    private Category parentCategory;

    public Category()
    {
        childCategories = new HashedSet();
    }

    public virtual int Id { get; protected set; }

    public virtual string Name { get; set; }

    public virtual ReadOnlyCollection ChildCategories
    {
        get
        {
            return new ReadOnlyCollection(new List(childCategories));
        }
    }

    public virtual Category ParentCategory
    {
        get
        {
            return parentCategory;
        }
    }

    public virtual void AddChildCategory(Category category)
    {
        if (category == null) throw new ArgumentNullException("category");
        childCategories.Add(category);
    }

    public virtual void SetParentCategory(Category category)
    {
        if (category == null) throw new ArgumentNullException("category");
        parentCategory = category;
    }
}

Every category contains link to its parent and the list of Child Categories URLs. Currently the model does not guarantee that the tree will actually be a tree. What I mean is: we have the following nesting: “Parent” –> “Child” -> “Child of Child”. Domain model won’t restrict the next code in any way: Category(“Child Of Child”).AddChildCategory(“Parent”). It will create a loop, and none of the recursive algorithms will work without additional actions.

To avoid such a situation you may add the logic validation fro the new added categories to SetParentCategory and AddChildCategory methods. That’s way, while adding a subcategory you will be able to check whether the current hierarchy doesn’t already contain it, etc.

Mapping

Now let’s turn to Nhibernate. Mapping for this class will be the following:

public class CategoryMap : ClassMap
{
public CategoryMap()
{
Id(x => x.Id).GeneratedBy.Native();

Map(x => x.Name)
.Length(200)
.Not.Nullable();

References(x => x.ParentCategory)
.Column("ParentCategoryId")
.Access.CamelCaseField();

HasMany(x => x.ChildCategories)
.Cascade.AllDeleteOrphan()
.AsSet()
.KeyColumn("ParentCategoryId")
.Access.CamelCaseField();
}
}

According to the mapping we can generate the following table:

Overall it does not contain anything unusual. To create categories you may use the following code:
using (var transaction = Global.CurrentSession.BeginTransaction())

{
    Category parent = new Category();
    parent.Name = "parent";

    Category child = new Category();
    child.Name = "child";

    parent.AddChildCategory(child);

    Category childOfTheChild = new Category();
    childOfTheChild.Name = "child of the child";

    child.AddChildCategory(childOfTheChild);

    Global.CurrentSession.SaveOrUpdate(parent);
    transaction.Commit();
}

As AllDeleteOrphan cascading is installed in mappings, it is enough just to save parent category. Also it allows manage the behavior in case of deletion. Category which parent is being deleted at the moment, will be deleted with the parent one. If you don’t need such type of behavior, change the cascading type to All.

Some words about the performance

Hierarchical structures selections are complicated in SQL itself. If you leave it as it is, then Nhibernate will work in a usual lazy load mode and will select every collection of subcategories with a detached query. Next code is an example:

protected void Button1_Click(object sender, EventArgs e)
{
    ICriteria criteria = Global.CurrentSession.CreateCriteria(typeof (Category));
    criteria.Add(Restrictions.IsNull("ParentCategory"));
    foreach (Category category in criteria.List())
    {
        EnumerateChilds(category);
    }
}

protected void EnumerateChilds(Category category)
{
    foreach (Category childCategory in category.ChildCategories)
    {
        EnumerateChilds(childCategory);
    }
}

Here all parent categories are selected, and we will run through all the child ones. If there is a following nesting: “Category”->”Child Category”->”Child of child category”, this code will run the queries:

-- Choosing parent category
SELECT this_.Id as Id0_0_,
       this_.Name as Name0_0_,
       this_.ParentCategoryId as ParentCa3_0_0_
FROM [Category] this_
WHERE this_.ParentCategoryId is null

-- Choosing parent category
SELECT this_.Id as Id0_0_,
       this_.Name as Name0_0_,
       this_.ParentCategoryId as ParentCa3_0_0_
FROM [Category] this_
WHERE this_.ParentCategoryId is null

As a rule, to build a neat navigation tree all categories have to be selected at one time. Oren Eini has shown in his blog how to optimize the tree selection. This can be done by using the following query:

var categories = Global.CurrentSession
                       .CreateQuery("select c from Category c join fetch c.ChildCategories")
                       .SetResultTransformer(new DistinctRootEntityResultTransformer())
                       .List();

This option will run less queries, but will make extra one for each category, which is the last in the hierarchy. Thus category, which does not have any subcategories, will generate one more query to the database. Specifically, I had 2 such categories and the following queries were made:

-- statement #1
select category0_.Id                 as Id0_0_,
       childcateg1_.Id               as Id0_1_,
       category0_.Name               as Name0_0_,
       category0_.ParentCategoryId   as ParentCa3_0_0_,
       childcateg1_.Name             as Name0_1_,
       childcateg1_.ParentCategoryId as ParentCa3_0_1_,
       childcateg1_.ParentCategoryId as ParentCa3_0__,
       childcateg1_.Id               as Id0__
from   [Category] category0_
       inner join [Category] childcateg1_
         on category0_.Id = childcateg1_.ParentCategoryId

-- statement #2
SELECT childcateg0_.ParentCategoryId as ParentCa3_1_,
       childcateg0_.Id               as Id1_,
       childcateg0_.Id               as Id0_0_,
       childcateg0_.Name             as Name0_0_,
       childcateg0_.ParentCategoryId as ParentCa3_0_0_
FROM   [Category] childcateg0_
WHERE  childcateg0_.ParentCategoryId = 8 /* @p0 */

-- statement #3
SELECT childcateg0_.ParentCategoryId as ParentCa3_1_,
       childcateg0_.Id               as Id1_,
       childcateg0_.Id               as Id0_0_,
       childcateg0_.Name             as Name0_0_,
       childcateg0_.ParentCategoryId as ParentCa3_0_0_
FROM   [Category] childcateg0_
WHERE  childcateg0_.ParentCategoryId = 10 /* @p0 */

8th and 10th categories do not contain subcategories. This question is discussed in NHUsers group. Neither answers why it happens, nor solutions, were found there yet. So it is not guaranteed that if you run this query database load would decrease.
The same behavior is seen is you turn off the lazy load and set fetch-mode=”join”.
Another problem is that it excludes the categories, that have parent, but do not have any subcategories. Accordingly, if nesting is larger than one level, you will get excess results.
That is why I would prefer using standard lazy load until it will become the real problem for performance. As soon as it will be noticeably slowing down, I will have to optimize it through storage procedures, or some other way.