Join Operators in LINQ

by Sachin Singh

Posted on Saturday, 06 February 2021

Tags: Join Operators in Linq

Many times we come across the situation where we need to fetch data from multiple tables based on some conditions. for example consider the below scenarios:
  • Display all students with their Address.
  • Display all employee with their Departments.
  • Display all departments with Employee regardless it has any employee or it has no employee.
  • Display all combination of Student and courses.
  • Display all students based on their courses, group-wise.

Student and Address are two different tables, so Entity Framework will generate two separate collections which will be DbSet of Students and DbSet of Address, similarly, Employee and Department are two different tables so Entity Framework will generate two different collections.

In the first example we want all students with their address this means we will have to join students with the address collection based on the same Address _Id property, it will obviously be an inner join. The second example will also be an inner join between Employees and Department collection based on the same Department_Id. In the third example as we have to display all departments with employees we will have to perform a left Join. In the fourth example, we want all possible combinations of student and course, it can only be achieved with a cross join between student and course collection. In the last example, we want to group students based on the courses this could be achieved with a group Join.

To solve such problems, LINQ provides us Join operators, which we will discuss in great detail.

Join simply means linking two collections based on some common attributes. In LINQ, as we query a collection, so, here joining operators are used to join two or more lists or collections based on a specified expression. In general there are 4 types of joins possible between two collections , those are

Different possible joins
Different possible joins

In Linq we have only two Join Operators
    1. Join
    2. GroupJoin

But we can achieve all the four types of joins mentioned above, just using these two join operators. In our coming chapters, we will discuss each of these Join operators in detail with examples.