SubSonic 3.0 ActiveRecord - Preferred approach building object graph?

Question

Say I have a forum system with Threads, Posts and Tags. The structure is the same as StackOverflow: Threads have a 1-many relationship to Posts, and Tags have a many-many relationship to Threads.

The (simplified) tables:

Thread
------
ThreadID int PK
Title  varchar(200)

Tag
----
TagID  int PK
Name  varchar(50)

ThreadTag
-----------
ThreadTagID int PK
ThreadID int FK
TagID  int FK

So SubSonic ActiveRecord templates generate my classes for me.

Code

For the front page I need to get a list of Threads, and attach to each of these its list of related Tags. Leaving the posts count aside, what is the best way to retrieve the Tags and build this object graph?

If I get the threads like:

var threadQuery = Thread.All().Skip(x).Take(n);
var threadList = threadQuery.ToList();

Should I add an "IList<Tag> Tags" property to a partial of the Thread class?

And to retrieve the right tags, should I execute two queries: one to get the ThreadTags and one to get the Tags themselves: e.g.

var tagLinks = (from t in threadQuery
                        join l in ThreadTag.All() on t.ThreadID equals l.ThreadID
                        select l).ToList();

var tags = (from t in threadQuery
                        join l in ThreadTag.All() on t.ThreadID equals l.ThreadID
                        join tg in Tag.All() on l.TagID equals tg.TagID
                        select tg).ToList();

...and then use these lists to sort the tags in to the correct Thread.Tags list?

Is there a better way? I don't think I can use the IQueryable properties generated by SubSonic using the foreign keys, as that would trigger a database call for each of the Threads in my list.

Answer 1

After using SubSonic for a while, I think this is the best way. For large complex object graphs Entity Framework seems better suited.