Performing Inner Join and Left Outer Join in LINQ Queries

Linq supports joining just like any other querying language. Syntax is bit different from standard SQL query though. Also We need to add some logic to achieve Left Outer Join as there is no standard keyword for this.

In our example, we have list of people with their name and their address. We also have list of houses in city with their house name and owner name. 

a. Lets understand performing inner join. Inner join joins two tables based on some condition. Results will include only those entries from joined tables which match the condition. To illustrate inner join we need to find out all the people who own a house with their address. We use "join" keyword with "on" clause to achieve this inner join.  Example I have provided is self explanatory.

b. If we have to output all the people along with their house names if they own otherwise print empty string, then we can go for left outer join. In this join lets make People as left item. We use  DefaultIfEmpty() method to ensure for every People entry will appear in output result. DefaultIfEmpty() provides empty\default for each non matching sequence i.e for any People object if matching sequence in Houses is empty then DefaultIfEmpty returns single default item. In next select we can check if result is default or contains some value. If result sequence contains some value other than default, then we can output that value otherwise we can output empty string or default value.

Below example shows how Left Outer Join and Inner Joins are performed in our example:

        public static void Main(string[] args)

        {

            People p1 = new People { Name = "Manu", Address = "Marathalli" };

            People p2 = new People { Name = "Dada", Address = "BTM" };

            People p3 = new People { Name = "Saavu", Address = "Kunadanahalli" };

            People p4 = new People { Name = "Vasanth", Address = "Munnekolala" };

            People p5 = new People { Name = "Andy", Address = "Jayanagar" };

            People p6 = new People { Name = "Putta", Address = "JP Nagar" };

            Houses h1 = new Houses { HouseName = "Jaipur Palace", HouseOwnerName = "Putta" };

            Houses h2 = new Houses { HouseName = "Mysore Palace", HouseOwnerName = "Manu" };

            Houses h3 = new Houses { HouseName = "Pune Vihar", HouseOwnerName = "Vasanth" };

            List<People> people= new List<People> { p1, p2, p3, p4, p5, p6 };

            List<Houses> houses = new List<Houses> { h1, h2, h3 };

            Console.WriteLine("Inner Join Results:");

           //Inner join to print all the Person with house with house name

            var houseOwners = from p in people

                              join h in houses on p.Name equals h.HouseOwnerName

                              select new { p.Name, PersonHouseName = h.HouseName, p.Address };

            foreach (var h in houseOwners)

            {

                Console.WriteLine(h.Name+" : "+h.PersonHouseName+" : "+h.Address);

            }

            Console.WriteLine("************************************************************************");

           //Left outer join to print all the people and also their house names if they have one otherwise empty string

            Console.WriteLine("Left Outer Join Results:");

            var allppl = from p in people

                   join h in houses on p.Name equals h.HouseOwnerName into tempOwners

                   from nonOwners in tempOwners.DefaultIfEmpty()

                   select new { p.Name, PersonHouseName = (nonOwners==null)? string.Empty:nonOwners.HouseName, p.Address };

            foreach (var h in allppl)

            {

                Console.WriteLine(h.Name + " : " + h.PersonHouseName + " : " + h.Address);

            }

        }

    public class People

    {

        public string Name {set;get;}

        public string Address { set; get; }

    }

    public class Houses

    {

        public string HouseName { set; get; }

        public string HouseOwnerName { set; get; }

    }

Console Output Window: