Left & Right Outer Join Example in LINQ and C#

Left & Right Outer Join Example in LINQ and C#

An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained (left, right, or both).

(In this case left and right refer to the two sides of the JOIN keyword.)

 

Suppose we have two  classes person and Address

public class Person

{

    public int EmpId { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

}

public class Address

{

    public int AddressId { get; set; }

    public int EmpId { get; set; }

    public string CityName { get; set; }

    public string StateName { get; set; }

}

//insert the data person and address classsed

         List<Person> people = new List<Person> {

            new Person{ EmpId = 1, FirstName = "Abc", LastName = "xyz0" },

            new Person{ EmpId = 2, FirstName = "Abc01", LastName = "xyz1" },

            new Person{ EmpId = 3, FirstName = "Abc02", LastName = "xyz2" },

            new Person{ EmpId = 4,FirstName="Abc03", LastName="xyz3" },

            new Person{ EmpId = 5,FirstName="Abc04", LastName="xyz4" },

            new Person{ EmpId = 6,FirstName="Abc05", LastName="xyz5" } };

        //  List pets = new List { barley, boots, whiskers, bluemoon, daisy };

        List<Address> address = new List<Address> {

            new Address{ AddressId = 1,EmpId=1, CityName = "Delhi", StateName = "Delhi" },

            new Address{ AddressId = 2,EmpId=1 , CityName= "Chandigarh", StateName ="Punjab" },

            new Address{ AddressId = 3, EmpId=1, CityName= "Shimla", StateName = "himachal"},

            new Address{ AddressId = 4,EmpId=2 ,CityName ="kanpur", StateName="Up" },

            new Address{ AddressId = 5,EmpId=4,CityName="other1", StateName="xyz4" },

            new Address{ AddressId = 6, EmpId=2, CityName="others", StateName="xyz5" } };

 

//Left Outer Join

  var query3 = from p in people

                    join r in address

                    on p.EmpId equals r.EmpId into temp

                    from t in temp.DefaultIfEmpty()

                    select new

                    {

                        FirstName = p.FirstName,

                        LastName = p.LastName,

                        city = t.CityName,

                        State = t.StateName,

                    };

//Right Outer Join

        var query4 = from r in address

                     join P in people

                     on r.EmpId equals P.EmpId into temp

                     from t in temp.DefaultIfEmpty()

                     select new

                     {

                         FirstName = t.FirstName,

                         LastName = t.LastName,

                         city = r.CityName,

                         State = r.StateName,

                     };