How do I get a left outer join in LINQ.

[abhijit]

Here is my code.

namespace LINQMatching
{
    class Student
    {
        public int StudentId { get; set; }
        public string Name { get; set; }
        public DateTime DateOfBirth { get; set; }
    }
    
    class Exam
    {
        public int TestId { get; set; }
        public string Name { get; set; }
    }
 
    class ExamScore
    {
        public int StudentId { get; set; }
        public int TestId { get; set; }
        public DateTime TestDate { get; set; }
        public decimal ? Score { get; set; }
    }
 
    class StudentScore
    {
        public int StudentId { get; set; }
        public string StudentName { get; set; }
        public int TestId { get; set; }
        public string TestName { get; set; }
        public decimal? TestScore { get; set; }
        public DateTime TestDate { get; set; }
    }
 
    class LinqMatching
    {
       static List<Student> students = new List<Student>();
       static List<Exam> exams = new List<Exam>();
       static List<ExamScore> scores = new List<ExamScore>();
 
        static void PopulateLists()
        {
            students = new List<Student>
                {
                    new Student {StudentId = 101, Name = "Steyn Jacob", DateOfBirth = new DateTime(1987, 1, 1)},
                    new Student {StudentId = 102, Name = "Elisha Martin", DateOfBirth = new DateTime(1989, 1, 1)},
                    new Student {StudentId = 103, Name = "Chad Gomes", DateOfBirth = new DateTime(1989, 11, 21)},
                    new Student {StudentId = 104, Name = "Jim Martin", DateOfBirth = new DateTime(1989, 12, 1)}
                };
            exams = new List<Exam>
                {
                    new Exam {TestId = 10, Name = "English"},
                    new Exam {TestId = 11, Name = "Math"},
                    new Exam {TestId = 13, Name = "Science"},
                    new Exam {TestId = 14, Name = "Geography"},
                    new Exam {TestId = 16, Name = "French"},
                    new Exam {TestId = 15, Name = "History"}
                };
            scores = new List<ExamScore>
                {
                    new ExamScore {TestId = 10, Score = 9.11M, StudentId = 101, TestDate = new DateTime(2013, 1, 1)},
                    new ExamScore {TestId = 11, Score = 8.11M, StudentId = 101, TestDate = new DateTime(2013, 2, 1)},
                    new ExamScore {TestId = 13, Score = 8.11M, StudentId = 101, TestDate = new DateTime(2013, 3, 1)},
                    new ExamScore {TestId = 10, Score = 9.11M, StudentId = 102, TestDate = new DateTime(2013, 1, 1)},
                    new ExamScore {TestId = 10, Score = 9.11M, StudentId = 104, TestDate = new DateTime(2013, 1, 1)}
                };
        }
 
        static void Main(string[] args)
        {
            var queryOut = from s in students
                           join e in scores on s.StudentId equals e.StudentId
                           join t in exams on e.TestId equals t.TestId
                           select new StudentScore
                               {
                                   StudentName = s.Name,
                                   TestId = e.TestId,
                                   TestName = t.Name,
                                   TestDate = e.TestDate,
                                   StudentId = s.StudentId,
                                   TestScore = e.Score
                               };
            foreach (var s in queryOut)
            {
                Console.WriteLine("{0} {1} {2} {3} {4}",s.StudentId, s.StudentName, s.TestName, s.TestDate, s.TestScore);
            }
        }
    }
}
 
i wish to know the syntax for getting this join to work. The output I expect from the query is
StudentName - TestName - TestDate - TestScore
 
The list must contain all the students and all the tests. If a student hasn't taken a test, the testscore and testdate would be empty.
How can I form this query?

[jmcilhinney]

You use the DefaultIfEmpty method on the right-hand list to perform a left outer join:

http://www.hookedonlinq.com/OuterJoinSample.ashx

[abhijit]

You use the DefaultIfEmpty method on the right-hand list to perform a left outer join:

http://www.hookedonlinq.com/OuterJoinSample.ashx

That would work very well with two tables, but with three lists, I run into a bit of a problem.

Here's my code to join the three lists.

var leftJoin = from s in students
                           join e in scores on s.StudentId equals e.StudentId into joinedScores
                           from jS in joinedScores.DefaultIfEmpty()
                               select new
                                   {
                                     score = jS ==null ? 0 :  jS.Score,
                                     testDate = jS == null ? "Did Not Take Test" : jS.TestDate.ToString("yyyy-dd-MM"),
                                     testId = jS == null ? 0 :jS.TestId,
                                     studentName = s.Name
                                   };
 
            var secondLeft = from t in exams join lj in leftJoin on t.TestId equals lj.testId into joinedTests
            from jt in joinedTests.DefaultIfEmpty()
                        select new 
                                {
                                    testName = t.Name,
                                    testId = t.TestId,
                                    score = jt == null ? 0 : jt.score,
                                    testDate = jt == null ? "Did not take test" : jt.testDate,
                                    studentName = jt == null ? "Not available" : jt.studentName
                                };
 
            
            foreach (var lJ in secondLeft)
            {
                Console.WriteLine("{3} \t{0}\t{1}\t{2}", lJ.score, lJ.studentName, lJ.testDate, lJ.testName);
            }

here is the output it produces

English         9.11    Steyn Jacob     2013-01-01
English         9.11    Elisha Martin   2013-01-01
English         9.11    Jim Martin      2013-01-01
Math    8.11    Steyn Jacob     2013-01-02
Science         8.11    Elisha Martin   2013-01-03
Geography       0       Not available   Did not take test
French  0       Not available   Did not take test
History         0       Not available   Did not take test 

I would instead prefer to have a listing that has all the students as well as all the tests.

[jmcilhinney]

I don't have time to try it myself right now but, if my understanding of what you want is correct, here's what I would try. Try performing two queries: one that gets the data including all students and another that gets the data including all exams. They will both returns lists of instances of the same type though. You can then perform a Union and a Distinct and that should give you one copy of everything. You may have to override Equals or GetHashCode or both in the result type in order to get Distinct to work as required.