LINQ - select everyone in parent-child hierarchy
I was wondering if there is a simple way of doing this that does NOT use any while loop or similar, preferably that would work against Linq for Entities as a single SQL jump, and also against Linq To Objects.
I have an entity - Forum
- with parent and child relationships. That is, it Forum
can (or in the case of the top level cannot) ParentForum
and can have a lot ChildForums
. A Forum
then contains a lot Posts
.
What I'm going to do here is a way to get everything Posts
from the tree Forums
- i.e. Forum
, and all these are children, grandchildren, etc. I don't know in advance how many sub-elements it can have Forum
.
(Note: I know this example is not necessarily a valuable use case, but the Forum object model is one that most people are familiar with and therefore serves as a general and accessible premise, not my real domain model.)
source to share
One possible way would be if your actual data tables were saved using a left / right tree (example here: http://www.sitepoint.com/hierarchical-data-database-2/ . This is an example in MySQL / PHP , but it's trivial to implement). Using this you can find out all forums that fall into the parent left / right values, and with that in mind, you can get all posts whose names are IN
those forum IDs.
source to share
I'm sure you can get some correct answers for Linq queries. I am posting this as a guideline when it comes to the SQL side.
I had a similar problem with virtual file system in SQL. I needed to be able to request files in folders recursively - with folders, of course, with recursive parent-child relationships. I also needed this to be fast, and I certainly didn't want to give up client side processing.
For performance, I ended up writing stored procedures and inline functions - unfortunately too hard to put here (and I could get a bag of company code sharing!). 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 down, but the performance is incredible (they get very wrong very easily, so - pay attention to the query plans).
source to share