HierarchyId as a typed parameter in NHibernate sp_executesql call

Question

I'm trying to find a way to cause the executed query sent by NHibernate (sp_executesql) to the database to use a custom sql data type. I've got a Linq expression in C# that I'm trying to run and uses a custom HqlGenerator to support the IsDescendantOf method of HierarchyId.

My C# properties for the HierarchyId are currently being represented as strings ( didn't need the complexity of the Microsoft.SqlServer.Types representation of it).

But the problem is that the resulting string being sent to sp_executesql is indicating that the parameters being passed are NVARCHAR(400), regardless of anything I change with the mappings at all. But this is causing problems with the indexing of my HierarchyId field, the query is timing out because it's not using my index at all. But as soon as I change the parameter type to HierarchyId, it runs in 0 to 1 seconds. So I'm trying to find a way to get the sp_executesql statement to specify the parameters being sent are HierarchyId and not NVARCHAR(4000).

I've tried changing the type in the mapping, I've tried changing the custom SqlType in the mapping. I'm not sure how else I can influence NHibernate to manually specify the type for the call to sp_executesql.

I really need a solution at this point, but I'm not sure how the innards of NHibernate determine the type of a parameter.

Updated with code

C# Code simplified expression for the scenario I'm dealing with

var currentUserScopedHierarchy = "/11/";
session.Query<ClaimSubmission>()
    .Where(x => x.Hierarchy.IsDescendantOf(currentUserScopedHierarchy))
    .OrderByDescending(x => x.DateCreated).ToList();

the Hierarchy property on my ClaimSubmission class is a string field. With the HqlGenerator I have for IsDescendantOf, the resulting SQL observed in SQL Server Profiler is as follows

SQL

exec sp_executesql N'/* [expression] */select claimsubmi0_.ObjectId     as ObjectId22_,
       claimsubmi0_.DateCreated  as DateCrea2_22_,
       claimsubmi0_.DateModified as DateModi3_22_,
       claimsubmi0_.Name         as Name22_,
       claimsubmi0_.SearchText   as SearchText22_,
       claimsubmi0_.CreatedBy    as CreatedBy22_,
       claimsubmi0_.ModifiedBy   as ModifiedBy22_,
       claimsubmi0_.OwnerId      as OwnerId22_,
       claimsubmi0_.Hierarchy    as Hierarchy22_,
       claimsubmi0_.TypeId       as TypeId22_,
       claimsubmi0_.Terminate    as Terminate22_
from   PseudoObjects claimsubmi0_
where  /* removed extra code here that delt with discriminators that isn't relevant */
    claimsubmi0_.Hierarchy.IsDescendantOf(@p0) = 1
order  by claimsubmi0_.DateCreated desc'
,N'@p0 nvarchar(4000)' ,@p0=N'/11/'

The problem is that last bit where the parameter with a value of "/11/" is being passed as a NVARCHAR(400). As is, this query takes about 20 seconds (30+ once I add back in other logic that I removed to get a more simplified example), and it's because the data type is causing it to ignore my indexes. If I change the type to HierarchyId, the query is instant. If I run the query out side of a call to sp_executesql and inline the parameter value, it's instant as well.

HqlGenerator Related

Extension method

    public static bool IsDescendantOf(this string childHierarchy, string parentHierarchy)
    {
        return childHierarchy.StartsWith(parentHierarchy);
    }

If it's relevant, here's the HqlGenerator as well

public class IsDescendantOfGenerator : BaseHqlGeneratorForMethod
{
    public IsDescendantOfGenerator()
    {
        SupportedMethods = new[]
            {
                ReflectionHelper.GetMethodDefinition(() => ExtensionMethods.IsDescendantOf(null, null))
            };
    }

    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 as part of the NHibernate setup, I have this

Config.AddSqlFunction("_IsDescendantOf", new SQLFunctionTemplate(NHibernateUtil.Boolean, "?1.IsDescendantOf(?2) = 1"));