LINQ Left Join -Where statement issue

Question

I have sort of figured out the original question I had (although if there is a better way I'd love to hear it). I'm having an issue now with the Where clause

where answer.TeacherID == "1234"

I need all 7 rows to return not just the 4 rows that have the TeacherID. In my current statement (below), no matter where I put the Where it always just returns the 4 rows... What am I doing incorrectly?

Here is my Updated LINQ statement (needing the Where clause still)

var resultSet = (from question in db.Questions
                 join TeacherDetail in db.AnswerDetails on question.QuestionID 
                 equals TeacherDetail.QuestionID into qListTeacher
                 from TeacherDetail in qListTeacher.DefaultIfEmpty()
                 join answer in db.Answers on TeacherDetail.AnswerKey 
                 equals answer.AnswerKey into aList
                 from answer in aList.DefaultIfEmpty()
                 join LeaderDetail in db.AnswerDetails on question.QuestionID 
                 equals LeaderDetail.QuestionID
                 into qListLeader
                 from LeaderDetail in qListLeader.DefaultIfEmpty()
                 select new ProfessionalObject
                 {
                     QuestionID = question.QuestionID,
                     IndicatorID = question.ID,
                     QuestionDescription = question.Description,
                     AnswerKey = answer.AnswerKey,
                     TeacherID = answer.TeacherID,
                     LeaderID = answer.LeaderID,
                     StatusKey = answer.StatusKey,
                     TeacherAnswerDetailKey = (answer.TeacherID != null ? TeacherDetail.AnswerDetailKey : 0),
                     TeacherAnswerOptionKey = (answer.TeacherID != null ? TeacherDetail.AnswerOptionKey : 0),
                     TeacherComment = (answer.LeaderDPSID == null ? TeacherDetail.Comment : ""),
                     LeaderAnswerDetailKey = (answer.LeaderID != null ? LeaderDetail.AnswerDetailKey : 0),
                     LeaderAnswerOptionKey = (answer.LeaderID != null ? LeaderDetail.AnswerOptionKey : 0),
                     LeaderComment = (answer.LeaderDPSID != null ? LeaderDetail.Comment : ""),                                 
                     }).ToList();

I have Questions that I would like to package into my object regardless if there are answers to them. I have a Leader and a Teacher that have the ability to answer these questions

So I want a resultSet that would return All of the Questions and I want the Teacher Comments to go into the Teacher Properties and the Leader Comments to go into the Leader Properties.

For Example:

QuestionID: 1, QuestionDescription: Question 1, TeacherComment = Null, LeaderComment = "Leader Answer" , TeacherID: 12345, LeaderID: 9999
QuestionID: 2, QuestionDescription: Question 2, TeacherComment = Null, LeaderComment = "Leader Answer 2", TeacherID: 12345, LeaderID: 9999
QuestionID: 3, QuestionDescription: Question 3, TeacherComment = "Teacher Answer", LeaderComment = NULL , TeacherID: 12345, LeaderID: NULL
QuestionID: 4, QuestionDescription: Question 4, TeacherComment = "Teacher Answer 2", LeaderComment = NULL , TeacherID: 12345, LeaderID: NULL
QuestionID 5, QuestionDescription: Question 5, TeacherComment = NULL, LeaderComment = NULL , TeacherID: NULL,  LeaderID: NULL
QuestionID 6, QuestionDescription: Question 6, TeacherComment = NULL, LeaderComment  = NULL , TeacherID: NULL,  LeaderID: NULL
QuestionID 7, QuestionDescription: Question 7, TeacherComment = NULL, LeaderComment = NULL, TeacherID: NULL,  LeaderID: NULL

The Answer table has a PK on the AnswerKey there is also a TeacherID and a LeaderID The AnswerDetail Table contains the QuestionID and the Comments to the questions.

I've tried adding where statements to split the Teacher Info (i.e. Where TeacherID != NULL and LeaderID == NULL As well as for the Leader info (TeacherID != NULL and LeaderID !=NULL). But they just limited my result set

Answer Records:

AnswerKey: 1 TeacherID: 1234 LeaderID: 9999

AnswerKey: 5 TeacherID: 1234 LeaderID: NULL

AnswerDetail Records:

AnswerKey: 1 QuestionID: 1 Comment: Leader Answer

AnswerKey: 1 QuestionID: 2 Comment: Leader Answer 2

AnswerKey: 5 Question: 3 Comment: Teacher Answer

AnswerKey: 5 Question: 4 Comment: Teacher Answer 2

Question Records:

QuestionID: 1 QuestionDescription: Question #1

QuestionID: 2 QuestionDescription: Question #2

QuestionID: 3 QuestionDescription: Question #3

QuestionID: 4 QuestionDescription: Question #4

QuestionID: 5 QuestionDescription: Question #5

QuestionID: 6 QuestionDescription: Question #6

QuestionID: 7 QuestionDescription: Question #7

UPDATE:

The above resultSet returns the 7 questions correctly and the Comments are technically correct. What I'm hoping to achieve is if the leader has comments then I want them to go into the Leader Properties and if the teacher has comments then I want them to go into the Teacher Properties.

The logic for the Leader is if the TeacherID != NULL and the LeaderID != NULL The logic for the Teacher is if the TeacherID != NULL and the LeaderID == NULL

This is my class:

public class ProfessionalObject
{
    public int? QuestionID { get; set; }
    public string IndicatorID { get; set; }
    public string QuestionDescription { get; set; }
    public int? AnswerKey { get; set; }
    public string TeacherID { get; set; }
    public string LeaderID { get; set; }
    public int? StatusKey { get; set; }
    public int? TeacherAnswerDetailKey { get; set; }
    public int? TeacherAnswerOptionKey { get; set; }
    public string TeacherComment { get; set; }
    public int? LeaderAnswerDetailKey { get; set; }
    public int? LeaderAnswerOptionKey { get; set; }
    public string LeaderComment { get; set; }

    public ProfessionalObject()
    {
        QuestionID = 0;
        IndicatorID = "";
        QuestionDescription = "";
        AnswerKey = 0;
        TeacherID = "";
        LeaderID = "";
        StatusKey = 0;
        TeacherAnswerDetailKey = 0;
        TeacherAnswerOptionKey = 0;
        TeacherComment = "";
        LeaderAnswerDetailKey = 0;
        LeaderAnswerOptionKey = 0;
        LeaderComment = "";
    }
}

Answer 1

I wound up using @ShlomiBorovitz suggestion to break up my pieces and the join them at the end:

var questionSet = ( from question in Questions select question);
var TeacherAnswer = ( from detail in AnswerDetails 
                    join answer in Answers on detail.AnswerKey equals answer.AnswerKey
                    where answer.TeacherID == "12345" && answer.LeaderID == null
                    select new { detail, answer});

var LeaderAnswer = (from detail in AnswerDetails 
                    join answer in Answers on detail.AnswerKey equals answer.AnswerKey
                    where answer.TeacherID == "12345" && answer.LeaderID != null
                    select new { detail, answer});                                       



var combination = ( from q in questionSet
                    join tDetail in TeacherAnswer on q.QuestionID equals tDetail.detail.QuestionID into tList
                    from tDetail in tList.DefaultIfEmpty()
                    join lDetail in LeaderAnswer on q.QuestionID equals lDetail.detail.QuestionID into lList
                    from lDetail in lList.DefaultIfEmpty()
                    select new {q,tList, lList });



var resultSet = (from combo in combination
                select new 
                {
                    QuestionID = (combo.q.QuestionID == null ? 0 : combo.q.QuestionID),
                    IndicatorID = combo.q.ID,
                    QuestionDescription = combo.q.Description,
                    TeacherAnswerKey = (combo.tList.Select(x => x.detail.AnswerKey).FirstOrDefault() == null ? 0 : combo.tList.Select(y => y.detail.AnswerKey).FirstOrDefault()),
                    LeaderAnswerKey = (combo.lList.Select(x => x.detail.AnswerKey).FirstOrDefault() == null ? 0 : combo.lList.Select(y => y.detail.AnswerKey).FirstOrDefault()),
                    TeacherID = (combo.tList.Select(y => y.answer.TeacherID).FirstOrDefault() == null ? "" : combo.tList.Select(y => y.answer.TeacherID).FirstOrDefault()),
                    LeaderID = (combo.lList.Select(x => x.answer.LeaderID).FirstOrDefault() == null ? "" : combo.lList.Select(y => y.answer.LeaderID).FirstOrDefault()),
                    TeacherStatusKey = (combo.tList.Select(x => x.answer.StatusKey).FirstOrDefault() == null ? 0 : combo.tList.Select(y => y.answer.StatusKey).FirstOrDefault()),
                    LeaderStatusKey = (combo.lList.Select(x => x.answer.StatusKey).FirstOrDefault() == null ? 0 : combo.lList.Select(y => y.answer.StatusKey).FirstOrDefault()),
                    TeacherAnswerDetailKey = (combo.tList.Select(x => x.answer.TeacherDPSID).FirstOrDefault() == null ? 0 : combo.tList.Select(y => y.detail.AnswerDetailKey).FirstOrDefault()),
                    TeacherAnswerOptionKey = (combo.tList.Select(x => x.answer.TeacherDPSID).FirstOrDefault() == null ? 0 : combo.tList.Select(y => y.detail.AnswerOptionKey).FirstOrDefault()),
                    TeacherComment = combo.tList.Select(y => y.detail.Comment).FirstOrDefault(),
                    LeaderAnswerDetailKey = (combo.lList.Select(x => x.answer.LeaderID).FirstOrDefault() == null ? 0 : combo.lList.Select(y => y.detail.AnswerDetailKey).FirstOrDefault()),
                    LeaderAnswerOptionKey = (combo.lList.Select(x => x.answer.LeaderDPSID).FirstOrDefault() == null ? 0 : combo.lList.Select(y => y.detail.AnswerOptionKey).FirstOrDefault()),
                    LeaderComment = combo.lList.Select(y => y.detail.Comment).FirstOrDefault(),
                }).ToList();

Answer 2

That's what Where does. Filter the collection, from all the data which doesn't fits the condition.

You may remove the where, if you don't want to filter out those items