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
- SELECT column_name(s)
- FROM table1
- 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.
- class Skill {
- public int Id {
- get;
- set;
- }
- public string Name {
- get;
- set;
- }
- }
- class Developer {
- public int Id {
- get;
- set;
- }
- public int SkillID {
- get;
- set;
- }
- public string Name {
- get;
- set;
- }
- }
- Developer[] developers = new Developer[] {
- new Developer {
- Id = 1, SkillID = 6, Name = "Gnanavel Sekar"
- },
- new Developer {
- Id = 2, SkillID = 2, Name = "Subash S"
- },
- new Developer {
- Id = 3, SkillID = 1, Name = "Ammaiyappan I"
- },
- new Developer {
- Id = 4, SkillID = 12, Name = "Robert B"
- },
- new Developer {
- Id = 3, SkillID = 10, Name = "Ramar A"
- },
- };
- Skill[] skills = new Skill[] {
- new Skill {
- Id = 1, Name = "ASP.NET"
- },
- new Skill {
- Id = 2, Name = "C#"
- },
- new Skill {
- Id = 3, Name = "LINQ"
- },
- new Skill {
- Id = 4, Name = "ORCHARD CMS"
- },
- new Skill {
- Id = 5, Name = "Entity Framework"
- },
- new Skill {
- Id = 6, Name = "ASP.NET MVC"
- },
- new Skill {
- Id = 7, Name = "ASP.NET WEB API"
- },
- new Skill {
- Id = 8, Name = "JQUERY"
- },
- new Skill {
- Id = 9, Name = "DOCUSIGN"
- },
- new Skill {
- Id = 10, Name = "KENDO UI"
- },
- new Skill {
- Id = 11, Name = "ASP.NET AJAX"
- },
- new Skill {
- Id = 12, Name = "HTML5"
- },
- };
The collections values are shown below.
Now, let us see the Left Outer Join example using Lambda.
- #region Left Outer join using Lambda
-
- var result = developers.GroupJoin(skills, devloper => devloper.SkillID, skill => skill.Id, (devloper, skill) => new {
- Key = devloper, Skills = skill
- });
- Console.WriteLine("Left Outer join using Lambda \n\t");
-
- foreach(var developer in result) {
- Console.WriteLine("\n Developer Name : " + developer.Key.Name);
- foreach(var person in developer.Skills) {
- Console.WriteLine("Working Skill : " + person.Name);
- }
- }
- #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.
- #region Left Outer Join using Linq
- Console.WriteLine("\n\t Left Outer join using Linq \n\t");
-
- var query = from developer in developers
- join skill in skills on developer.SkillID equals skill.Id
- into developer_skill_joined
- from dev_skill in developer_skill_joined.DefaultIfEmpty()
- from skill in skills.Where(var_skill => dev_skill == null ? false : var_skill.Id == dev_skill.Id).DefaultIfEmpty()
- select new {
- developer = developer.Name, Skill = skill == null ? String.Empty : skill.Name
- };
-
- foreach(var item in query) {
- Console.WriteLine("Developer Name : " + item.developer + " , Skill " + item.Skill);
- }
- #endregion
Result
Complete Code
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
- namespace LeftOuterJoinINLinqAndLambda {
- class Program {
- static void Main(string[] args) {
- Developer[] developers = new Developer[] {
- new Developer {
- Id = 1, SkillID = 6, Name = "Gnanavel Sekar"
- },
- new Developer {
- Id = 2, SkillID = 2, Name = "Subash S"
- },
- new Developer {
- Id = 3, SkillID = 1, Name = "Ammaiyappan I"
- },
- new Developer {
- Id = 4, SkillID = 12, Name = "Robert B"
- },
- new Developer {
- Id = 3, SkillID = 10, Name = "Ramar A"
- },
- };
- Skill[] skills = new Skill[] {
- new Skill {
- Id = 1, Name = "ASP.NET"
- },
- new Skill {
- Id = 2, Name = "C#"
- },
- new Skill {
- Id = 3, Name = "LINQ"
- },
- new Skill {
- Id = 4, Name = "ORCHARD CMS"
- },
- new Skill {
- Id = 5, Name = "Entity Framework"
- },
- new Skill {
- Id = 6, Name = "ASP.NET MVC"
- },
- new Skill {
- Id = 7, Name = "ASP.NET WEB API"
- },
- new Skill {
- Id = 8, Name = "JQUERY"
- },
- new Skill {
- Id = 9, Name = "DOCUSIGN"
- },
- new Skill {
- Id = 10, Name = "KENDO UI"
- },
- new Skill {
- Id = 11, Name = "ASP.NET AJAX"
- },
- new Skill {
- Id = 12, Name = "HTML5"
- },
- };
- #region Developer and Skill Collection
- Console.WriteLine("Developer Collection \n\t");
- foreach(var developer in developers) {
- Console.WriteLine("ID : " + developer.Id + " , SkillID : " + developer.SkillID + " , Name : " + developer.Name);
- }
- Console.WriteLine("\n Skill Collection \n\t");
- foreach(var skill in skills) {
- Console.WriteLine("ID : " + skill.Id + " , Name : " + skill.Name);
- }
- #endregion
- Console.WriteLine("\n Group-joined list of developer working in following skills \n\t");
- #region Left Outer join using Lambda
-
- var result = developers.GroupJoin(skills, devloper => devloper.SkillID, skill => skill.Id, (devloper, skill) => new {
- Key = devloper, Skills = skill
- });
- Console.WriteLine("Left Outer join using Lambda \n\t");
-
- foreach(var developer in result) {
- Console.WriteLine("\n Developer Name : " + developer.Key.Name);
- foreach(var person in developer.Skills) {
- Console.WriteLine("Working Skill : " + person.Name);
- }
- }
- #endregion
- # region Left Outer Join using Linq
- Console.WriteLine("\n\t Left Outer join using Linq \n\t");
-
- var query = from developer in developers
- join skill in skills on developer.SkillID equals skill.Id
- into developer_skill_joined
- from dev_skill in developer_skill_joined.DefaultIfEmpty()
- from skill in skills.Where(var_skill => dev_skill == null ? false : var_skill.Id == dev_skill.Id).DefaultIfEmpty()
- select new {
- developer = developer.Name, Skill = skill == null ? String.Empty : skill.Name
- };
-
- foreach(var item in query) {
- Console.WriteLine("Developer Name : " + item.developer + " , Skill " + item.Skill);
- }
- #endregion
- }
- }
- class Skill {
- public int Id {
- get;
- set;
- }
- public string Name {
- get;
- set;
- }
- }
- class Developer {
- public int Id {
- get;
- set;
- }
- public int SkillID {
- get;
- set;
- }
- public string Name {
- get;
- set;
- }
- }
- }
Refer to the attached demo project. I hope it's helpful.