3. One-to-Many Joins
A one-to-many join allows a subgroup collection of
related elements to a parent element in another sequence. The most
common example of a one-to-many join is a listing of Customers, each
with a collection of Orders. The same caveat applies to one-to-many
joins as warned in one-to-one joins. LINQ to Objects will not perform
joins as well as a relational database system like SQL Server. Joining
data is what DBMSs are good at, and although you can perform these
joins in LINQ to Objects, it should be reserved for small data volumes.
Do the heavy lifting in SQL Server and fine-tuning in LINQ to Objects.
There are a number of ways to achieve one-to-many joins using LINQ, and these include
Using the join/into combination (or GroupJoin) operator
Using a subquery in the select projection
Using the ToLookup operator
Each of these approaches are covered in detail next, including their performance behavior, but Table 4
summarizes the different performance traits for each syntax when
working with many records at one time or only joining to a single
record.
Table 4. One-to-Many Join Techniques and Their Performance Comparisons
| Technique | Many outer record performance | Single outer record performance | Comments |
|---|
| Using the join/into combination (or GroupJoin) operator | Generally poor; better as inner sequence size grows | Worst | When
run over one million iterations, this performed slower than both other
options. Unless trying to stay syntactically similar to SQL, use either
a subquery or lookup technique. This technique is more effective than
the subquery technique as the inner sequence size grows. Break even at
around five records. |
| Using a subquery in the select projection | Good for small inner sequences only or when ordering of the inner sequence is important | Best | This
technique was only marginally slower than the lookup technique. The
lookup technique traverses the inner sequence only once; this technique
will traverse the inner sequence multiple times. Use this technique
when you need to order the inner sequence elements.
|
| Using the ToLookup operator | Best | Good | If performance is a key requirement, then use this technique. |
The identical sample data used in the previous section on one-to-one joins (Table 2) will continue to be used throughout this section unless otherwise specified.
The join/into (GroupJoin) Operator
A small variation of the query expression join syntax allows joined records to be captured in a subgroup
Listing 17 demonstrates how to achieve a one-to-many group relationship using the join and into keyword combination. The Console output is shown in Output 7. The into
keyword captures each group, and this group can be accessed in the
select projection and subsequently accessed as a property of each
result element.
Listing 7. One-to-Many Join using the join/into keyword combination—see Output 7
var customers = Customer.SampleData();
var orders = Order.SampleData();
var q1 = from c in customers
join o in orders on
c.CustomerID equals o.CustomerID
into cust_orders
select new
{
LastName = c.LastName,
Orders = cust_orders
};
foreach (var customer in q1)
{
Console.WriteLine("Last name: {0}", customer.LastName);
foreach (var order in customer.Orders)
Console.WriteLine(" - Order number: {0}", order.OrderNumber);
}
|
Output 7.
Last name: Gottshall
- Order number: Order 1
- Order number: Order 2
Last name: Valdes
Last name: Gauwain
Last name: Deane
- Order number: Order 3
- Order number: Order 4
Last name: Zeeman
- Order number: Order 5
