Left Outer Join Using LINQ

I was in a kind of puzzled while I started searching how to do left outer join with LINQ. Finally, I found the solution and proper explanation of left outer join using LINQ. I am going to explain that here to help those who is still searching and also for my future reference :-)

We know “Join” combines two or more tables in a database. And by definition, left outer join returns all the values from the left table; doesn’t matter whether they match or not with the right table (returns NULL, incase don’t match).

We need to use LINQ’s “DefaultIfEmpty()” function to accomplish the task which returns the default value if the sequence is empty. It also takes default item as the parameter. However- let’s see how can we do a left outer join between Customer and Order table using LINQ:

list = from c in customers
                   join o in orders on c.ID equals o.CID into t_c

                   from r in t_c.DefaultIfEmpty()
                   select new { Name=c.Name, Item=(r!=null)?r.Item:"nothing" };

We have two steps in the above query:

1. Produce a collection by doing inner join between orders and customers table.

2. Use “DefaultIfEmpty” to invoke each element of first collection.

For your reference, below is the Customer and Order classes:

   class Customer {
        public int ID { get; set; }
        public string Name { get; set; }
    }

    class Order {
        public int ID { get; set; }
        public int CID { get; set; }
        public string Item { get; set; }
    }

Please check in MSDN for more details explanation-

http://msdn.microsoft.com/en-us/library/bb397895.aspx

Happy Programming!