NHibernate: Recursive queries

Question

Hi, I've got a basic tree structure that is stored in a single table. Let's say this is my model:

public class TreeNode {
  public virtual Guid Id { get; private set; } 
  public virtual string Name { get; private set; }
  public virtual IEnumerable<TreeNode> Contents { get; private set; }
}

and the table:

TREE_NODES
  PK_NODE Guid
  FK_NODE_PARENT Guid

NODE_NAME Varchar

I want the following implementation where the return value is a TreeNode with the full eagerly loaded tree of its children and their children, etc.

public class Tree {
  ISessionFactory _sessions;
  public TreeNode GetBy(Guid id) {
    using(var s = _sessions.OpenSession())
      return s.Linq<TreeNode>().Single(n => n.Id == id);
  }
}

How would I do this mapping?

Answer 1

I doubt you can optimize it - there is no recurtion in basic SQL. You can optimize it using server-side procedures (server specific - some servers, like MySQL does not support them) but it still be doubtful as you get non-recursive components.

Probably the best way is to walk down the tree in loading function and force the evaluation. Something like:

public class TreeNode {
  public virtual Guid Id { get; private set; } 
  public virtual string Name { get; private set; }
  public virtual IEnumerable<TreeNode> Contents { get; private set; }
}

public class Tree {
  ISessionFactory _sessions;
  public TreeNode GetBy(Guid id) {
    using(var s = _sessions.OpenSession()) {
      return LoadSubTree(s.Linq<TreeNode>().Single(n => n.Id == id));  
    }
  }
  private LoadSubTree(TreeNode node) {
    foreach(var n in node.Contents)
      LoadSubTree(n);
  }
}

PS. Tree is probably not the best place for ISessionFactory.