How to write that special select query

Question

Hi , I have a post class :

class Post
{
public int ID;
public int? ParentID;
}

The only difference between answer and question in post class is that question has parend id equals null.

And I want to write one query which always return question and it's answers no matter i pass to it ID of question or ID of answer for example:

I have ID=1 and don't know that it is ID of answer or question but if it is question ID i want to get that question and answers from post class

var selectquestionbyquestionid= from p in posts where p.ParentID==id || p.ID==id select p; 
(it's query only for first variant)

but if ID=1 is ID of answer and want to get question of that answer and all answers of that question

Is it possible ?

Update (following Jon Skeet advice): Class Post looks like that:

  class Post
    {
    public int ID;
    public int QuestionID;
    }

And if post is a question it has QuestionID equals it's own Id, but still i would like to write one query to do this, i know it is easier to write two

Answer 1

EDIT: With the new QuestionID field instead, I suspect this will work:

var query = from post in posts
            where post.ID == id
            join related in posts on post.QuestionID equals related.QuestionID
            select related;

(You might want to try reversing the order of the join to see if that affects the query plan for the generated SQL.)

---

(The original question used a nullable ParentID field instead of QuestionID.)

Do you absolutely have to do it in one query? I'm sure it will be possible, but it's likely to be uglier than splitting it up:

var post = posts.Where(p => p.ID == id)
                .FirstOrDefault();

if (post == null)
{
    // No such question - deal with it
}
int questonID = post.ParentID ?? post.ID;

var allPosts = posts.Where(p => p.ID == questionID || p.ParentID == questionID);

One thing to note: if you could change it from "ParentID" to "QuestionID" and make it non-nullable, such that a question had its own ID as the QuestionID, the queries would become a bit simpler.

---

If you really want to do this in one query, you may be able to do it as:

var query = from post in posts
            where post.ID == id
            let questionID = post.ParentID ?? post.ID
            from related in posts
            where related.ID == questionID || related.ParentID == questionID
            select related;

I don't know what LINQ to SQL will make of that though.