Join 3 tables with lambda expression?

Question

Basically, I want the following sql query to be used as a lambda expression:

SELECT studentname, coursename, grade FROM student S, course C, grade G WHERE S.id = G.studentid AND C.coursecode = G.coursecode AND G.grade<='B'; 

I'm having problems since I need to join 3 tables.

Answer 1

Well, this looks like a query expression:

 var q = from grade in db.Grades where grade.grade <= 'B' join student in db.Students on grade.studentid equals student.studentid join course in db.Courses on grade.coursecode equals course.coursecode select new { student.studentname, course.coursename, grade.grade }; 

(I would usually use the variable name query instead of q - I just used q for formatting purposes here.)

You can translate this into explicit Join calls with lambda expressions, but I highly recommend using query expressions for complex queries like this.

Note that I have reordered the request so that the where clause is expressed as simply and efficiently as possible. In all likelihood, the SQL query planner will optimize it anyway, but for something like LINQ to Objects, this will help.