Introduction

In this article, I will demonstrate how we can perform join operations with SQL database using Language-Integrated Query (LINQ).

There are 4 types of join in LINQ

1. Group Join
2. Inner Join
3. Left Outer Join
4. Cross Join

Group Join

Group join produces hierarchical data structure. Each element from the first collection is paired with a set of correlated elements from the second collection

Inner Join

If you have 2 tables or collections, when you perform an inner join, then only the matching rows or elements between the 2 table or collections are included in the result set.

Left Outer Join

All the matching elements and non-matching elements from the left collection are included in the result set.

Cross Join

Cross join produces a Cartesian 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 result set will always be equal to the product of the elements in the two source sequences.

Step 1

Open SQL server 2014 and create Employee table and insert some records

  1. CREATE TABLE [dbo].[Employee](
  2. [ID] [int] IDENTITY(1,1) NOT NULL,
  3. [Name] [nvarchar](50) NULL,
  4. [Position] [nvarchar](50) NULL,
  5. [Office] [nvarchar](50) NULL,
  6. [Salary] [money] NULL,
  7. [DepartmentId] [int] NULL,
  8. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  9. (
  10. [ID] ASC
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  12. ) ON [PRIMARY]
  14. GO
  1. CREATE TABLE [dbo].[Department](
  2. [ID] [int] IDENTITY(1,1) NOT NULL,
  3. [Department_Name] [nvarchar](50) NULL,
  4. CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED
  5. (
  6. [ID] ASC
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  8. ) ON [PRIMARY]
  10. GO

Step 2

Open Visual Studio 2015 and create a new console application with a meaningful name.

Step 3

Add Entity Framework now. For that, right click on Models folder, select Add, then select New Item, then click on it.

Screenshot for adding Entity Framework 1

How To Use Join Operations With Database Using LINQ

After clicking on new item, you will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name DBModels (this name is not mandatory you can give any name) and click on Add.

Screenshot for adding Entity Framework 2

How To Use Join Operations With Database Using LINQ

After you click on "Add a window", the wizard will open, choose EF Designer from the database and click next.

Screenshot for adding Entity Framework 3

How To Use Join Operations With Database Using LINQ

After clicking on Next a window will appear. Choose New Connection. Another window will appear, add your server name if it is local then enter dot (.). Choose your database and click on OK.

Screenshot for adding Entity Framework 4

How To Use Join Operations With Database Using LINQ

A connection will be added. You can change the name of your connection below. It will save connection in web config then click on Next.

Screenshot for adding Entity Framework 5

How To Use Join Operations With Database Using LINQ

After clicking on NEXT another window will appear; choose database table name as shown in the below screenshot then click on Finish.

Screenshot for adding Entity Framework 6

How To Use Join Operations With Database Using LINQ

Entity framework will be added and respective class gets generated under Models folder.

Screenshot for adding Entity Framework 7

How To Use Join Operations With Database Using LINQ

Screenshot for adding Entity Framework 8

How To Use Join Operations With Database Using LINQ

The following class will be added

Employee Class

  1. namespace JoinOperations_Demo
  2. {
  3. using System;
  4. using System.Collections.Generic;
  6. public partial class Employee
  7. {
  8. public int ID { get; set; }
  9. public string Name { get; set; }
  10. public string Position { get; set; }
  11. public string Office { get; set; }
  12. public Nullable<decimal> Salary { get; set; }
  13. public Nullable<int> DepartmentId { get; set; }
  14. }
  15. }
Department Class
  1. namespace JoinOperations_Demo
  2. {
  3. using System;
  4. using System.Collections.Generic;
  6. public partial class Department
  7. {
  8. public int ID { get; set; }
  9. public string Department_Name { get; set; }
  10. }
  11. }

Example of Group Join With Extension Method

  1. using System;
  2. using System.Linq;
  4. namespace JoinOperations_Demo
  5. {
  6. class GroupJoin
  7. {
  8. static void Main()
  9. {
  10. using (DBModel db=new DBModel())
  11. {
  12. var results = db.Departments.GroupJoin(db.Employees,
  13. d => d.ID,
  14. e => e.DepartmentId,
  15. (department, employee) => new
  16. {
  17. Department = department,
  18. Employee = employee
  19. });
  21. Console.WriteLine("----------GROUP JOIN----------");
  22. foreach (var department in results)
  23. {
  24. Console.WriteLine(" "+ department.Department.Department_Name);
  26. foreach (var employee in department.Employee)
  27. {
  28. Console.WriteLine(" \t"+ employee.Name);
  29. }
  30. }
  31. Console.ReadLine();
  32. }
  33. }
  34. }
  35. }
Output
How To Use Join Operations With Database Using LINQ

Example of Group Join with SQL like syntax

  1. using System;
  2. using System.Linq;
  4. namespace JoinOperations_Demo
  5. {
  6. class GroupJoin
  7. {
  8. static void Main()
  9. {
  10. using (DBModel db=new DBModel())
  11. {
  12. var results = from d in db.Departments
  13. join e in db.Employees on
  14. d.ID equals e.DepartmentId into eGroup
  15. select new
  16. {
  17. Department = d,
  18. Employee = eGroup
  19. };
  22. Console.WriteLine("----------GROUP JOIN----------");
  23. foreach (var department in results)
  24. {
  25. Console.WriteLine(" "+ department.Department.Department_Name);
  27. foreach (var employee in department.Employee)
  28. {
  29. Console.WriteLine(" \t"+ employee.Name);
  30. }
  31. }
  32. Console.ReadLine();
  33. }
  34. }
  35. }
  36. }
Output
How To Use Join Operations With Database Using LINQ

Example of Inner Join With Extension Method
  1. using System;
  2. using System.Linq;
  4. namespace JoinOperations_Demo
  5. {
  6. class InnerJoin
  7. {
  8. static void Main()
  9. {
  10. using (DBModel db=new DBModel())
  11. {
  12. var result = db.Employees.Join(db.Departments,
  13. e => e.DepartmentId,
  14. d => d.ID,
  15. (employee, department) => new
  16. {
  17. EmployeeName=employee.Name,
  18. DepartmentName=department.Department_Name
  19. });
  21. Console.WriteLine("----------INNER JOIN----------\n");
  22. foreach (var employee in result)
  23. {
  24. Console.WriteLine(" Name: " + employee.EmployeeName + "\t\t" + " Department: "+ employee.DepartmentName);
  25. }
  26. Console.ReadLine();
  27. }
  28. }
  29. }
  30. }
Output

How To Use Join Operations With Database Using LINQ
Example of Inner Join with SQL like syntax
  1. using System;
  2. using System.Linq;
  4. namespace JoinOperations_Demo
  5. {
  6. class InnerJoin
  7. {
  8. static void Main()
  9. {
  10. using (DBModel db=new DBModel())
  11. {
  12. var result = from e in db.Employees
  13. join d in db.Departments on e.DepartmentId equals d.ID
  14. select new
  15. {
  16. EmployeeName=e.Name,
  17. DepartmentName=d.Department_Name
  18. };
  20. Console.WriteLine("----------INNER JOIN----------\n");
  21. foreach (var employee in result)
  22. {
  23. Console.WriteLine(" Name: " + employee.EmployeeName + "\t\t" + " Department: "+ employee.DepartmentName);
  24. }
  25. Console.ReadLine();
  26. }
  27. }
  28. }
  29. }
Output

How To Use Join Operations With Database Using LINQ

Example of Left Outer Join With Extension Method
  1. using System;
  2. using System.Linq;
  4. namespace JoinOperations_Demo
  5. {
  6. class LeftOuterJoin
  7. {
  8. static void Main()
  9. {
  10. using (DBModel db=new DBModel())
  11. {
  12. var result = db.Employees.GroupJoin(db.Departments,
  13. e => e.DepartmentId, d => d.ID,(employee,department)=>new
  14. {
  15. EmployeeName=employee,
  16. DepartmentName =department
  17. })
  18. .SelectMany(x=>x.DepartmentName.DefaultIfEmpty(),
  19. (a,b)=>new
  20. {
  21. emp=a.EmployeeName.Name,
  22. dept=b==null? "No Department":b.Department_Name
  23. });
  25. Console.WriteLine("----------LEFT OUTER JOIN----------");
  26. foreach (var employee in result)
  27. {
  28. Console.WriteLine("Employee Name: " + employee.emp +"\t\t"+ "Department Name: " + employee.dept);
  29. }
  31. Console.ReadLine();
  32. }
  33. }
  34. }
  35. }
Output

How To Use Join Operations With Database Using LINQ

Example of Left Outer Join with SQL like syntax
  1. using System;
  2. using System.Linq;
  4. namespace JoinOperations_Demo
  5. {
  6. class LeftOuterJoin
  7. {
  8. static void Main()
  9. {
  10. using (DBModel db=new DBModel())
  11. {
  12. var result = from e in db.Employees
  13. join d in db.Departments
  14. on e.DepartmentId equals d.ID into eGroup
  15. from d in eGroup.DefaultIfEmpty()
  16. select new
  17. {
  18. EmployeeName=e.Name,
  19. DepartmentName=d==null? "No Department " :d.Department_Name //This line of code display if employee does not have department
  20. };
  22. Console.WriteLine("----------LEFT OUTER JOIN----------");
  23. foreach (var employee in result)
  24. {
  25. Console.WriteLine("Employee Name: " + employee.EmployeeName +"\t\t"+ "Department Name: " + employee.DepartmentName);
  26. }
  28. Console.ReadLine();
  29. }
  30. }
  31. }
  32. }
Output

How To Use Join Operations With Database Using LINQ

Example of Cross Join With Extension Method
  1. using System;
  2. using System.Linq;
  4. namespace JoinOperations_Demo
  5. {
  6. class CrossJoin
  7. {
  8. static void Main()
  9. {
  10. using (DBModel db=new DBModel())
  11. {
  12. var result = db.Employees.Join(db.Departments, e=>true,d=>true,(e,d)=>new { e,d });
  14. foreach (var employee in result)
  15. {
  16. Console.WriteLine(" Employee Name: " + employee.e.Name + "\t\t\t" + "Department Name:" + employee.d.Department_Name);
  17. }
  19. Console.ReadLine();
  20. }
  21. }
  22. }
  23. }
Output

How To Use Join Operations With Database Using LINQ

Example of Cross Join with SQL like syntax

  1. using System;
  2. using System.Linq;
  4. namespace JoinOperations_Demo
  5. {
  6. class CrossJoin
  7. {
  8. static void Main()
  9. {
  10. using (DBModel db=new DBModel())
  11. {
  12. var result=from e in db.Employees
  13. from d in db.Departments
  14. select new{ e, d };
  16. foreach (var employee in result)
  17. {
  18. Console.WriteLine(" Employee Name: " + employee.e.Name + "\t\t\t" + "Department Name:" + employee.d.Department_Name);
  19. }
  21. Console.ReadLine();
  22. }
  23. }
  24. }
  25. }
Output

How To Use Join Operations With Database Using LINQ