nHibernate HqlTreeBuilder to implement Linq methods for HierarchyId

Question

I am looking at implementing a Hierarchy data structure in SQL Server using HierarchyId, and I need to add extension methods that can be used, via Linq, to use the HierarchyId methods exposed in TSQL. Now I have all the code for connecting a Linq method to NHibernate via a HqlGenerator I just can't find the right code to build the SQL I need.

So for example, for the blow Linq...

session.Query<Person>().Where(x=>x.Hierarchy.IsDescendantOf('/1/3/'))

I want to end up with SQL that looks something like this...

SELECT people0_.ObjectId, people0_.Name, people0_.Hierarchy
    FROM People people0_
        WHERE people0_.Hierarchy.IsDescendantOf('/1/3/') = 1

My problem is I can't figure out the HqlTreeBuilder code to implement in my BaseHqlGeneratorForMethod class to accomplish it because I need to get the IsDescendantOf method to be a child method of the column, meaning I need to combine the expression representing the Hierarchy Column to appear immediately before my method call with the dot in between.

I THOUGHT this would work, but it doesn't. Any suggestions?

public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
{
    return treeBuilder.Equality(
            treeBuilder.Dot(visitor.Visit(arguments[0]).AsExpression(), treeBuilder.MethodCall("IsDescendantOf", new[] {visitor.Visit(arguments[1]).AsExpression()})),
            treeBuilder.Constant(1)
        );
}

score 0 · Accepted Answer · answered Jun 13 '14 at 14:59

I ended up doing it this way...

My code implementation

public static bool IsDescendantOf(this string childHierarchy, string parentHierarchy)
{
    //In most cases this will be translated to the SQL implementation by LINQ, but on the off chance it's used on an in memory collection, the simplest implementation
    //Is to verify that the child hierarchy starts with the hierarchy of the parent.
    //for example....
    // "/11/534/2134/".StartsWith("/11/534/") //would be TRUE
    return childHierarchy.StartsWith(parentHierarchy);
}

The HqlGenerator

public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
{
    return treeBuilder.BooleanMethodCall("_IsDescendantOf", new[] { visitor.Visit(arguments[0]).AsExpression(), visitor.Visit(arguments[1]).AsExpression() });
}

And then where you configure nHibernate I have this line

cfg.AddSqlFunction("_IsDescendantOf", new NHibernate.Dialect.Function.SQLFunctionTemplate(NHibernate.NHibernateUtil.Boolean, "?1.IsDescendantOf(?2) = 1"));

Where cfg is an instance of your NHibernate.Cfg.Configuration class

An example of a project that support many hierarchyId functions is: https://github.com/RaveNoX/NHibernate.HierarchyId. Note, it uses string rather than CLR's SqlhierarchyId — Phil, Jul 09 '14 at 21:33

nHibernate HqlTreeBuilder to implement Linq methods for HierarchyId

1 Answers1