Best way to fetch tree data in NHibernate?

To find out what the "best way" is, more information regarding the actual scenario would be needed. What kind of "optimization" are you looking for? Minimal amount of data (only the rows you are really going to need) or minimal number of SQL queries (preferably one roundtrip to the database) or any other?

To find out what the "best way" is, more information regarding the actual scenario would be needed. What kind of "optimization" are you looking for? Minimal amount of data (only the rows you are really going to need) or minimal number of SQL queries (preferably one roundtrip to the database) or any other?

Scenario 1: Menu or tree structure that is loaded once and kept in memory for longer periods of time (not a list that updates every few seconds). Small number of rows in the table (small is relative but I'd say anything below 200). In this case I would just get the whole table with one query like this: var items = session.Query() .

Fetch(c => c. ParentWork) . Fetch(c => c.

ChildWorks).ToList(); var item = session. Get(id); This will result in a single SQL query which simply loads all the rows from the table. Item will contain the complete tree (parents, grandparents, children, etc.).

Scenario 2: Large number of rows and only a fraction of rows needed. Only few levels in the hierarchy are to be expected.In this case, just load the item and let Nbernate to the rest with lazy loading or force it to load everything by writing a recursive method to traverse parents and children. This will cause a N+1 select, which may or may not be slower than scenario 1 (depending on your data).

Here is a quick hack demonstrating this: var item = session. Get(id); Work parent = item. ParentWork; Work root = item; // find the root item while (parent!

= null) { root = parent; parent = parent. ParentWork; } // scan the whole tree this. ScanChildren(root); // ----- private void ScanChildren(Work item) { if (item == null) { return; } foreach (Work child in item.

ChildWorks) { string name = child.Name; this. ScanChildren(child); } } Edit: Scenario 3: Huge amount of data. Minimal number of queries and minimal amount of data.In this case, I would think not of a tree structure but of having layers of data that we load one after another.

Var work = repo.Session. Get(id); // get root of that Work Work parent = work. ParentWork; Work root = work; while (parent!

= null) { root = parent; parent = parent. ParentWork; } // Get all the Works for each level IList worksAll = new List() { root }; IList worksPerLevel = new List() { root }; // get each level until we don't have any more Works in the next level int count = worksPerLevel. Count; while (count > 0) { worksPerLevel = this.

GetChildren(session, worksPerLevel); // add the Works to our list of all Works worksPerLevel. ForEach(c => worksAll. Add(c)); count = worksPerLevel.

Count; } // here you can get the names of the Works or whatever foreach (Work c in worksAll) { string s = c. Name; } // this methods gets the Works in the next level and returns them private IList GetChildren(ISession session, IList worksPerLevel) { IList result = new List(); // get the IDs for the works in this level IList ids = worksPerLevel. Select(c => c.

Id).ToList(); // use a WHERE IN clause do get the Works // with the ParentId of Works in the current level result = session.QueryOver() . Where( NCriterion.Restrictions. InG( NCriterion.Projections.

Property( c => c.ParentWork. Id), ids) ) . Fetch(c => c.

ChildWorks). Eager // this will prevent the N+1 problem .List(); return result; } This solution will not cause a N+1 problem, because we use an eager load for the children, so Nbernate will know the state of the child lists and not hit the DB again. You will only get x+y selects, where x is the number of selects to find the root Work and y is the number of levels (max depth of he tree).

Thanks for the reply and I appreciate your effort in elaborating the solution. – sqlnewbie Apr 14 at 9:41 What kind of "optimization" are you looking for? Minimal amount of data (only the rows you are really going to need) or minimal number of SQL queries (preferably one roundtrip to the database) or any other?

To answer your above queries...(I am looking for minimal number of queires and minimal amout of data. – sqlnewbie Apr 14 at 9:42 Data I am working on is Huge (6lac recs or so).. so I think first soultion and second solution might not work for me :( – sqlnewbie Apr 14 at 9:43 @Srinivas I added another solution (minimal amount of data, minimal number of queris, plenty of code). – Florian Lim Apr 14 at 10:35 lim- Scenario 3 is perfect I think its the best way to get data in tree structures.

– sqlnewbie Apr 147 at 5:07.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.