Hooked on LINQ

Hooked on LINQ - Developers' Wiki
for .NET Language Integrated Query

Companion book for this site
LINQ to Objects Using C# 4.0:
Using and Extending LINQ to Objects and Parallel LINQ (PLINQ)
Quick Search

Advanced Search »

Outer Join Sample

Modified: 2009/02/19 10:53 by Raibeart - Categorized as: LINQ to Objects, Patterns, Samples
Edit

Simulating an Outer Join

A normal join using the Join Standard Query Operator performs an inner-join, where outer elements without any inner elementsd are not included in the result. This may not be the desired behaviour. In database parlance, retrieving all outer elements, even when there is no matching record(s) on the inner sequence is called Outer Join.

We can achieve the same in LINQ to Objects by using the DefaultIfEmpty Operator, and handling the null instance in the Select projection.

public static void OuterJoinSimpleExample() { var customers = new List() { new Customer {Key = 1, Name = "Gottshall" }, new Customer {Key = 2, Name = "Valdes" }, new Customer {Key = 3, Name = "Gauwain" }, new Customer {Key = 4, Name = "Deane" }, new Customer {Key = 5, Name = "Zeeman" } };

var orders = new List() { new Order {Key = 1, OrderNumber = "Order 1" }, new Order {Key = 1, OrderNumber = "Order 2" }, new Order {Key = 4, OrderNumber = "Order 3" }, new Order {Key = 4, OrderNumber = "Order 4" }, new Order {Key = 5, OrderNumber = "Order 5" }, };

var q = from c in customers join o in orders on c.Key equals o.Key into g from o in g.DefaultIfEmpty() select new {Name = c.Name, OrderNumber = o == null ? "(no orders)" : o.OrderNumber};

foreach (var i in q) { Console.WriteLine("Customer: {0} Order Number: {1}", i.Name.PadRight(11, ' '), i.OrderNumber); }

Console.ReadLine(); }

public class Customer { public int Key; public string Name; }

public class Order { public int Key; public string OrderNumber; }

Customer: Gottshall    Order Number: Order 1
Customer: Gottshall    Order Number: Order 2
Customer: Valdes       Order Number: (no orders)
Customer: Gauwain      Order Number: (no orders)
Customer: Deane        Order Number: Order 3
Customer: Deane        Order Number: Order 4
Customer: Zeeman       Order Number: Order 5

If you want to use a join operator other than the "equal" operator you will need to use a nested query. On the discussion page a user presents this example

string[] words = {"walking","walked","bouncing","bounced","bounce","talked","running"}; string[] suffixes = {"ing","ed","er","iest"};

Where the post indicates "...and we wish to return all words along with their suffixes, but we still want the word returned (paired with a null), even if no suffix is found."

This code will do just that.

string[] words = {"walking","walked","bouncing","bounced","bounce","talked","running"}; string[] suffixes = {"ing","ed","er","iest"};

var pairs = from word in words from suffix in (from suffix in suffixes where word.EndsWith(suffix) select suffix).DefaultIfEmpty() select new {word, suffix};

Which Yields a result of

word: walking  suffix: ing
word: walked   suffix: ed
word: bouncing suffix: ing
word: bounced  suffix: ed
word: bounce   suffix: null  
word: talked   suffix: ed
word: running  suffix: ing

Note the null value for "bounce"

See also how to do joins on multiple columns - LINQ to SQL Multiple Column Outer Joins

If you would like to comment on this page, click on the Discuss button located on the top-right of each page. Feel free to edit any mistakes or omissions you find. If you have an objection or find in-appropriate content then contact the administrator. This website is not affiliated with Microsoft®, all content and opinions are those of the specific author and some advice, solutions and article may contain unintentional errors - please use care. Other websites by this author: Focused Objective, Geek Speak Decoded.