Using Common Table Expressions for hierarchical data in NHibernate

NHibernate has no support for hierarchical queries. To get all descendents of a tree node, we have to do a recursive N+1 query. But by using Common Table Expressions (CTE) we can solve this very elegantly.