CODEWITHSUNDEEP
c#vb.netlinqlinq-to-entitieslinq-to-objects
RichardW1001
RichardW1001

Reputation: 1985

LINQ - Select all in parent-child hierarchy

I was wondering if there is a neat way do to this, that DOESN'T use any kind of while loop or similar, preferably that would run against Linq to Entities as a single SQL round-trip, and also against Linq To Objects.

I have an entity - Forum - that has a parent-child relationship going on. That is, a Forum may (or in the case of the top level, may not) have a ParentForum, and may have many ChildForums. A Forum then contains many Posts.

What I'm after here is a way to get all the Posts from a tree of Forums - i.e. the Forum in question, and all it's children, grandchildren etc. I don't know in advance how many sub-levels the Forum in question may have.

(Note - I know this example isn't necessarily a valuable use case, but the Forum object model one is one that is familiar to most people, and so serves as a generic and accessible premise rather than my actual domain model.)

Upvotes: 3

Views: 2039

Answers (2)

Andras Zoltan
Andras Zoltan

Reputation: 42363

I'm sure you might get a few proper answers regarding the Linq queries. I'm posting this as an advisory when it comes to the SQL side of things.

I had a similar issue with a virtual filesystem in SQL. I needed to be able to query files in folders recursively - with folders, of course, having a recursive parent-child relationship. I also needed it to be fast, and I certainly didn't want to be dropping back to client-side processing.

For performance I ended up writing stored procedures and inline functions - unfortunately much too complicated to post here (and I might get the sack for sharing company code!). The key, however, was to learn how to work with Recursive CTEs http://msdn.microsoft.com/en-us/library/ms186243.aspx. It took me a few days to nail it but the performance is incredible (they are very easy to get wrong though - so pay attention to the query plans).

Upvotes: 1

Moo-Juice
Moo-Juice

Reputation: 38820

One possible way would be if your actual data tables were stored using a left/right tree (example here: http://www.sitepoint.com/hierarchical-data-database-2/ . Note, that example is in MySQL/PHP, but it's trivial to implement). Using this, you can find out all forums that fall within a parent's left/right values and given that, you can retrieve all posts who's forum IDs is IN those forum IDs.

Upvotes: 1

Related Questions