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.

Create-Data-Layer.gif

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.