http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660No, you should not. Post your question along with mapping & code to the nh users list http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment19http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment19Tue, 15 Sep 2009 20:05:15 GMTShould i expect to see NH issuing extra selects when I enumerate the child collection of the leaf nodes in the tree? I'm observing this happening and wondering if im doing something wrong :s http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment18http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment18Tue, 15 Sep 2009 13:16:51 GMTCould you turn off lazy loading and then retrieve the first employee. Nhibernate will then try and populate the children property and hence retrieve the full tree. http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment17http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment17Fri, 04 Sep 2009 03:46:48 GMTZihotki, If you need to order the collection itself, you need an ordered collection. NHibernate doesn't allow to change sort order for collections on the fly without using filters. For the leaf ones, you use left joins, yes. I am not sure how you got the leafs to have a reference to themselves, please post the results to nh users http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment16http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment16Wed, 02 Sep 2009 11:01:30 GMTAyende, This snippet doesn't work as well as I supposed. Yes, all tree will be loaded at once, but there is one "small" problem. During traversal through this tree (using several nesting cycles or recursion) NH will generate N additional queries, N equals to the count of leafs (employees who have no managed employees, parent without childs). In order to make it working, I mean to remove these additional queries for leafs, the query should be changed to "select e from Employee e left join fetch e.ManagedEmployees". But using this query you'll get other errors - all leafs will be in the root of the result and each leaf will have reference to self (I added "public virtual Employee Manager {get; set;}") http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment15http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment15Wed, 02 Sep 2009 01:06:20 GMTThis snippet is very cool. Thanks a lot, I have to rewrite some code in my project :). Is there any easy way to filter entire result's tree to contain only employees that have height (Employee table/entity contains Height column/property) more than 180cm ordering by height? E.g. only employees with height more than 180cm will be loaded from DB and the result tree will not contain any other employee. And employees in ManagedEmployees will be ordered by Height. Neither "select e from Employee e join fetch e.ManagedEmployees where e.Height>180 order by e.Height" nor "select e from Employee e join fetch e.ManagedEmployees as empl where e.Height>180 and empl.Height > 180 order by e.Height, empl.Height" are not working because "A fetch join does not usually need to assign an alias, because the associated objects should not be used in the where clause (or any other clause). Also, the associated objects are not returned directly in the query results. Instead, they may be accessed via the parent object.". http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment14http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment14Tue, 01 Sep 2009 18:11:08 GMTNot with the 1.0 version, we will have those in the 2.0 one http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment13http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment13Tue, 01 Sep 2009 09:12:07 GMTThat's great. Is there a corresponding way to do this using the Linq provider? Nathan http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment12http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment12Mon, 31 Aug 2009 13:37:31 GMTulu, That presume that we can agree on the terms on the left side, though. http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment11http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment11Sun, 30 Aug 2009 06:58:18 GMTDmitriy, The difference is that mine will also pre-load all the ManagedEmployees. As for the difference in the DB, it is simple create Employee(Id, ManagedBy, ...) create OrganizationHierarchy(EmpId, ManagedBy, Level) Now you have a way to refer, using standard set notation, to the entire hierarchy. Think about it as denormalizing that http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment10http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment10Sun, 30 Aug 2009 06:57:44 GMTJust another thought. Your posts on mappings are fantastic, but sometimes a noob like me needs a simple table showing which language constructs can be mapped with which mapping elements, so that I don't have to read all mapping chapter and your posts to figure out what I need in this particular case. Sort of, simple property => property entity-valued property => one-to-many (or is it many-to-one?) IDictionary => map etc etc Would be great if some of you NHibernate Masters could put such a table in a wiki. http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment9http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment9Fri, 28 Aug 2009 14:32:21 GMTThe whole idea of delayed posts is.. refreshing. I was faced with the task to load a tree yesterday, and I had a choice: wait till thi post appears, or go to sleep. Seriously, could you please post the mapping as well? Another question is, suppose I want only descendants from a single entity. Can't I just set the children to eager loading and fetch this entity? Thanks a lot for your posts about NH. I'm just learning it, and the official docs are a bit hard to grasp. http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment8http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment8Fri, 28 Aug 2009 12:24:55 GMTIf only for this abstraction, i will use nhibernate ;) this is excellent! http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment7http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment7Fri, 28 Aug 2009 07:50:32 GMTOren, What exactly do you mean saying "relation between a parent and all its children (indirect and direct) is preserved in the DB."? It would be interesting to see how you use NHibernate to effectively load the whole tree (maybe not in a single query:) ) as I saw it has been asked many times in the NH User Groups. Additionally what is the difference between your HQL (with distinct root transformer) and simply this? "from Employee" they seem to return equals lists (maybe except of order). http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment6http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment6Fri, 28 Aug 2009 05:54:02 GMTDmitriy, You are correct, I haven't shown the calling code, which loaded all the employees by calling GetHierarchyRecursive. The problem that you have here is that SQL has no good way of specifying hierarchies. Therefor, you would have to write DB specific tree code, and feed that to NH if you wanted to load an entire tree in a single shot using N level parent. A much easier task is to change the hierarchy so the relation between a parent and all its children (indirect and direct) is preserved in the DB. That is a MUCH better proposition than doing hierarchical queries http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment5http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment5Fri, 28 Aug 2009 04:24:12 GMTRob, DistinctRootEntityResultTransformer will filter the output in memory so the list of entities that you get will have no duplicates. There are going to be duplicates in the result set if you don't do that because there is a join i the query http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment4http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment4Fri, 28 Aug 2009 04:17:53 GMTThe original code (with all its cons) retrieves the hierarchy for a given root only. The HQL example you provided retrieves ALL the hierarchies with all the roots. This seems to be totally different scenario comparing to the original code. Right? Also how would you efficiently retrieve the tree hierarchy starting from a specific root? Cheers. http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment3http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment3Fri, 28 Aug 2009 04:08:13 GMTThis may answer your question: [nhforge.org/.../...esults-from-joined-queries.aspx](http://nhforge.org/wikis/howtonh/get-unique-results-from-joined-queries.aspx)http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment2http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment2Fri, 28 Aug 2009 03:18:12 GMTWhat is the significance of DistinctRootEntityResultTransformer? Why do I need that? http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment1http://ayende.com/4151/nhibernate-tips-tricks-efficiently-selecting-a-tree#comment1Fri, 28 Aug 2009 02:51:56 GMT