Can I create a custom expression without using the where clause?

Question

While I have already solved this issue in a previous question using a native query. I am now wondering if it is possible to create a custom expression that is usable in a Criteria without using the where clause? The reason I don't want the where clause is because Oracle's connect by ... start with ... (here) statement. I followed this page to get my started. However, this will generate code like select * from foo where connect by start with...

Here is what I am using. Looking at what gets generated I can say that it is generating the correct statement minus the where clause.

public class StartWithConnectByCriteria : AbstractCriterion
{
    public StartWithConnectByCriteria(string parentName, string parentValue, string childName)
    {
        ParentName = parentName;
        ParentValue = parentValue;
        ChildName = childName;
    }

    public string ParentName { get; set; }
    public string ParentValue { get; set; }
    public string ChildName { get; set; }
    public IProjection P { get; set; }

    public override IProjection[] GetProjections()
    {
        if(P != null)
        {
            return new IProjection[] {P};
        }
        return null;
    }

    public override TypedValue[] GetTypedValues(ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return
            CriterionUtil.GetTypedValues(criteriaQuery, criteria, P, ParentName, ParentValue.ToString());
    }

    public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery,
                                          IDictionary<string, IFilter> enabledFilters)
    {
        var sqlBuilder = new SqlStringBuilder();
        SqlString[] parentColumnNames = CriterionUtil.GetColumnNames(ParentName,
                                                               P, criteriaQuery,
                                                               criteria, enabledFilters);
        SqlString parentColumnName = parentColumnNames[0];

        SqlString[] childColumnNames = CriterionUtil.GetColumnNames(ChildName,
                                                   P, criteriaQuery,
                                                   criteria, enabledFilters);
        SqlString childColumnName = childColumnNames[0];

        criteriaQuery.AddUsedTypedValues(GetTypedValues(criteria, criteriaQuery));
        sqlBuilder
            .Add("start with " + parentColumnName + " = '" + ParentValue + "'")
            .Add(" connect by prior " + childColumnName + " = " + parentColumnName);

        return sqlBuilder.ToSqlString();
    }

    public override string ToString()
    {
        return "";
    }
}

I am using it like this.

StartWithConnectByCriteria criterion = 
    new StartWithConnectByCriteria(
        "parent", 
        "parent_value", 
        "child");

DetachedCriteria dc = DetachedCriteria.For<NormalUpstream>("nu")
    .Add(criterion);

I have a feeling it has to do with the .Add() from the DetachedCriteria but not 100% sure. Unfortunately, I can't seem to find much documentation about creating a custom expression.

Edit: Now that I think about it looks like I'm barking up the wrong tree. While this isn't crucial (I already have a decent implementation). I'm still interested in seeing how I can further customize NHibernate.

Edit 2: Since out of the box NHibernate doesn't support Oracle's proprietary function, start with ... connect by. I'm trying to learn more about extending NHibernate by adding native support for it. I'm aware I can register those function with a custom dialect. But I'm wondering if it's possible to implement it as a Criteria so I can use it with my other criteria queries. The code I posted works fine and correctly creates valid SQL but when I add StartWithConnectByCriteria to my criteria, NHibernate will issues a query such as select this_.id from table where start with ... connect by. Which is an invalid query because that clause does not belong with the where.

This is the query I'd expect for NHibernate to generate.

select
    random_column
from
    table
start with parent_id = 'parent_node_id'
connect by prior child_up_id = parent_id

Notice how there is no where clause in this query. However, start with ... connect by could still be used with a where clause. You can read more about how those keywords work here.

Answer 1

I don't know if the existing NHibernate syntax allows for this, but there is an ANSI standard syntax for hierarchical queries which may prove useful. I believe it only works in 11R2 and above, however, so I'm not sure if it is useful for you. See Recursive Subquery Refactoring for more information.