Tuesday, February 01, 2011

Left Outer Join in Linq

I always can't quite remember how to do left outer join in Linq. Here is an example of how to do left outer join in linq using C#.

Class definitions:
class Book
{
    public int Id { getset; }
    public String Name { getset; }
}
 
class OrderItem
{
    public int BookId { getset; }
    public int Quantity { getset; }
}
Sample data:
var books = new List<Book>
{
    new Book {Id = 1, Name = "Groovy in Action"},
    new Book {Id = 2, Name = "Implementation Patterns"},
    new Book {Id = 3, Name = "C# Cookbook"}
};
var orderItems = new List<OrderItem>
{
    new OrderItem {BookId = 1, Quantity = 1},
    new OrderItem {BookId = 1, Quantity = 2},
    new OrderItem {BookId = 1, Quantity = 2},
    new OrderItem {BookId = 2, Quantity = 1},
    new OrderItem {BookId = 2, Quantity = 1},
    new OrderItem {BookId = 2, Quantity = 1}
};
Query:
var query = from b in books
            join oi in orderItems on b.Id equals oi.BookId into t
            from r in t.DefaultIfEmpty(new OrderItem())
            select new
                        {
                            BookId = b.Id,
                            BookName = b.Name,
                            Quantity = r.Quantity
                        };
 
foreach (var r in query)
{
    Console.WriteLine("Id = {0} Name = {1} Quantity = {2}",
        r.BookId, r.BookName, r.Quantity);
}
Result:
Id = 1 Name = Groovy in Action Quantity = 1
Id = 1 Name = Groovy in Action Quantity = 2
Id = 1 Name = Groovy in Action Quantity = 2
Id = 2 Name = Implementation Patterns Quantity = 1
Id = 2 Name = Implementation Patterns Quantity = 1
Id = 2 Name = Implementation Patterns Quantity = 1
Id = 3 Name = C# Cookbook Quantity = 0

No comments: