Post
Ask Question

How To Use Join Operations With Database Using LINQ

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]  
  13.   
  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]  
  9.   
  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;  
  5.       
  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;  
  5.       
  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;  
  3.   
  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.                     });  
  20.   
  21.                 Console.WriteLine("----------GROUP JOIN----------");  
  22.                 foreach (var department in results)  
  23.                 {  
  24.                     Console.WriteLine(" "+ department.Department.Department_Name);  
  25.   
  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;  
  3.   
  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.                               };  
  20.   
  21.   
  22.                 Console.WriteLine("----------GROUP JOIN----------");  
  23.                 foreach (var department in results)  
  24.                 {  
  25.                     Console.WriteLine(" "+ department.Department.Department_Name);  
  26.   
  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;  
  3.   
  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.                                              });  
  20.   
  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;  
  3.   
  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.                              };  
  19.   
  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;  
  3.   
  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.                     });  
  24.   
  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.                 }  
  30.   
  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;  
  3.   
  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.                              };  
  21.   
  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.                 }  
  27.   
  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;  
  3.   
  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 });  
  13.   
  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.                 }  
  18.   
  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;  
  3.   
  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 };  
  15.   
  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.                 }  
  20.   
  21.                 Console.ReadLine();  
  22.             }  
  23.         }  
  24.     }  
  25. }  
Output

How To Use Join Operations With Database Using LINQ


Similar Articles