How can I setup a self-referencing hierarchy with LLBLGen? Here is my table schema:

Table: SiteMap ( ID int Primary Key, Field1, Field2, ParentID int null )

Basically a row in SiteMap with ParentID=null is the root of the hierarchy. All other rows will have a value in ParentID which will point to the parent record (via the parent's ID field). i.e. ID to ParentID is a 1:n relationship.

What is the best way to traverse such a hierarchy from top down with LLBLGen Pro? Can I retrieve a collection of child entities from the parent entity?

Forgot to mention, I am using the Adapter scenario.

I want to understand how to set this up in the designer because currently by creating the Relationship I am not getting the code I was expecting. I get these 2 methods: I cant see this retrieving the child entity collection from the GetDependingRelatedEntities().

/// <summary> Gets a collection of related entities referenced by this entity which depend on this entity (this entity is the PK side of their FK fields). These entities will have to be persisted after this entity during a recursive save.</summary>
/// <returns>Collection with 0 or more IEntity2 objects, referenced by this entity</returns>
public override IEntityCollection2 GetDependingRelatedEntities()
        {
            EntityCollection toReturn = new EntityCollection();

            return toReturn;
        }

/// <summary> Gets a collection of related entities referenced by this entity which this entity depends on (this entity is the FK side of their PK fields). These
        /// entities will have to be persisted before this entity during a recursive save.</summary>
        /// <returns>Collection with 0 or more IEntity2 objects, referenced by this entity</returns>
public override IEntityCollection2 GetDependentRelatedEntities()
        {
            EntityCollection toReturn = new EntityCollection();
            if(_siteMap!=null)
            {
                toReturn.Add(_siteMap);
            }

            return toReturn;
        }

GetDependingRelatedEntities is a framework method for the recursive save method, you should use the generated fields mapped on relation to retrieve the related entities.

Though with such a hierarchy, it's easier: - fetch all entities, sort on ParentID asc - create a hashtable and use it for PK - NodeObject relations - walk the collection once, create for each entity a new tree node (or whatever hierarchy object you want to use the hierarchy for) and store the relation in the hashtable. The node to store the new node under is found by using parentid as index in the hashtable.

It's not easy to fetch it in 1 query, as it's not definable in 1 SELECT statement.

Should not it be possible to handle hierarchical queries like this with a single sql statement using common table expressions, available in sql server 2005?

Check out: http://www.theserverside.net/articles/showarticle.tss?id=HeirarchicalQueries

What about supporting this in future version of llblgen pro?

It might be too expensive to fetch all the hierarchy at once.

Though you may fetch the root entity along with its direct children and the next level.... up to the level you want by using PrefetchPaths. (This is also expensive), when I faced this before I used to fetch 2 levels deep at a time.

Indeed. It might be more cheaper to fetch all entities in an entity collection and then build the hierarchy using 2 hashtables which can be done on O(n)

sami wrote:

Should not it be possible to handle hierarchical queries like this with a single sql statement using common table expressions, available in sql server 2005?

Check out: http://www.theserverside.net/articles/showarticle.tss?id=HeirarchicalQueries

What about supporting this in future version of llblgen pro?

I aggree, CTE's should be useful in making this happen in the new version. It should be less "expensive" than reading the entire collection and then wiring it up with hashtables as Frans suggested.

alexdresko wrote:

sami wrote:

Should not it be possible to handle hierarchical queries like this with a single sql statement using common table expressions, available in sql server 2005?

Check out: http://www.theserverside.net/articles/showarticle.tss?id=HeirarchicalQueries

What about supporting this in future version of llblgen pro?

I aggree, CTE's should be useful in making this happen in the new version. It should be less "expensive" than reading the entire collection and then wiring it up with hashtables as Frans suggested.

I'm not sure, aren't you re-reading rows from the same table while just changing the filter? Reading htem straight forward is much faster then.

Also, using CTE's is usefull on following scenario:

For example, you have table of deparments, which can have sub-departments. A deparment contains people working in that deparment.

Now, you want to fetch all people working under certain deparment or its sub-deparments. I think using CTE is the easiest way to do that.

sami wrote:

Also, using CTE's is usefull on following scenario:

For example, you have table of deparments, which can have sub-departments. A deparment contains people working in that deparment.

Now, you want to fetch all people working under certain deparment or its sub-deparments. I think using CTE is the easiest way to do that.

If the departments are in the same table, reading the set once is faster than creating different subsets of the same set through CTE's.

Make no mistake: storing a tree in a single table with the 'parentid' fk pointing to the same table isn't the most efficient way to storing tree data, no matter if there are cte's or recursive queries to help you. Much easier is it to store the tree as a balanced tree, so you can use simple select statements to retrieve a complete hierarchy in a branche.

Correct me if I am wrong, but are you suggesting that I would have a table per hierarchy level?

sami wrote:

Correct me if I am wrong, but are you suggesting that I would have a table per hierarchy level?

No, please check this document: long url on google groups

Now this is interesting, thanks for the hint

Is there a way of implementing what is talked about in that google groups page in LLBLGen without using stored procedures? Specifically the adding of new entities?

Thanks, Matt

Yes, I don't see a reason why that wouldn't work. The queries are all writable as LLBLGen Pro queries, except perhaps the insert code with the update + CASE statement. This needs 2 or 3 to overcome the lack of support for CASE.

A nice tip to look into the balanced tree approach.

Might the referenced "Long URL on Google Groups" be now pointing at an unintended link?

Maybe the link previously contained information about database storage using a "balanced tree" in lieu of a "self-referencing" table?

The only information on this link now appears to contain is a suggestion: "Considering get hold of "Advanced Transact-SQL for SQL Server 2000" by Itzik Ben-Gan and Tom Moreau. The book has an entire chapter on dealing with hierarchical structures implemented in SQL Server. "

...or maybe your recommendation is to get this book to understand about hierarchical structures in SQL Server?

greenstone wrote:

A nice tip to look into the balanced tree approach.

Might the referenced "Long URL on Google Groups" be now pointing at an unintended link?

Maybe the link previously contained information about database storage using a "balanced tree" in lieu of a "self-referencing" table?

yes.

The only information on this link now appears to contain is a suggestion: "Considering get hold of "Advanced Transact-SQL for SQL Server 2000" by Itzik Ben-Gan and Tom Moreau. The book has an entire chapter on dealing with hierarchical structures implemented in SQL Server. " ...or maybe your recommendation is to get this book to understand about hierarchical structures in SQL Server?

No, I was pointing to a thread on google groups where CELKO explained it. He posted it a number of times so do a search on CELKO + tree and you'll find it

Thanks!