NHibernate and recursive query filter

I have a problem with tree structure and applying filters to the tree root. I need to create a query by using the NHibernate criteria, which will apply filter to the very root of the tree (there can be more than one tree and I need to provide the ID of the root). In plain sql this would look sth like:

WITH    c ( Node, Parent, Child, LEVEL )
      AS ( SELECT   N.CatalogNodeId ,
                    N.ParentCatalogNodeId ,
                    N.CatalogNodeId ,
                    CAST(0 AS BIGINT)
           FROM     CatalogNode N
           WHERE    N.ParentCatalogNodeId IS NULL
           UNION ALL
           SELECT   C.Node ,
                    N.ParentCatalogNodeId ,
                    N.Ca开发者_如何学编程talogNodeId ,
                    C.Level + 1
           FROM     CatalogNode N
                    JOIN c ON C.Child = n.ParentCatalogNodeId
         )
SELECT  
        c.Child
FROM    c
        JOIN dbo.CatalogItem CI ON Ci.CatalogNodeId = c.Child
        JOIN dbo.Item I ON I.ItemId = CI.ItemId
WHERE   C.Node = @Node_Id

This query returns exactly what I want, but it's only a part of bigger query. What I need is to use the criteria api to create the same result: a list of items with a given root ID (keeping the tree structure is not needed - it's the result of the "search" command, so just a collection of entries is required). Unfortunatelly I'm not NHibernate expert, so... any guides, ideas would be helpful.

My suggestion is that you keep your SQL as-is. Don't even try doing that with Criteria.

You can mix SQL with criteria but not a cte as the Expression.SQL() puts the relevant sql in the WHERE clause... that said you can apply a two step process in your method:

execute the above recursive query in plain sql and it returns an Id of some sort (c.Child) and that product you can use with Restrictions.Eq if it is a single row, or Restrictions.In for a returned collection of ids.