LINQ

Working With Right Outer Join Using Lambda And LINQ

Right Outer Join
 
The RIGHT OUTER JOIN returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side when there is no match.
 
SQL Syntax 
  1. SELECT column_name(s)  
  2. FROM table1  
  3. RIGHT JOIN table2 ON table1.column_name = table2.column_name;  
Okay! Now, let us see the examples in both, LINQ and lambda. For that, I have created two classes and added the dummy values to it as below.
  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. };  
  80. #region Developer and Skill Collection  
  81. Console.WriteLine("Developer Collection \n\t");  
  82. foreach(var developer in developers) {  
  83.     Console.WriteLine("ID : " + developer.Id + " , SkillID : " + developer.SkillID + " , Name : " + developer.Name);  
  84. }  
  85. Console.WriteLine("\n Skill Collection \n\t");  
  86. foreach(var skill in skills) {  
  87.     Console.WriteLine("ID : " + skill.Id + " , Name : " + skill.Name);  
  88. }  
  89. #endregion  
The collection values are shown below.

 

Now, let us see the example of Right Outer Join using lambda.
  1. #region Left Outer join using Lambda  
  2. //Defered Query Execution  
  3. var rightOuterJoin = (skills.GroupJoin(developers, left => left.Id, right => right.SkillID, (left, right) => new {  
  4.     TableA = right, TableB = left  
  5. }).SelectMany(p => p.TableA.DefaultIfEmpty(), (x, y) => new {  
  6.     TableA = y, TableB = x.TableB  
  7. }));  
  8. //Immediate Query Exectuion  
  9. foreach(var item in rightOuterJoin) {  
  10.     if (item.TableA != null && item.TableB != null) {  
  11.         Console.WriteLine("Skill : " + item.TableB.Name + " , Developer Name : " + item.TableA.Name);  
  12.     } else {  
  13.         Console.WriteLine("Skill : " + item.TableB.Name + " , Developer Name : " + string.Empty);  
  14.     }  
  15. }  
  16. #endregion  
Result
 
 
 
Now, let us see an example using LINQ.
 
You can use LINQ to perform a right outer join by calling the DefaultIfEmpty method on the results of a group join.
  1. #region Right Outer Join using Linq Query  
  2. //Defered Query Execution  
  3. var rightJoin = from skill in skills  
  4. join deve in developers  
  5. on skill.Id equals deve.SkillID into joinDeptEmp  
  6. from employee in joinDeptEmp.DefaultIfEmpty()  
  7. select new {  
  8.     EmployeeName = employee != null ? employee.Name : null,  
  9.         SkillName = skill.Name  
  10. };  
  11. //Immediate Query Exectuion  
  12. foreach(var item in rightJoin) {  
  13.     Console.WriteLine("Skill : " + item.SkillName + " , Developer Name : " + item.EmployeeName);  
  14. }  
  15. #endregion  
Result

 
 
I hope you got a clear picture of how to use right outer join using LINQ and lambda.
 
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 RightOuterJoinWithLinqAndLambda {  
  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 rightOuterJoin = (skills.GroupJoin(developers, left => left.Id, right => right.SkillID, (left, right) => new {  
  78.                 TableA = right, TableB = left  
  79.             }).SelectMany(p => p.TableA.DefaultIfEmpty(), (x, y) => new {  
  80.                 TableA = y, TableB = x.TableB  
  81.             }));  
  82.             //Immediate Query Exectuion  
  83.             foreach(var item in rightOuterJoin) {  
  84.                 if (item.TableA != null && item.TableB != null) {  
  85.                     Console.WriteLine("Skill : " + item.TableB.Name + " , Developer Name : " + item.TableA.Name);  
  86.                 } else {  
  87.                     Console.WriteLine("Skill : " + item.TableB.Name + " , Developer Name : " + string.Empty);  
  88.                 }  
  89.             }#  
  90.             endregion# region Right Outer Join using Linq Query  
  91.             //Defered Query Execution  
  92.             var rightJoin = from skill in skills  
  93.             join deve in developers  
  94.             on skill.Id equals deve.SkillID into joinDeptEmp  
  95.             from employee in joinDeptEmp.DefaultIfEmpty()  
  96.             select new {  
  97.                 EmployeeName = employee != null ? employee.Name : null,  
  98.                     SkillName = skill.Name  
  99.             };  
  100.             //Immediate Query Exectuion  
  101.             foreach(var item in rightJoin) {  
  102.                 Console.WriteLine("Skill : " + item.SkillName + " , Developer Name : " + item.EmployeeName);  
  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  tothe 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