Data shaping isn’t limited to adding only scalar values to
the anonymous classes we return. We can also return additional sub-collections
of objects as part of our LINQ query. For example, we could modify our query
like so to return a sub-collection of the most recent 5 orders for each
customer (note how the Take() LINQ aggregate method can be used to return only
5 results from the database):
Listing 12
GridView1.DataSource = from customer in db.Customers
where customer.Country == "USA"
orderby customer.CompanyName
select new {
CustomerID = customer.CustomerID,
CompanyName = customer.CompanyName,
City = customer.City,
Region = customer.Region,
NumOrders = customer.Orders.Count,
LastOrder = customer.Orders.Max(o => o.OrderDate),
Orders = customer.Orders.OrderByDescending(o => o.OrderDate).Take(5)
};This query returns a collection of anonymous objects with
each instance containing 7 properties – one of which is a sub-collection of at
most 5 Orders associated with the customer. I can then update my GridView like
below to hierarchically bind the Customer’s orders within a templated column of
the Grid to generate a bulleted list of “Recent Orders”:
Listing 13
<asp:GridView ID="GridView1" AutoGenerateColumns="false" runat="server">
<Columns>
<asp:BoundField HeaderText="Customer ID" DataField="CustomerID" />
<asp:BoundField HeaderText="Name" DataField="CompanyName" />
<asp:BoundField HeaderText="City" DataField="City" />
<asp:BoundField HeaderText="State" DataField="Region" />
<asp:BoundField HeaderText="NumOrders" DataField="NumOrders" />
<asp:TemplateField HeaderText="Recent Orders">
<ItemTemplate>
<ul>
<asp:Repeater datasource='<%# Eval("Orders") %>' runat="server">
<ItemTemplate>
<li>
<%# Eval("OrderID")%>
(<%# Eval("OrderDate", "{0:dd MMM yyyy}")%>)
</li>
</ItemTemplate>
</asp:Repeater>
</ul>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>Which will cause this page to be output like so:
Figure 5
DLINQ in the sample above was smart and optimized the data
access to only hit the database twice – once to retrieve the top-level customer
data, and then once to retrieve all of the needed orders for our particular
customers (DLINQ then split this orders result and associated each order
correctly with the appropriate customer). This perf optimization avoided us
having to hit the database separately to populate and display each individual
customer order collection (note that we could also have alternatively expressed
to DLINQ to lazy populate the Orders if we wanted this behavior instead).
