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 

SELECT column_name(s)
FROM table1
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.

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"
    },
};
#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

The collection values are shown below.

 

Now, let us see the example of Right Outer Join using lambda.

#region Left Outer join using Lambda
//Defered Query Execution
var rightOuterJoin = (skills.GroupJoin(developers, left => left.Id, right => right.SkillID, (left, right) => new {
    TableA = right, TableB = left
}).SelectMany(p => p.TableA.DefaultIfEmpty(), (x, y) => new {
    TableA = y, TableB = x.TableB
}));
//Immediate Query Exectuion
foreach(var item in rightOuterJoin) {
    if (item.TableA != null && item.TableB != null) {
        Console.WriteLine("Skill : " + item.TableB.Name + " , Developer Name : " + item.TableA.Name);
    } else {
        Console.WriteLine("Skill : " + item.TableB.Name + " , Developer Name : " + string.Empty);
    }
}
#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.

#region Right Outer Join using Linq Query
//Defered Query Execution
var rightJoin = from skill in skills
join deve in developers
on skill.Id equals deve.SkillID into joinDeptEmp
from employee in joinDeptEmp.DefaultIfEmpty()
select new {
    EmployeeName = employee != null ? employee.Name : null,
        SkillName = skill.Name
};
//Immediate Query Exectuion
foreach(var item in rightJoin) {
    Console.WriteLine("Skill : " + item.SkillName + " , Developer Name : " + item.EmployeeName);
}
#endregion

Result

 

 

I hope you got a clear picture of how to use right outer join using LINQ and lambda.

 

Complete Code 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace RightOuterJoinWithLinqAndLambda {
    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 rightOuterJoin = (skills.GroupJoin(developers, left => left.Id, right => right.SkillID, (left, right) => new {
                TableA = right, TableB = left
            }).SelectMany(p => p.TableA.DefaultIfEmpty(), (x, y) => new {
                TableA = y, TableB = x.TableB
            }));
            //Immediate Query Exectuion
            foreach(var item in rightOuterJoin) {
                if (item.TableA != null && item.TableB != null) {
                    Console.WriteLine("Skill : " + item.TableB.Name + " , Developer Name : " + item.TableA.Name);
                } else {
                    Console.WriteLine("Skill : " + item.TableB.Name + " , Developer Name : " + string.Empty);
                }
            }#
            endregion# region Right Outer Join using Linq Query
            //Defered Query Execution
            var rightJoin = from skill in skills
            join deve in developers
            on skill.Id equals deve.SkillID into joinDeptEmp
            from employee in joinDeptEmp.DefaultIfEmpty()
            select new {
                EmployeeName = employee != null ? employee.Name : null,
                    SkillName = skill.Name
            };
            //Immediate Query Exectuion
            foreach(var item in rightJoin) {
                Console.WriteLine("Skill : " + item.SkillName + " , Developer Name : " + item.EmployeeName);
            }#
            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  tothe attached demo project. I hope it's helpful.