Joining Data with LINQ Query Syntax

When working with data, we often need to retrieve data from multiple tables and combine them into a single result set. In LINQ, we do this by joining data from two or more collections using the join clause.

Syntax

var result = from item1 in collection1 
             join item2 in collection2 on item1.Key equals item2.Key
             select new {
                 Property1 = item1.Property1,
                 Property2 = item2.Property2,
                 ...
             };

Example

Let's consider two classes, Employee and Department, with a one-to-many relationship between them. Here's how we can join them using the LINQ query syntax:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int DepartmentId { get; set; }
}

public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
}

List<Employee> employees = new List<Employee> {
    new Employee { Id=1, Name="John", DepartmentId=1 },
    new Employee { Id=2, Name="Jane", DepartmentId=1 },
    new Employee { Id=3, Name="Bob", DepartmentId=2 },
    new Employee { Id=4, Name="Alice", DepartmentId=2 }
};

List<Department> departments = new List<Department> {
    new Department { Id=1, Name="HR" },
    new Department { Id=2, Name="IT" },
};

var result = from e in employees
             join d in departments on e.DepartmentId equals d.Id
             select new {
                 EmployeeName = e.Name,
                 DepartmentName = d.Name
             };

In the above example, we joined the Employee and Department collections on the DepartmentId and Id properties, respectively. Then we selected a new anonymous object with the employee and department names.

Output

After executing the above query, the output will be like this:

EmployeeName: John, DepartmentName: HR
EmployeeName: Jane, DepartmentName: HR
EmployeeName: Bob, DepartmentName: IT
EmployeeName: Alice, DepartmentName: IT

Explanation

In the above syntax, we used the join keyword to join the two collections on a common key or property. In this case, we joined the Employee collection with the Department collection on the DepartmentId and Id properties, respectively.

The equals keyword is used to specify the property or key to join the collections on.

We then selected a new anonymous object with the properties we need in the output.

Use

Joining data is a common requirement when working with data, especially when we want to retrieve data from multiple tables. The LINQ query syntax provides an easy and intuitive way of joining data.

Important Points

• The join condition must be specified using the equals keyword.
• The result of the join is an IEnumerable of a new anonymous type.
• In join clause, you can use any condition, not just ==, to evaluate whether the two objects match or not.

Summary

In this tutorial, we learned how to join data from multiple collections using the LINQ query syntax. We saw an example of how to join the Employee and Department collections based on their primary key and foreign key relationship. We also saw the output and the explanation of the query, along with its syntax and important points.