Contribute
LINQ

Working With Left Outer Join Using Lambda And LINQ

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.
Ebook Download
View all
Learn
View all
Get Started

You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

Become a sharper developer and jumpstart your career.

Basic

$0

$

. 00

monthly

For Basic members:

  • Standard Profile
  • Access learning courses
  • Access free e-books
  • 1x point
Premium

$20

$

. 00

monthly

For Premium members:

  • Earn 2x reward points
  • Premium profile with Verified check
  • Premium Avatar with status update
  • Learning courses
  • Unlimited Certifications and badges on profile
  • Unlimited Book downloads
  • Access to Premium content
  • Online Training
  • Unlimited PDF conversions and downloads
  • Professional Resume Writing
  • Interview Preparations Guide
  • 25 messages per month
  • 1 "Kodzu" avatar, basic
Elite

$45

$

. 00

monthly

For Elite members:

  • Bundle E-books
  • Access to all Learn Series
  • Unlimited Apply Jobs