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

2 answers

One possible way would be if your actual data tables were saved using a left / right tree (example here: . 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.



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 . 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).



All Articles