Linq-to-Sql: recursively get children

Question

I have a comment table that has CommentID and ParentCommentID. I am trying to get a list of all the children comment. This is what I still have, I have not tested yet.

private List<int> searchedCommentIDs = new List<int>();
// searchedCommentIDs is a list of already yielded comments stored
// so that malformed data does not result in an infinite loop.
public IEnumerable<Comment> GetReplies(int commentID) {
    var db = new DataClassesDataContext();
    var replies = db.Comments
        .Where(c => c.ParentCommentID == commentID 
            && !searchedCommentIDs.Contains(commentID));
    foreach (Comment reply in replies) {
        searchedCommentIDs.Add(CommentID);
        yield return reply;
        // yield return GetReplies(reply.CommentID)); // type mis-match.
        foreach (Comment replyReply in GetReplies(reply.CommentID)) {
            yield return replyReply;
        }
    }
}

2 questions:

• Is there an obvious way to improve this? (In addition, it is possible to create a view in sql with CTE.)
• Why can't I give IEnumerable <Comment>IEnumerable <Comment>, only Comment?
• Can I use SelectMany in this situation?

Answer 1

I would probably use either UDF / CTE or (for very deep structures) a stored procedure that does the same thing manually.

, , /, BETWEEN, (.. , // , ).

---

Re 2 - yield , (T IEnumerable<T>/IEnumerator<T>).

yield a IEnumerable<Comment> , IEnumerable<IEnumerable<Comment>> - ?

:

• , udf ( , ), CTE
• using, DataContext IDisposable...

:

using(var db = new MyDataContext() ) { /* existing code */ }

• LoadWith , , ...
• - , , ... , ... ( .. , API)