LINQ – Left Join Example in C#




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.

class Program
{
static void Main(string[] args)
{
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"}
};
}
}

public class Book
{
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; }
}
}

Let us do a Left Outer Join between the Book and Order collection

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.

OUTPUT

image

Observe that BookID =2 was included in the list even though it did not have an entry in the Order table. You can compare this result with the one we got in our previous article to understand the difference between Inner Join and Left Outer Join.

Make sure you read my previous article Inner Join Example in LINQ and C# to understand the difference between the Inner Join and Left Outer Join.



Will you give this article a +1 ? Thanks in advance




About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

comments

5 Responses to "LINQ – Left Join Example in C#"
  1. Dan Miser said...
    January 21, 2011 at 8:01 AM

    Nice post. That syntax shows how to simulate a left outer join when the 2 tables aren't related, but in things like LINQ to SQL, you can just use this:

    var orderForBooks =
    from bk in Books
    select new
    {
    bk.BookID,
    Name = bk.Name,
    PaymentMode =
    from o in bk.Orders
    select o.PaymentMode
    };

    Granted, the shape of the result set is different (not flattened one per row), but it's another option. You just need to be sure to set up FKs in your database.

  2. Suprotim Agarwal said...
    January 21, 2011 at 9:14 PM

    Yes! Thanks for sharing your code Dan.

  3. Gediminas said...
    May 17, 2012 at 10:56 AM

    Dan Miser's solution creates structure very different from SQL Left join. PaymentMode is IEnumerable collection packed anonymous object. It may be empty. Using this structure in pure C# code is not a problem but sending it to Reporting Services requires additional transformations

  4. Anonymous said...
    November 7, 2012 at 8:41 AM

    It would be nice if your example was for a left outer join on a DB table rather than two hard coded objects.

  5. Golem said...
    November 14, 2012 at 7:57 AM

    Hey.
    Nice Code, but to specify each field in "select" is very "static" I would say.

    My problem is, that do not know the data structure i'am going to join, because it will be specified by user interactions in runtime.

    I want to join two "EnumerableRowCollection"s with unknown fields, but linq allows me only to "select" one of the entity structures at the end.

    Actually I expected same results like a "SQL-Left-Join", that will join table structures as well...

    My code:

    var baseTable = GetDataTable(...);
    var joinTable = GetDataTable(...);

    var baseTableAsEnumerable = baseTable.AsEnumerable();
    var joinTableAsEnumerable = joinTable.AsEnumerable();

    var joinResult = from baseAlias in baseTableAsEnumerable
    join joinAlias in joinTableAsEnumerable
    on baseAlias.Field(baseColumn)
    equals joinAlias.Field(joinColumn)
    into joinedQueryResult
    select joinedQueryResult.First();


    The result of this example only contains column structure of "joinTableAsEnumerable", but should contain both - "baseTableAsEnumerable" AND "joinTableAsEnumerable".

    My idea was to "create" a new "DataRow" by my own, but this will not result in a correct join-entity at the end. Take a look:

    ...
    into joinedQueryResult
    select GetNewJoinedRow(baseTable.Columns, joinTable.Columns, joinedQueryResult.First());

    Any solutions?

 

Copyright © 2009-2014 All Rights Reserved for DevCurry.com by Suprotim Agarwal | Terms and Conditions