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

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
//Defered Query Execution
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");
//Immediate Query Exectuion
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");
//defered Query Execution
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
};
//Immediate Query Execution
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
            //Defered Query Execution
            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");
            //Immediate Query Exectuion
            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");
            //defered Query Execution
            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
            };
            //Immediate Query Execution
            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.