EditSimulating an Outer Join
A normal join using the
Join Standard Query Operator performs a cross-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<Customer>() {
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<Order>() {
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