Linq in C#
Language Integrated Query (LINQ) in C# provides a robust syntax for querying collections, whether they are objects in memory (like arrays or lists), or data sources like databases or XML files. One of the powerful features of LINQ is its ability to perform join operations similar to those in SQL. Here, I'll provide examples of different types of joins you can perform using LINQ: inner join, group join, and left outer join.
### Example Data Classes
Before jumping into the join examples, let's define some sample data classes:
```csharp
public class Employee
{
public int EmployeeID { get; set; }
public string Name { get; set; }
}
public class Department
{
public int DepartmentID { get; set; }
public string Name { get; set; }
}
public class EmployeeDepartment
{
public int EmployeeID { get; set; }
public int DepartmentID { get; set; }
}
```
### 1. Inner Join
This join returns only those records where there is a match in both joined tables.
```csharp
var employees = new List<Employee>
{
new Employee { EmployeeID = 1, Name = "John" },
new Employee { EmployeeID = 2, Name = "Jane" },
new Employee { EmployeeID = 3, Name = "Doe" }
};
var departments = new List<Department>
{
new Department { DepartmentID = 1, Name = "IT" },
new Department { DepartmentID = 2, Name = "HR" }
};
var employeeDepartments = new List<EmployeeDepartment>
{
new EmployeeDepartment { EmployeeID = 1, DepartmentID = 1 },
new EmployeeDepartment { EmployeeID = 2, DepartmentID = 2 }
};
var innerJoinQuery = from emp in employees
join empDep in employeeDepartments on emp.EmployeeID equals empDep.EmployeeID
join dep in departments on empDep.DepartmentID equals dep.DepartmentID
select new
{
EmployeeName = emp.Name,
DepartmentName = dep.Name
};
foreach (var item in innerJoinQuery)
{
Console.WriteLine($"Employee: {item.EmployeeName}, Department: {item.DepartmentName}");
}
```
### 2. Group Join
Group join combines elements from two sequences based on key equality and groups the results.
```csharp
var groupJoinQuery = from dep in departments
join emp in employees
on dep.DepartmentID equals emp.EmployeeID into deptEmployees
select new
{
DepartmentName = dep.Name,
Employees = deptEmployees
};
foreach (var dept in groupJoinQuery)
{
Console.WriteLine($"Department: {dept.DepartmentName}");
foreach (var emp in dept.Employees)
{
Console.WriteLine($" Employee: {emp.Name}");
}
}
```
### 3. Left Outer Join
A left outer join returns all records from the left table, and the matched records from the right table. If there is no match, the result is `null` on the side of the right table.
```csharp
var leftOuterJoinQuery = from emp in employees
join empDep in employeeDepartments
on emp.EmployeeID equals empDep.EmployeeID into empDeptResult
from subEmpDep in empDeptResult.DefaultIfEmpty()
join dep in departments
on subEmpDep?.DepartmentID equals dep.DepartmentID into depResult
from subDep in depResult.DefaultIfEmpty()
select new
{
EmployeeName = emp.Name,
DepartmentName = subDep?.Name ?? "No Department"
};
foreach (var item in leftOuterJoinQuery)
{
Console.WriteLine($"Employee: {item.EmployeeName}, Department: {item.DepartmentName}");
}
