A LINQ JOIN keyword is used to combine rows from two or more tables, based on a common field between them. Like SQL Joins, the Linq is also provided some keywords to achieve Inner Join and Outer Join. As we know from SQL outer join is divided into 2 groups that is Left Outer Join and Right Outer Join. Here, in this tip, we learn how to achieve these joins in LINQ. Let us discuss what is inner join.
  • INNER JOIN: Inner Join only return matched records between two or more tables based on a common field between these tables.

  • LEFT OUTER JOIN: Return all rows from the left table, and the matched rows from the right table.

  • RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table.

  • FULL JOIN: Return all rows when there is a match in ONE of the tables.
Here in this console application, I have two lists (EmployeeList and DepartmentList). Considering these 2 lists as 2 different tables, I am joining these 2 tables on the basis of a common column, i.e., ID.
INNER JOIN
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Threading.Tasks;
  7. namespace Joins
  8. {
  9. public class Employee
  10. {
  11. public int Id { get; set; }
  12. public int age { get; set; }
  13. public string name { get; set; }
  14. public string gender { get; set; }
  15. }
  16. public class Department
  17. {
  18. public int id { get; set; }
  19. public string Departments { get; set; }
  20. public string Location { get; set; }
  21. }
  22. class Demo
  23. {
  24. static void Main(string[] args)
  25. {
  26. List<Employee> li = new List<Employee>();
  27. li.Add(new Employee
  28. { Id = 1, age = 19, name = "Ritesh", gender = "M" });
  29. li.Add(new Employee
  30. { Id = 2, age = 20, name = "sujit", gender = "M" });
  31. li.Add(new Employee
  32. { Id = 3, age = 23, name = "Kabir", gender = "F" });
  33. li.Add(new Employee
  34. { Id = 4, age = 3, name = "mantu", gender = "F" });
  35. li.Add(new Employee
  36. { Id = 5, age = 24, name = "Kamlesh", gender = "M" });
  37. li.Add(new Employee
  38. { Id = 6, age = 28, name = "Manoj", gender = "M" });
  41. List<Department> Deli = new List<Department>();
  42. Deli.Add(new Department
  43. { id = 2, Departments = "IT", Location = "Bangalore" });
  44. Deli.Add(new Department
  45. { id = 8, Departments = "IT", Location = "Bangalore" });
  46. Deli.Add(new Department
  47. { id = 3, Departments = "HR", Location = "Bangalore" });
  48. Deli.Add(new Department
  49. { id = 7, Departments = "HR", Location = "Bangalore" });
  50. Deli.Add(new Department
  51. { id = 6, Departments = "Account", Location = "Bangalore" });
  52. var result = from emp in li
  53. join
  54. de in Deli
  55. on emp.Id equals de.id
  56. select new
  57. {
  58. EmployeeId = emp.Id,
  59. EmployeeName = emp.name,
  60. Department = de.Departments,
  61. Location = de.Location
  62. };
  64. Console.WriteLine(" ID\t\tName\t\t DepartmentName \t\tLocation");
  66. foreach (var obj in result)
  67. {
  68. Console.WriteLine(obj.EmployeeId + "\t\t" + obj.EmployeeName +
  69. "\t\t" + obj.Department + "\t\t\t\t" + obj.Location);
  70. }
  72. Console.ReadLine();
  73. }
  74. }
  75. }
Now here is the result produced.
LEFT OUTER JOIN
Here is the program for Left Outer join.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Threading.Tasks;
  7. namespace Joins
  8. {
  9. public class Employee
  10. {
  11. public int Id { get; set; }
  12. public int age { get; set; }
  13. public string name { get; set; }
  14. public string gender { get; set; }
  15. }
  16. public class Department
  17. {
  18. public int id { get; set; }
  19. public string Departments { get; set; }
  20. public string Location { get; set; }
  21. }
  22. class Demo
  23. {
  24. static void Main(string[] args)
  25. {
  26. List<Employee> li = new List<Employee>();
  27. li.Add(new Employee { Id = 1, age = 19, name = "Ritesh", gender = "M" });
  28. li.Add(new Employee { Id = 2, age = 20, name = "sujit", gender = "M" });
  29. li.Add(new Employee { Id = 3, age = 23, name = "Kabir", gender = "F" });
  30. li.Add(new Employee { Id = 4, age = 3, name = "mantu", gender = "F" });
  31. li.Add(new Employee { Id = 5, age = 24, name = "Kamlesh", gender = "M" });
  32. li.Add(new Employee { Id = 6, age = 28, name = "Manoj", gender = "M" });
  34. List<Department> Deli = new List<Department>();
  35. Deli.Add(new Department { id = 2, Departments = "IT", Location = "Bangalore" });
  36. Deli.Add(new Department { id = 8, Departments = "IT", Location = "Ranchi" });
  37. Deli.Add(new Department { id = 3, Departments = "HR", Location = "bihar" });
  38. Deli.Add(new Department { id = 7, Departments = "HR", Location = "bhubaneshwar" });
  39. Deli.Add(new Department { id = 6, Departments = "Account", Location = "keonjhar" });
  41. var result = from emp in li
  42. join
  43. de in Deli
  44. on emp.Id equals de.id into tempstorage
  45. from dx in tempstorage.DefaultIfEmpty()
  46. select new
  47. {
  48. EmployeeId = emp.Id,
  49. EmployeeName = emp.name,
  51. Department = (dx != null) ? dx.Departments : "NULL",
  52. Location = (dx != null) ? dx.Location : "NULL"
  53. };
  55. Console.WriteLine("ID\t\tName\t\t DepartmentName \t\tLocation");
  57. foreach (var obj in result)
  58. {
  59. Console.WriteLine(obj.EmployeeId + "\t\t" +
  60. obj.EmployeeName + "\t\t" + obj.Department + "\t\t\t\t" + obj.Location);
  61. }
  63. Console.ReadLine();
  64. }
  65. }
  66. }
The output produced can be seen below:
RIGHT OUTER JOIN
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Threading.Tasks;
  7. namespace Joins
  8. {
  9. public class Employee
  10. {
  11. public int Id { get; set; }
  12. public int age { get; set; }
  13. public string name { get; set; }
  14. public string gender { get; set; }
  15. }
  16. public class Department
  17. {
  18. public int id { get; set; }
  19. public string Departments { get; set; }
  20. public string Location { get; set; }
  21. }
  22. class Demo
  23. {
  24. static void Main(string[] args)
  25. {
  26. List<Employee> li = new List<Employee>();
  27. li.Add(new Employee
  28. { Id = 1, age = 19, name = "Ritesh", gender = "M" });
  29. li.Add(new Employee
  30. { Id = 2, age = 20, name = "sujit", gender = "M" });
  31. li.Add(new Employee
  32. { Id = 3, age = 23, name = "Kabir", gender = "F" });
  33. li.Add(new Employee
  34. { Id = 4, age = 3, name = "mantu", gender = "F" });
  35. li.Add(new Employee
  36. { Id = 5, age = 24, name = "Kamlesh", gender = "M" });
  37. li.Add(new Employee
  38. { Id = 6, age = 28, name = "Manoj", gender = "M" });
  40. List<Department> Deli = new List<Department>();
  41. Deli.Add(new Department
  42. { id = 2, Departments = "IT", Location = "Bangalore" });
  43. Deli.Add(new Department
  44. { id = 8, Departments = "IT", Location = "Bangalore" });
  45. Deli.Add(new Department
  46. { id = 3, Departments = "HR", Location = "Bangalore" });
  47. Deli.Add(new Department
  48. { id = 7, Departments = "HR", Location = "Bangalore" });
  49. Deli.Add(new Department
  50. { id = 6, Departments = "Account", Location = "Bangalore" });
  52. var result = from
  53. de in Deli
  54. join
  55. emp in li
  57. on de.id equals emp.Id into tempstorage
  58. from dx in tempstorage.DefaultIfEmpty()
  59. select new
  60. {
  61. EmployeeId = (dx != null) ? dx.Id :0,
  62. EmployeeName = (dx != null) ? dx.name : "NULL",
  64. Department = de.Departments,
  65. Location = de.Location
  66. };
  68. Console.WriteLine("ID\t\tName\t\t DepartmentName \t\tLocation");
  70. foreach (var obj in result)
  71. {
  72. Console.WriteLine(obj.EmployeeId + "\t\t" + obj.EmployeeName +
  73. "\t\t" + obj.Department + "\t\t\t\t" + obj.Location);
  74. }
  76. Console.ReadLine();
  77. }
  78. }
  79. }
So in this, we can see 2 different tables in different ways in LINQ.