In SQL Server, when we perform an INNER join, only the matching elements from the 2 tables are included in the result set, and non-matching elements are simply excluded, as shown in the following diagram:
But, with LINQ, we don't query a database table directly; rather, we query the collection of objects. The Entity Framework converts the tables into a collection of objects, and we simply query those collections (DbSets), and then the LINQ provider translates our query to corresponding T-SQL.
The Entity Framework will generate the following domain model based on the Employee and Department tables mentioned above:
In LINQ, we have the Join operator to perform the Inner Join. If we have 2 collections, and we perform an Inner Join, then only the matching elements from the 2 collections are included in the result set. Non-matching elements are excluded.
Join in Method Syntax
public static void Main()
{
OrganizationContext db = new OrganizationContext();
var result= db.Departments.Join(db.Employees, x => x.Id, x => x.DepartmentId, (d, e) => new
{
departmentName = d.Name,
EmployeeName = e.Name,
Salary = e.Salary
});
foreach (var item in result)
{
Console.WriteLine("{0}|{1}|{2}",item.departmentName,item.EmployeeName,item.Salary);
}
Console.ReadLine();
}
Like all other LINQ operators, Join is an extension method on IQueryalbe<T>, which means we can apply the Join operator to any Collection type. In the above example of a join query,
- Department collection is an outer sequence because the query starts from it.
- The first parameter in the Join method is used to specify the inner sequence, which is Departments in the above example.
- The second and third parameters of the Join method are used to specify a field whose value should be matched using a lambda expression to include an element in the result. The key selector for the outer sequence x =>x.Id indicates that the Id field of each element of Department should be matched with the key of the inner sequence x=>x.DepartmentId. If the value of both the key fields is matched, then include that element in the result.
- The last parameter in the Join method is an expression to formulate the result. In the above example, the result selector includes the Department's Name, Employee's Name, and Employee's Salary property of both sequences.
Output: Notice that, in the output, we don't have any record of Department's Id 3 (BA Department). This is because the BA department does not have a matching employee in the Employee collection. In other words, there is no employee in the BA department, so the BA department has been excluded from the result. So this is effectively an Inner Join.
Join in Query Syntax
The Join operator in query syntax works a little differently than the method syntax. It requires four parameters, namely the outer sequence, inner sequence, key selector, and result selector. The 'on' keyword is used for a key selector where the left side of the 'equals' operator becomes the outerKeySelector and the right side of 'equals' becomes the innerKeySelector.
Example: Convert the First Example to Sql-Like Syntax
public static void Main()
{
OrganizationContext db = new OrganizationContext();
var result = from d in db.Departments
join e in db.Employees
on d.Id equals e.DepartmentId
select new
{
departmentName = d.Name,
EmployeeName = e.Name,
Salary = e.Salary
};
foreach (var item in result)
{
Console.WriteLine("{0}|{1}|{2}",item.departmentName,item.EmployeeName,item.Salary);
}
Console.ReadLine();
}
