title: Linq-to-Sql: recursively get children
body: <p>I have a Comment table which has a CommentID and a ParentCommentID. I am trying to get a list of all children of the Comment. This is what I have so far, I haven't tested it yet. </p>
  
  <pre><code>private List&lt;int&gt; searchedCommentIDs = new List&lt;int&gt;();
  // searchedCommentIDs is a list of already yielded comments stored
  // so that malformed data does not result in an infinite loop.
  public IEnumerable&lt;Comment&gt; GetReplies(int commentID) {
      var db = new DataClassesDataContext();
      var replies = db.Comments
          .Where(c =&gt; c.ParentCommentID == commentID 
              &amp;&amp; !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;
          }
      }
  }
  </code></pre>
  
  <p>2 questions: </p>
  
  <ol>
  <li>Is there any obvious way to improve this? (Besides maybe creating a view in sql with a CTE.)</li>
  <li>How come I can't yield a <code>IEnumerable &lt;Comment&gt;</code> to an IEnumerable <code>&lt;Comment&gt;</code>, only <code>Comment</code> itself?</li>
  <li>Is there anyway to use SelectMany in this situation?</li>
  </ol>