208Microsoft Visual Studio 2010: A Beginner’s Guide
The select many and join clauses are synonymous with SQL inner joins because there must be a foreign key in a child table that matches a parent in the parent table before any records for the parent will be returned. To address the issue of needing to get parents that don’t have children, you must perform a left outer join. To perform the equivalent of a SQL left outer join in LINQ, you must use a standard operator called DefaultIfEmpty. The following query gets a record for all customers, regardless of whether they have orders or not:
C#:
var myShop = new MyShopDataContext();
var customers =
from cust in myShop.Customers join ord in myShop.Orders
on cust.CustomerID equals ord.CustomerID into customerOrders
from custOrd in customerOrders.DefaultIfEmpty() select new
{
Name = cust.Name,
Date = custOrd == null ?
new DateTime(1800, 1, 1) : custOrd.OrderDate
};
foreach (var custOrd in customers)
{
Console.WriteLine(
"Name: " + custOrd.Name +
"Date: " + custOrd.Date);
}
VB:
Dim myShop As New MyShopDataContext
Dim customers =
From cust In myShop.Customers Group Join ord In myShop.Orders
On cust.CustomerID Equals ord.CustomerID Into customersOrders = Group
From custOrd In customersOrders.DefaultIfEmpty() Select New With
{
.Name = cust.Name,
.Date = IIf(custOrd Is Nothing,
New DateTime(1800, 1, 1), custOrd.OrderDate)
}
