LINQ to SQL - Self Join Child to Parent (Same Tabl

Question

For some reason I'm having a hard time wrapping my mind around how to do this.

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

So for example, very simply there might a Posts table that looks like this:

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

So imagine that a very simple Posts table is defined like this:

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]

Here's the problem. I would like to find all replies made to a particular User.

So, for example, if I wanted to find all replies made to UserID #3 above, I should come up with this:

ID,ParentID,Content,UserID
===========================
2,1,"Reply to Hello",7
3,1,"Another Reply to Hello",4

That's because UserID #3 posted post ID #1, and those two were replies.

It is simple to find all parent posts by UserID #3. I would just do this:

var posts = (from db.Posts
  where p.UserID == 3
  && p.ParentID == 0 // this is a parent
  select p).ToList();

Likewise, to find all child (reply) posts not made by UserID #3, I would just do this:

var posts = (from db.Posts
  where p.UserID != 3
  && p.ParentID != 0 // this is a child
  select p).ToList();

But how do I find all replies made ONLY to UserID #3???

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.

If I could do this, it would work:

int userId = 3;
var posts = (from p in db.Posts
  where p.UserID != 3
  && p.ParentID != 0 // this is a child
  && DID_USER_CREATE_POST(userId,p.ID) // can't do this -- since this imaginary C# function won't work here
  select p).ToList();

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.

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

Thanks for any assistance!

Answer 1

I haven't tried this against a data structure like you have, but it may get you there:

var id = 3;
var query =
from p in db.Posts
join r in db.Posts on p.ID equals r.ParentId
where p.UserId == id && p.ParentId == 0
select new { Post = p, Reply = r};

Answer 2

If you have proper Foreign Key setup, the LINQ should see the reference, so your query would be something like this.

var posts = from p in db.Posts
  where p.Parent != null //Looking for children only
  && p.Parent.UserId == 3 //UserId of the parent post

The reference object might be called differently. If you don't see any of this kind of object, you can achieve the same thing by join as well.

from post in db.Posts
join parent in db.Posts on post.ParentId equals parent.ID
where parent.UserId == 3
select post

I omitted the other simpler where clauses.