Inner Join, Cross Join and Left Outer Join With LINQ to SQL

The join operations in this article are done using COURSE and STUDENT tables. So now I am going to explain the joins to be done using LINQ to SQL step-by-step.

Step 1: Create COURSE and STUDENT Tables in the database as in the following:

 CREATE TABLE COURSE
 (
     course_id int IDENTITY(1,1) PRIMARY KEY,
     course_name nvarchar(70) NOT NULL,
     course_desc nvarchar(255) NULL,
     modified_date date NULL,
 )
 CREATE TABLE STUDENT
 (
     student_id int IDENTITY(1,1) PRIMARY KEY,
     student_name nvarchar(70),
     student_city nvarchar(30),
     course_id int NOT NULL
 )
 
Step 2: Define foreign key constraints on the STUDENT table as in the following:

 ALTER TABLE STUDENT
 ADD CONSTRAINT [FK_STUDENT_COURSE] FOREIGN KEY (course_id)REFERENCES COURSE(course_id)
 

Inner Join in LINQ to SQL
 
 OperationDataContext odDataContext = new OperationDataContext();
 var studentInfo = from student in odDataContext.STUDENTs
 join course in odDataContext.COURSEs
 on student.course_id equals       course.course_id
 select new { student.student_name, student.student_city, course.course_name, course.course_desc };
 
In the above code we join the STUDENT table and the COURSE table using the "join" keyword and using the "on" keyword join the tables by the course_id field of both tables. It returns all rows with a common course_id in both tables.
 
 Cross Join in LINQ to SQL
 
 OperationDataContext odDataContext = new OperationDataContext();
 var studentInfo = from student in odDataContext.STUDENTs
 from course in odDataContext.COURSEs
 select new { student.student_name, student.student_city, course.course_name, course.course_desc };
 
In the above code we are doing a cross-join on both the STUDENT table and the COURSE table. We get all rows from both tables and the total rows are STUDENT table rows * COURSE table rows.
 
Left Join in LINQ to SQL
 
 OperationDataContext odDataContext = new OperationDataContext();
 var courseInfo = from course in odDataContext.COURSEs
 join student in odDataContext.STUDENTs
 on course.course_id equals  student.course_id into studentInfo
 from students in studentInfo.DefaultIfEmpty()
 select new 
 {
     STUDENTNAME = (students.student_name == null)? NULL":students.student_name, 
     STUDENTCITY = (students.student_city == null)? "NULL":students.student_city,
     COURSENAME = course.course_name, 
     COUSREDESCRIPTION = course.course_desc 
 };
 
In above code we are doing a Left Join. Here the left table is COURSE. So it will return all rows from the course table not depends STUDENT table course_id field. If the course_id field value is in the COURSE table but not in the STUDENT table then in the row course_name and course_desc fields will show and student_name and student_city fields will show NULL. Here the output will be the total number of rows in the COURSE table.