In this post, we will see an example of how to do a Left Outer Join in LINQ and C#.
In a previous post, we saw how to do an Inner join in C# and LINQ where each element of the first collection appears one time for every matching element in the second collection. If an element in the first collection has no matching elements, it does not appear in the join result set. However in a Left Outer Join, each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection.
Let us see this with an example.
In the code shown above, the query uses the join clause to match Book objects with Order objects testing it for equality using the equals operator. Up till here, the query is the same as in our previous article.
Additionally in order to include each element of the Book collection in the result set even if that element has no matches in the Order collection, we are using DefaultIfEmpty() and passing in an empty instance of the Order class, when there is no Order for that Book.
The select clause defines how the result will appear using anonymous types that consist of the BookID, Book Name and Order Payment Mode.
In a previous post, we saw how to do an Inner join in C# and LINQ where each element of the first collection appears one time for every matching element in the second collection. If an element in the first collection has no matching elements, it does not appear in the join result set. However in a Left Outer Join, each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection.
Let us see this with an example.
public
class
Book
{
public int BookID { get; set; }
public string BookNm { get; set; }
}
{
public int BookID { get; set; }
public string BookNm { get; set; }
}
public
class
Order{
public int OrderID { get; set; }
public int BookID { get; set; }
public string PaymentMode { get; set; }}
public int OrderID { get; set; }
public int BookID { get; set; }
public string PaymentMode { get; set; }}
class
Program
{
{
static
void
Main(string[]
args)
{
#region
bind Data
List<Book>
bookList = new
List<Book>
{
new
Book{BookID=1,
BookNm="DevCurry.com
Developer Tips"},
new
Book{BookID=2,
BookNm=".NET
and COM for Newbies"},
new
Book{BookID=3,
BookNm="51
jQuery ASP.NET Recipes"},
new
Book{BookID=4,
BookNm="Motivational
Gurus"},
new
Book{BookID=5,
BookNm="Spiritual
Gurus"}
};
List<Order>
bookOrders = new
List<Order>{
new
Order{OrderID=1,
BookID=1, PaymentMode="Cheque"},
new
Order{OrderID=2,
BookID=5, PaymentMode="Credit"},
new
Order{OrderID=3,
BookID=1, PaymentMode="Cash"},
new
Order{OrderID=4,
BookID=3, PaymentMode="Cheque"},
new
Order{OrderID=5,
BookID=5, PaymentMode="Cheque"},
new
Order{OrderID=6,
BookID=4, PaymentMode="Cash"}
};
#endregion
var
orderForBooks = from
bk in
bookList
join
ordr in
bookOrders
on
bk.BookID equals
ordr.BookID
into
a
from
b in
a.DefaultIfEmpty(new
Order())
select
new
{
bk.BookID,
Name
= bk.BookNm,
b.PaymentMode
};
foreach
(var
item in
orderForBooks)
Console.WriteLine(item);
Console.ReadLine();
}
}
In the code shown above, the query uses the join clause to match Book objects with Order objects testing it for equality using the equals operator. Up till here, the query is the same as in our previous article.
Additionally in order to include each element of the Book collection in the result set even if that element has no matches in the Order collection, we are using DefaultIfEmpty() and passing in an empty instance of the Order class, when there is no Order for that Book.
The select clause defines how the result will appear using anonymous types that consist of the BookID, Book Name and Order Payment Mode.
Observe that BookID =2 was included in the list even though it did not have an entry in the Order table.
