1

I need help fetching tree hierarchy from db using nhibernate 3.0

 QueryOver.Of<Enterprise>(() => entAlias)
     .JoinAlias(() => entAlias.ChildEntities, () => childEntityAllias, JoinType.LeftOuterJoin)
     .TransformUsing(new DistinctRootEntityResultTransformer())

I am getting only the two level of the graph (parent and its childrens) but not the childrens of children etc.

The same is if I try to fetch parents of the leaf. I get only the parent of the leaf, but not parent of the parent of the parent... of the leaf. where level = n.

Ho to do this type of query. no mather if Icriteria, linq, HQL or else.

Luka
  • 4,075
  • 3
  • 35
  • 61
  • Could you show your classes and mapping files? Is your tree hierarchy different types at each level or are the ChildEntities also Enterprise objects? – James Kovacs Dec 01 '10 at 20:51

2 Answers2

1

You could use batch-size to fetch the children more efficiently.

<bag name="Childen" batch-size="20" ...>

Children are still loaded by separate queries (you shouldn't join them in the query anymore), but always 20 at once. To turns "N+1" to "N/20+1" which usually performs very good. The advantage of this solution is that you don't need to care about in your queries.

You could also load parents in batches:

<class name="Enterprise" batch-size="20">

It loads many-to-one relations to Enterprises in batches if possible.

If you need even more optimization, consider to add a reference to the root (the top most parent). Then you can load all the children of a root in one easy query. The disadvantage is that you need to care about this reference, it is a redundancy which could also be hard to maintain.

Stefan Steinegger
  • 63,782
  • 15
  • 129
  • 193
  • Thanks, I think this is the best answer for now. The only problem is that I am working on a legacy database and it uses Identity as primary keys,so batching is disabled and cant be used. – Luka Dec 06 '10 at 09:17
  • this is batch-fetching (not batching of insert and update). It should always be possible, it does not depend on the kind of id-generator in use. – Stefan Steinegger Dec 06 '10 at 10:06
0

If you really want to fetch everything (although I'm not sure why you would do such a thing) then disable lazy loading (thus enabling eager loading) in NHibernate.

Venemo
  • 18,515
  • 13
  • 84
  • 125