The
following are the different types of joins in LINQ
Group Join
Inner Join
Left Outer Join
Cross Join
Left Outer Join
Cross Join
Group
Join produces hierarchical data structures. Each element from the first
collection is paired with a set of correlated elements from the second
collection.
While
using Group Join with Extension Method Syntax : Use GroupJoin() extension
method.
While
using Group Join with Sql Like Syntax :: Use “join” operator and the “into”
keyword.
Example 1: Group employees by Department using Extension method syntax.
var employeesByDepartment = Department.GetAllDepartments()
.GroupJoin(Employee.GetAllEmployees(),
d => d.ID,
e => e.DepartmentID,
(department, employees) => new
{
Department = department,
Employees = employees
});
foreach (var department in employeesByDepartment)
{
Console.WriteLine(department.Department.Name);
foreach (var employee
in department.Employees)
{
Console.WriteLine("
" + employee.Name);
}
Console.WriteLine();
}
Example 2: Rewrite Example 1 using SQL like syntax.
var employeesByDepartment = from
d in Department.GetAllDepartments()
join e in
Employee.GetAllEmployees()
on d.ID equals
e.DepartmentID into eGroup
select new
{
Department = d,
Employees = eGroup
};
Please note: Group Join uses the join operator and the into keyword to group the results of the join.
Inner Join in LINQ
If you
have 2 collections, and when you perform an inner join, then only the matching
elements between the 2 collections are included in the result set. Non - Matching elements are excluded from the result set.
Consider
the Department and Employee classes. Notice that, Employee Andy
does not have a department assigned. An inner join will not include his record
in the result set.
Example 1 : Join the Employees and Department collections
and print all the Employees and their respective department names.
var result = Employee.GetAllEmployees().Join(Department.GetAllDepartments(),
e => e.DepartmentID,
d => d.ID,
(employee, department) => new
{
EmployeeName = employee.Name,
DepartmentName = department.Name
});
foreach (var employee in result)
{
Console.WriteLine(employee.EmployeeName + "\t" + employee.DepartmentName);
}
Output: Notice that, in the output we don't have Andy record. This is because, Andy does not have a matching department in Department collection. So this is effectively an inner join.
Example 2 : Rewrite Example 1 using SQL like syntax.
var result = from e in Employee.GetAllEmployees()
join d in Department.GetAllDepartments()
on
e.DepartmentID equals d.ID
select new
{
EmployeeName = e.Name,
DepartmentName = d.Name
};
foreach (var employee in result)
{
Console.WriteLine(employee.EmployeeName + "\t" + employee.DepartmentName);
}
By
default, a Linq Join is an Inner Join.
Join
method joins two sequences on a Key and yields a sequence (flat result).
In
Linq, we don’t need “Inner Join” if we have Navigation Properties between the
Entities.
for
eg. between Authors and Courses, we have one-to-many relationship, So we can
simply join them using navigation properties.
CTM:
1. If we have
a navigation key then we can simply use that navigation property. eg.
var result = from c in Courses
Select new {
CourseName= c.Name,
AuthorName = c.Author.Name //
here Author is the navigation key in
// course table
};
2. If we don’t
have a navigation key, then we use a key to join the entities.
var result = from c in Courses
join a in Authors
on c.AuthorID equals a.ID
select new {CourseName = c.Name, AuthorName=a.Name};
Inner Join
on Multiple Conditions
In this case we need to make two anonymous
types (one for the left-table and one for the right-table) by using new keyword
and then we compare both the anonymous types.
Note: DataType, Name,AcceptNull or Not Null
will be exactly same for anonymous types or use Type casting.
var result = (from pd in tblProducts
join od in tblOrders on pd.ProductID equals od.ProductID join cust in tblCustomers on new {a = od.CustomerID, b= od.ContactNo}
equals
new {a=cust.CustomerID, b = cust.ContactNo}
orderby od.OrderID
select new {
od.OrderID,
pd.ProductID,
pd.ProductName,
customer
= cust.Name
}).ToList();
Difference between Group Join and Inner Join in Linq
Consider Department and Employee classes
The following query performs a
GroupJoin on the 2 tables
var result = from d in Department.GetAllDepartments()
join e in Employee.GetAllEmployees()
on d.ID equals e.DepartmentID into eGroup
select new
{
Department = d,
Employees = eGroup
};
Notice that When using Sql like syntax, we are using the join operator and the into
keyword to group the results of the join.
To perform group join using
extension method syntax, we use GroupJoin() Extension method as shown
below.
var result = Department.GetAllDepartments()
.GroupJoin(Employee.GetAllEmployees(),
d => d.ID,
e => e.DepartmentID,
(department, employees) => new
{
Department = department,
Employees = employees
});
To print the Department and Employee
Names we use 2 foreach loops as shown below.
foreach (var department in result)
{
Console.WriteLine(department.Department.Name);
foreach (var employee
in department.Employees)
{
Console.WriteLine("
" + employee.Name);
}
Console.WriteLine();
}
The following query performs an Inner
Join on the 2 lists
var result = from e in Employee.GetAllEmployees()
join d in Department.GetAllDepartments()
on e.DepartmentID equals d.ID
select new { e, d };
To perform an inner join using extension method syntax, we use Join() Extension method as shown below.
var result = Employee.GetAllEmployees()
.Join(Department.GetAllDepartments(),
e => e.DepartmentID,
d => d.ID, (employee, department) => new
{
e = employee,
d = department
});
The
above 2 queries would produce a
flat
result set
To print the Department and Employee
Names we use just 1 foreach loop as shown below.
foreach (var employee in result)
{
Console.WriteLine(employee.e.Name + "\t" + employee.d.Name);
}
In short, Join is similar to INNER JOIN in SQL and GroupJoin is similar to OUTER JOIN in SQL.
Left Outer Join in LINQ
With LEFT
OUTER JOIN all the matching elements + all the non matching elements from
the left collection are included in the result set.
Consider
the following Department and Employee classes. Notice that, Employee Mary does not have a department assigned. An
inner join will not include her record in the result set, where as a Left Outer
Join will.
Use DefaultIfEmpty()
method on the results of a group join to implement Left Outer Join
Example 1 : Implement a Left Outer Join between Employees and
Department collections and print all the Employees and their respective
department names. Employees without a department, should display "No
Department" against their name.
var result = from e in Employee.GetAllEmployees()
join d in Department.GetAllDepartments()
on e.DepartmentID equals d.ID into eGroup
from d in
eGroup.DefaultIfEmpty()
select new
{
EmployeeName = e.Name,
DepartmentName = d == null ?
"No Department" : d.Name
};
foreach (var v in result)
{
Console.WriteLine(v.EmployeeName + "\t" + v.DepartmentName);
}
Output: Notice that, we also have Mary record in spite of she not having a department. So this is effectively a left outer join.
Example 2 : Rewrite Example 1 using extension method
syntax.
var result = Employee.GetAllEmployees()
.GroupJoin(Department.GetAllDepartments(),
e => e.DepartmentID,
d => d.ID,
(emp, depts) => new { emp, depts })
.SelectMany(z => z.depts.DefaultIfEmpty(),
(a, b) => new
{
EmployeeName = a.emp.Name,
DepartmentName = b == null ? "No Department" : b.Name
});
foreach (var v in result)
{
Console.WriteLine("
" + v.EmployeeName + "\t"
+ v.DepartmentName);
}
To implement Left Outer Join, with extension method syntax we use the GroupJoin() method along with SelectMany() and DefaultIfEmpty() methods.
Cross join
produces a cartesian product i.e when we cross join two sequences, every
element in the first collection is combined with every element in the second
collection. The total number of elements in the resultant sequence will always
be equal to the product of the elements in the two source sequences. The on keyword that specifies the JOIN KEY is not required.
Consider the Department and Employee classes.
Consider the Department and Employee classes.
Example 1 : Cross Join Employees collection with Departments collections.
var result = from e in Employee.GetAllEmployees()
from d in Department.GetAllDepartments()
select new { e, d };
foreach (var v in result)
{
Console.WriteLine(v.e.Name + "\t" + v.d.Name);
}
Output: We have 5 elements in Employees collection and 2 elements in Departments collection. In the result we have 10 elements, i.e the cartesian product of the elements present in Employees and Departments collection. Notice that every element from the Employees collection is combined with every element in the Departments collection.
Example 2 : Cross Join Departments collections with Employees
collection
var result = from d in Department.GetAllDepartments()
from e in Employee.GetAllEmployees()
select new { e, d };
foreach (var v in result)
{
Console.WriteLine(v.e.Name + "\t" + v.d.Name);
}
Output: Notice that the output in this case is slightly different from Example 1. In this case, every element from the Departments collection is combined with every element in the Employees collection.
Example 3 : Rewrite Example 1 using extension method syntax
To implement Cross Join using extension method syntax, we could either use SelectMany() method or Join() method
To implement Cross Join using extension method syntax, we could either use SelectMany() method or Join() method
Implementing cross join using SelectMany()
var result = Employee.GetAllEmployees()
.SelectMany(e => Department.GetAllDepartments(),
(e, d) => new { e, d });
foreach (var v in result)
{
Console.WriteLine(v.e.Name + "\t" + v.d.Name);
}
Implementing cross join using Join()
var result = Employee.GetAllEmployees()
.Join(Department.GetAllDepartments(),
e => true,
d => true,
(e, d) => new { e, d });
foreach (var v in result)
{
Console.WriteLine(v.e.Name + "\t" + v.d.Name);
}
0 comments:
Post a Comment