6. How to Use Grouping Results in the Same Query (Query Continuation)
It is possible to use grouping results as a local variable within the same query by appending the into
clause after the group when using the query expression syntax (when
using extension method syntax, you just continue your query from the
result of the GroupBy extension method). The into
clause projects the grouping result into a local variable that you can
reference throughout the rest of the query expression, giving you
access to the key values and the grouped elements.
The simplest form of query continuation using groups
is for providing aggregation results within the query’s select
projection clause. Listing 9 demonstrates how to calculate various subtotals for a set of grouped data. The Console results are shown in Output 6.
Listing 9. Query continuation allows you to calculate various aggregation values for each group (think subtotaling)—see Output 6
List<CallLog> calls = CallLog.SampleData();
var q = from c in calls
group c by c.Number into g
select new
{
Number = g.Key,
InCalls = g.Count(c => c.Incoming),
OutCalls = g.Count(c => !c.Incoming),
TotalTime = g.Sum(c => c.Duration),
AvgTime = g.Average(c => c.Duration)
};
foreach (var number in q)
Console.WriteLine(
"{0} ({1} in, {2} out) Avg Time: {3} mins",
number.Number,
number.InCalls,
number.OutCalls,
Math.Round(number.AvgTime, 2));
|
Output 6.
885 983 8858 (4 in, 2 out) Avg Time: 9.33 mins
165 737 1656 (2 in, 0 out) Avg Time: 13.5 mins
364 202 3644 (2 in, 2 out) Avg Time: 13.75 mins
603 303 6030 (0 in, 4 out) Avg Time: 12.5 mins
546 607 5462 (2 in, 4 out) Avg Time: 4.33 mins
848 553 8487 (2 in, 2 out) Avg Time: 13.75 mins
278 918 2789 (2 in, 0 out) Avg Time: 11 mins
|
Query continuation allows very complex rollup analysis to be performed on grouped data with any level of nesting. Listing 10
demonstrates how to group two levels deep. This query groups phone
calls by year and month for each customer record in the source
collection. The groupings are captured in an anonymous type field (YearGroup and MonthGroup), and these are accessible when iterating the result sequence using nested foreach loops. The Console output of this code is shown in Output 7.
Listing 10. The example demonstrates grouping data multiple levels deep—see Output 7
List<Contact> contacts = Contact.SampleData();
List<CallLog> callLog = CallLog.SampleData();
var q = from contact in contacts
select new
{
Name = contact.FirstName + " " +
contact.LastName,
YearGroup = from call in callLog
where call.Number == contact.Phone
group call by call.When.Year
into groupYear
select new
{
Year = groupYear.Key,
MonthGroup =
from c in groupYear
group c by c.When.ToString("MMMM")
}
};
foreach (var con in q)
{
Console.WriteLine("Customer: {0}", con.Name);
foreach (var year in con.YearGroup)
{
Console.WriteLine(" Year:{0}", year.Year);
foreach (var month in year.MonthGroup)
{
Console.WriteLine(" Month:{0}", month.Key);
foreach (var call in month)
{
Console.WriteLine(" {0} - for {1} minutes",
call.When, call.Duration);
}
}
}
}
|
Output 7.
Customer: Barney Gottshall
Year:2006
Month:August
8/7/2006 8:12:00 AM - for 2 minutes
8/7/2006 1:12:00 PM - for 15 minutes
8/7/2006 1:47:00 PM - for 3 minutes
Month:July
7/12/2006 8:12:00 AM - for 5 minutes
7/7/2006 1:47:00 PM - for 21 minutes
Month:June
6/7/2006 1:12:00 PM - for 10 minutes
Customer: Armando Valdes
Year:2006
Month:August
8/8/2006 2:00:00 PM - for 3 minutes
8/8/2006 2:37:00 PM - for 7 minutes
Month:July
7/8/2006 2:00:00 PM - for 32 minutes
7/8/2006 2:37:00 PM - for 13 minutes
...
Customer: Ariel Hazelgrove
Year:2006
Month:August
8/7/2006 9:23:00 AM - for 15 minutes
Month:June
6/14/2006 9:23:00 AM - for 12 minutes
