Left Outer Join
A LEFT OUTER JOIN is one of the JOIN operations that allows you to specify a join clause. A LEFT OUTER JOIN is one of the JOIN operations that allows you to specify a join clause.The LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.
SQL syntax
  1. SELECT column_name(s)
  2. FROM table1
  3. LEFT JOIN table2 ON table1.column_name = table2.column_name;
Okay! Now, let us see the example in both LINQ and lambda. For that, I have created two classes and added dummy values to those.
  1. class Skill {
  2. public int Id {
  3. get;
  4. set;
  5. }
  6. public string Name {
  7. get;
  8. set;
  9. }
  10. }
  11. class Developer {
  12. public int Id {
  13. get;
  14. set;
  15. }
  16. public int SkillID {
  17. get;
  18. set;
  19. }
  20. public string Name {
  21. get;
  22. set;
  23. }
  24. }
  25. Developer[] developers = new Developer[] {
  26. new Developer {
  27. Id = 1, SkillID = 6, Name = "Gnanavel Sekar"
  28. },
  29. new Developer {
  30. Id = 2, SkillID = 2, Name = "Subash S"
  31. },
  32. new Developer {
  33. Id = 3, SkillID = 1, Name = "Ammaiyappan I"
  34. },
  35. new Developer {
  36. Id = 4, SkillID = 12, Name = "Robert B"
  37. },
  38. new Developer {
  39. Id = 3, SkillID = 10, Name = "Ramar A"
  40. },
  41. };
  42. Skill[] skills = new Skill[] {
  43. new Skill {
  44. Id = 1, Name = "ASP.NET"
  45. },
  46. new Skill {
  47. Id = 2, Name = "C#"
  48. },
  49. new Skill {
  50. Id = 3, Name = "LINQ"
  51. },
  52. new Skill {
  53. Id = 4, Name = "ORCHARD CMS"
  54. },
  55. new Skill {
  56. Id = 5, Name = "Entity Framework"
  57. },
  58. new Skill {
  59. Id = 6, Name = "ASP.NET MVC"
  60. },
  61. new Skill {
  62. Id = 7, Name = "ASP.NET WEB API"
  63. },
  64. new Skill {
  65. Id = 8, Name = "JQUERY"
  66. },
  67. new Skill {
  68. Id = 9, Name = "DOCUSIGN"
  69. },
  70. new Skill {
  71. Id = 10, Name = "KENDO UI"
  72. },
  73. new Skill {
  74. Id = 11, Name = "ASP.NET AJAX"
  75. },
  76. new Skill {
  77. Id = 12, Name = "HTML5"
  78. },
  79. };
The collections values are shown below.
Now, let us see the Left Outer Join example using Lambda.
  1. #region Left Outer join using Lambda
  2. //Defered Query Execution
  3. var result = developers.GroupJoin(skills, devloper => devloper.SkillID, skill => skill.Id, (devloper, skill) => new {
  4. Key = devloper, Skills = skill
  5. });
  6. Console.WriteLine("Left Outer join using Lambda \n\t");
  7. //Immediate Query Exectuion
  8. foreach(var developer in result) {
  9. Console.WriteLine("\n Developer Name : " + developer.Key.Name);
  10. foreach(var person in developer.Skills) {
  11. Console.WriteLine("Working Skill : " + person.Name);
  12. }
  13. }
  14. #endregion
Result



Now, let us see the example using LINQ.
You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.
  1. #region Left Outer Join using Linq
  2. Console.WriteLine("\n\t Left Outer join using Linq \n\t");
  3. //defered Query Execution
  4. var query = from developer in developers
  5. join skill in skills on developer.SkillID equals skill.Id
  6. into developer_skill_joined
  7. from dev_skill in developer_skill_joined.DefaultIfEmpty()
  8. from skill in skills.Where(var_skill => dev_skill == null ? false : var_skill.Id == dev_skill.Id).DefaultIfEmpty()
  9. select new {
  10. developer = developer.Name, Skill = skill == null ? String.Empty : skill.Name
  11. };
  12. //Immediate Query Execution
  13. foreach(var item in query) {
  14. Console.WriteLine("Developer Name : " + item.developer + " , Skill " + item.Skill);
  15. }
  16. #endregion
Result



Complete Code
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Threading.Tasks;
  6. namespace LeftOuterJoinINLinqAndLambda {
  7. class Program {
  8. static void Main(string[] args) {
  9. Developer[] developers = new Developer[] {
  10. new Developer {
  11. Id = 1, SkillID = 6, Name = "Gnanavel Sekar"
  12. },
  13. new Developer {
  14. Id = 2, SkillID = 2, Name = "Subash S"
  15. },
  16. new Developer {
  17. Id = 3, SkillID = 1, Name = "Ammaiyappan I"
  18. },
  19. new Developer {
  20. Id = 4, SkillID = 12, Name = "Robert B"
  21. },
  22. new Developer {
  23. Id = 3, SkillID = 10, Name = "Ramar A"
  24. },
  25. };
  26. Skill[] skills = new Skill[] {
  27. new Skill {
  28. Id = 1, Name = "ASP.NET"
  29. },
  30. new Skill {
  31. Id = 2, Name = "C#"
  32. },
  33. new Skill {
  34. Id = 3, Name = "LINQ"
  35. },
  36. new Skill {
  37. Id = 4, Name = "ORCHARD CMS"
  38. },
  39. new Skill {
  40. Id = 5, Name = "Entity Framework"
  41. },
  42. new Skill {
  43. Id = 6, Name = "ASP.NET MVC"
  44. },
  45. new Skill {
  46. Id = 7, Name = "ASP.NET WEB API"
  47. },
  48. new Skill {
  49. Id = 8, Name = "JQUERY"
  50. },
  51. new Skill {
  52. Id = 9, Name = "DOCUSIGN"
  53. },
  54. new Skill {
  55. Id = 10, Name = "KENDO UI"
  56. },
  57. new Skill {
  58. Id = 11, Name = "ASP.NET AJAX"
  59. },
  60. new Skill {
  61. Id = 12, Name = "HTML5"
  62. },
  63. };
  64. #region Developer and Skill Collection
  65. Console.WriteLine("Developer Collection \n\t");
  66. foreach(var developer in developers) {
  67. Console.WriteLine("ID : " + developer.Id + " , SkillID : " + developer.SkillID + " , Name : " + developer.Name);
  68. }
  69. Console.WriteLine("\n Skill Collection \n\t");
  70. foreach(var skill in skills) {
  71. Console.WriteLine("ID : " + skill.Id + " , Name : " + skill.Name);
  72. }
  73. #endregion
  74. Console.WriteLine("\n Group-joined list of developer working in following skills \n\t");
  75. #region Left Outer join using Lambda
  76. //Defered Query Execution
  77. var result = developers.GroupJoin(skills, devloper => devloper.SkillID, skill => skill.Id, (devloper, skill) => new {
  78. Key = devloper, Skills = skill
  79. });
  80. Console.WriteLine("Left Outer join using Lambda \n\t");
  81. //Immediate Query Exectuion
  82. foreach(var developer in result) {
  83. Console.WriteLine("\n Developer Name : " + developer.Key.Name);
  84. foreach(var person in developer.Skills) {
  85. Console.WriteLine("Working Skill : " + person.Name);
  86. }
  87. }
  88. #endregion
  89. # region Left Outer Join using Linq
  90. Console.WriteLine("\n\t Left Outer join using Linq \n\t");
  91. //defered Query Execution
  92. var query = from developer in developers
  93. join skill in skills on developer.SkillID equals skill.Id
  94. into developer_skill_joined
  95. from dev_skill in developer_skill_joined.DefaultIfEmpty()
  96. from skill in skills.Where(var_skill => dev_skill == null ? false : var_skill.Id == dev_skill.Id).DefaultIfEmpty()
  97. select new {
  98. developer = developer.Name, Skill = skill == null ? String.Empty : skill.Name
  99. };
  100. //Immediate Query Execution
  101. foreach(var item in query) {
  102. Console.WriteLine("Developer Name : " + item.developer + " , Skill " + item.Skill);
  103. }
  104. #endregion
  105. }
  106. }
  107. class Skill {
  108. public int Id {
  109. get;
  110. set;
  111. }
  112. public string Name {
  113. get;
  114. set;
  115. }
  116. }
  117. class Developer {
  118. public int Id {
  119. get;
  120. set;
  121. }
  122. public int SkillID {
  123. get;
  124. set;
  125. }
  126. public string Name {
  127. get;
  128. set;
  129. }
  130. }
  131. }
Refer to the attached demo project. I hope it's helpful.