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?

Welcome to Stack Overflow. Can you please supplement your question with the SQL table definitions. Also, precisely where in your code are you having problems? Are you using ORM, such as Entity Framework? — Daniel Dearlove, Aug 09 '21 at 11:44
SQL can't be converted into LINQ. At best, you can write LINQ that translates into SQL that returns the same result. For us to be able to help you we need to see the C# class model and how it is mapped to the database model. Also, please explain where specifically you experience problems trying to achieve this. (A description like "not work" doesn't mean much). — Gert Arnold, Aug 09 '21 at 11:58
What @GertArnold and I are trying to ask is that you expand your question so it it a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example). That way, we know exactly what your problem is and exactly how to help you. — Daniel Dearlove, Aug 09 '21 at 12:04
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Aug 09 '21 at 21:15

MarcG · Accepted Answer · 2021-08-09T17:37:41.250

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>();

The idea is that people coming from a SQL background should not automatically resort to `join` in LINQ. Usually it's possible to create a class model with associations (references and/or collections) and joins aren't necessary. — Gert Arnold, Aug 09 '21 at 17:53

Convert Sql Query to expression in LINQ

1 Answers1