3

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

Alex
  • 21,273
  • 10
  • 61
  • 73
RichardW1001
  • 1,985
  • 13
  • 22
  • you may find this answer http://stackoverflow.com/questions/5205777/recursive-linq-grouping useful. Apologies if I interpretted the question incorrectly. if you call the GetChildrenMethod initially, you only do one call to the db – Krishna Mar 12 '12 at 09:30
  • 1
    Related: http://stackoverflow.com/questions/3758162/rendering-a-hierarchy-using-linq – Gert Arnold Mar 12 '12 at 09:39

2 Answers2

1

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.

Moo-Juice
  • 38,257
  • 10
  • 78
  • 128
  • Interesting... what happens when you change the structure? Do you then re-build the left/right tree? – RichardW1001 Mar 12 '12 at 09:34
  • @RichardW1001, yes, there are a set of UPDATES you need to do. It's generally okay unless you have a seriously massive hierarchy, but in the case of forums and sub-forums I don't see performance being a particular issue here. – Moo-Juice Mar 12 '12 at 09:35
1

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

Andras Zoltan
  • 41,961
  • 13
  • 104
  • 160