In SQL Server when we perform an inner join, then only the matching elements from the 2 Tables are included in the result set and Non - Matching elements are simply excluded from the result set as shown in the below diagram.
But with Linq we don't query a database tables directly , rather we query the Collection of Objects .The Entity Framework converts the tables into 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 following domain model on the basis of Employee and Department table mentioned above.
In Linq we have Join operator to perform 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 Join operator an any Collection type.In the above example of a join query,
• Department collection is an outer sequence because the query starts from it.
• First parameter in the Join method is used to specify the inner sequence which is Departments in the above example.
• Second and third parameter of Join method is 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 inner sequence x=>x.DepartmentId. If the value of both the key field 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 the sequence.
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
Join operator in query syntax works a little differently than method syntax. It requires four parameters namely outer sequence, inner sequence, key selector, and result selector. 'on' keyword is used for key selector where left side of 'equals' operator becomes the outerKeySelector and right side of 'equals' becomes the innerKeySelector.
Example 2 : Convert Example1 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();
}