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)
 
Step 3: Create Data Layer
 
Create a dbml file (Operation.dbml in this article) and using the Object Relation Designer create a data context class for the STUDENT and COUSRE tables.


 
In the above figure we drag and drop both the COURSE and STUDENT tables onto the designer map of the Operation.dbml file and that shows the relationship between the COURSE and STUDENT tables.
 
Step 4: 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.
 
Step 5: 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.
 
Step 6: 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.