Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Part 22 - Inner Join in LINQ

Suggested Videos
Part 19 - Group by multiple keys in linq
Part 20 - Element Operators in LINQ
Part 21 - Group Join in LINQ



The following are the different types of joins in LINQ
Group Join - Discussed in Part 21
Inner Join - We will discuss in this video
Left Outer Join - Later Video
Cross JoinLater Video



In this video we will discuss implementing 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.

Let us understand Inner Join with an example. Consider the following 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.

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

    public static List<Department> GetAllDepartments()
    {
        return new List<Department>()
        {
            new Department { ID = 1, Name = "IT"},
            new Department { ID = 2, Name = "HR"},
            new Department { ID = 3, Name = "Payroll"},
        };
    }
}

public class Employee
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int DepartmentID { get; set; }

    public static List<Employee> GetAllEmployees()
    {
        return new List<Employee>()
        {
            new Employee { ID = 1, Name = "Mark", DepartmentID = 1 },
            new Employee { ID = 2, Name = "Steve", DepartmentID = 2 },
            new Employee { ID = 3, Name = "Ben", DepartmentID = 1 },
            new Employee { ID = 4, Name = "Philip", DepartmentID = 1 },
            new Employee { ID = 5, Name = "Mary", DepartmentID = 2 },
            new Employee { ID = 6, Name = "Valarie", DepartmentID = 2 },
            new Employee { ID = 7, Name = "John", DepartmentID = 1 },
            new Employee { ID = 8, Name = "Pam", DepartmentID = 1 },
            new Employee { ID = 9, Name = "Stacey", DepartmentID = 2 },
            new Employee { ID = 10, Name = "Andy"}
        };
    }
}

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.
linq inner join example c#

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);
}

linq tutorial

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.