title: LINQ to SQL - Self Join Child to Parent (Same Table)
body: <p>For some reason I'm having a hard time wrapping my mind around how to do this.</p>
  
  <p>I have a table of message posts. (It is part of a very large existing legacy system, and I can't change the way the tables are defined.) There is only 1 table of posts. It is a two-level hierarchy. Each post is either a "parent" or a "child". Children are posts replying to parents. (There are no other levels.)</p>
  
  <p>So for example, very simply there might a Posts table that looks like this:</p>
  
  <pre><code>ID,ParentID,Content,UserID
  ===========================
  1,0,"Hello",3
  2,1,"Reply to Hello",7
  3,1,"Another Reply to Hello",4
  4,0,"New Post",2
  ...etc...
  </code></pre>
  
  <p>So imagine that a very simple Posts table is defined like this:</p>
  
  <pre><code>INT ID [identity value for the post],
  INT ParentID [0 == no parent, otherwise the value is the parent identity value]
  TEXT Content [just imagine it is text]
  INT UserID [the author of the post]
  </code></pre>
  
  <p>Here's the problem. I would like to find all replies made to a particular User.</p>
  
  <p>So, for example, if I wanted to find all replies made to UserID #3 above, I should come up with this:</p>
  
  <pre><code>ID,ParentID,Content,UserID
  ===========================
  2,1,"Reply to Hello",7
  3,1,"Another Reply to Hello",4
  </code></pre>
  
  <p>That's because UserID #3 posted post ID #1, and those two were replies.</p>
  
  <p>It is simple to find all parent posts by UserID #3. I would just do this:</p>
  
  <pre><code>var posts = (from db.Posts
    where p.UserID == 3
    &amp;&amp; p.ParentID == 0 // this is a parent
    select p).ToList();
  </code></pre>
  
  <p>Likewise, to find all child (reply) posts not made by UserID #3, I would just do this:</p>
  
  <pre><code>var posts = (from db.Posts
    where p.UserID != 3
    &amp;&amp; p.ParentID != 0 // this is a child
    select p).ToList();
  </code></pre>
  
  <p>But how do I find all replies made ONLY to UserID #3???</p>
  
  <p>Imagine the Posts table has 1,000,000 rows from the last 10 years, and there may only be 3 rows that are replies, so I can't exactly just brute all of it into some List and then sort through. I need to do 1 LINQ to SQL query that only returns the needed 3 rows.</p>
  
  <p>If I could do this, it would work:</p>
  
  <pre><code>int userId = 3;
  var posts = (from p in db.Posts
    where p.UserID != 3
    &amp;&amp; p.ParentID != 0 // this is a child
    &amp;&amp; DID_USER_CREATE_POST(userId,p.ID) // can't do this -- since this imaginary C# function won't work here
    select p).ToList();
  </code></pre>
  
  <p>I think I need to do some sort of self-join (???), since the parent and child are in the same table, to come up with those 3 needed rows.. but I have yet to figure out how to do this with the existing table structure.</p>
  
  <p>Does anyone have any idea how I can accomplish this using LINQ to SQL. I'm using C# and the code will be in an ASP.NET MVC controller (that emits RSS).</p>
  
  <p>Thanks for any assistance!</p>