LINQ join Clause -Left Outer Join

Using LINQ’s join clause, you can also perform  a left outer join. Like an inner join, a left outer join also returns a flat  result. An inner join omits any item that has no corresponding items from  another data source. As an example, if an author wrote no book, then he will be  omitted in the result of the query. The left outer join includes even the items that have no corresponding partner in the result. This is made possible using the DefaultIfEmpty method.

Figure 1 – Left Outer Join

Let’s take a look at an example of doing a left outer join.

Author[] authors = new Author[]                                                        
{                                                                                      
    new Author() { AuthorId = 1, Name = "John Smith" },                                
    new Author() { AuthorId = 2, Name = "Harry Gold" },                                
    new Author() { AuthorId = 3, Name = "Ronald Schwimmer" },                          
    new Author() { AuthorId = 4, Name = "Jerry Mawler" }                               
};                                                                                    
                                                                                       
Book[] books = new Book[]                                                              
{                                                                                      
    new Book() { AuthorId = 1, Title = "Little Blue Riding Hood" },                    
    new Book() { AuthorId = 3, Title = "The Three Little Piggy Banks" },               
    new Book() { AuthorId = 1, Title = "Snow Black" },                                 
    new Book() { AuthorId = 2, Title = "My Rubber Duckie" },                           
    new Book() { AuthorId = 2, Title = "He Who Doesn't Know His Name" },               
    new Book() { AuthorId = 1, Title = "Hanzel and Brittle" }                          
};                                                                                     
                                                                                       
var result = from a in authors                                                          
             join b in books on a.AuthorId equals b.AuthorId into booksByAuthors        
             from x in booksByAuthors.DefaultIfEmpty(new Book {AuthorId=0,Title="None"})
             select new { Author = a.Name, x.Title };                                   
                                                                                       
Console.WriteLine("{0, -20} {1}", "Author", "Book");                                   
foreach (var r in result)                                                              
{                                                                                      
    Console.WriteLine("{0, -20} {1}", r.Author, r.Title);                              
}

Example 1

Author Book
John Smith Little Blue Riding Hood
John Smith Snow Black
John Smith Hanzel and Brittle
Harry Gold My Rubber Duckie
Harry Gold He Who Doesn't Know His Name
Ronald Schwimmer The Three Little Piggy Banks
Jerry Mawler None

To do a left outer join using the join clause, you first need to group join  the two data sources. You then perform another query by using the created groups as the  data source. You need to call the DefaultIfEmpty  for each group so whenever a group contains no items, a specified default value  will be provided.

As you can see in the query expression in lines 19 to 22, the first two lines  of the query expression performs a group join by querying every author object  and joining every book object which has an equal AuthorId  property as the queried author’s AuthorId  property. The next line performs another query by using the grouped result of  the first query as the data source. Notice that we call the DefaultIfEmpty method of the group to yield a  default value if the group is empty. The DefaultIfEmpty  method accepts one argument, which is an instance of an object which has a  similar type as every item of the group. Since each group in our query contains Book items, we created a new instance of the Book class and specified some default values for  its properties using objection initialization syntax.

Based on our data sources, Jerry Mawler (AuthorId  4) has no corresponding book from the books data source. If we simply used  an inner join, Jerry Mawler will be gone in the results, but since we used left  outer join, Jerry Mawler was included and as you can see in the output of Figure  2, the default value you specified was shown as his book.

There is no direct equivalent of a left outer join when you want to use the  method syntax. Doing a left outer join using the method syntax requires the combination of  the GroupBy method and the SelectMany method.

var result = authors.GroupJoin(books,
                               author => author.AuthorId,
                               book => book.AuthorId,
                               (author, booksByAuthor) => 
                               new { Author = author, Books = booksByAuthor })
                    .SelectMany(x=>x.Books.DefaultIfEmpty(new Book{AuthorId = 0,Title = "None"}),
                               (x, y) => new { Author = x.Author.Name, y.Title });

The GroupJoin method simply groups each book by author and projects a new type with an Author  property assigned with the author and Books  property assigned with the group of books he wrote. We then nested a call to the SelectMany  method. The SelectManymethod here accepts two  parameters. The first is the collection selector which selects an item from the result yielded by the GroupJoinmethod. Notice that we call the DefaultIfEmpty method so if the Books property of  an item is empty, then a default set of values specified will be used. The  second parameter is the result selector and through here, you can project the  final result of the query.