Convert Sql Query to expression in LINQ

Question

Two tables handle Student records: Students_Subjects and Students_Tests. The tables are in an m to n relationship. With the sql below we list the Students for Subject 44 that have taken the test. (admittedly there are easier ways to get to the same result).

Here is a SQL Query I want to convert

SELECT *
FROM Students_Subjects AS s 
LEFT OUTER JOIN Students_Tests AS r
    ON s.Stud_National_Id = r.Stud_National_Id 
    AND s.Subject_Id = r.Subject_Id
WHERE s.Subject_Id = 44

How can we do this with a Linq Expression?

Answer 1

Please note that I had to change the names of the columns. When joining 2 tables on more than one column, the columns you join on must have identical names.

    void Main()
    {
        Students_Subjects.Add(new Student_Subject() { Subject_ID = 44, Stud_National_ID = 157 });
        Students_Tests.Add(new Student_Test() { Subject_ID = 44, Stud_National_ID = 157 });
    
        var q = Students_Subjects
            .Join(Students_Tests,s => new{s.Stud_National_ID,s.Subject_ID},r=>new {r.Stud_National_ID,r.Subject_ID},(s,r) => new {s.Stud_National_ID,s.Subject_ID})
            .Where(s => s.Subject_ID == 44);
    }
    class Student_Subject
    {
        public int Subject_ID {get;set;}
        public int Stud_National_ID { get; set; }
    }
    List<Student_Subject> Students_Subjects = new List<Student_Subject>();
    class Student_Test
    {
        public int Subject_ID { get; set; }
        public int Stud_National_ID { get; set; }
    }
    List<Student_Test> Students_Tests = new List<Student_Test>();