Chapter 7
Sorting and Grouping Queries
In This Chapter
Sorting Information
Grouping Information
“If they want peace, nations should avoid the pin-pricks that precede cannon shots.”
—Napoleon Bonaparte
The most common thing after selecting data is ordering that data. This chapter explores sorting information as well as the reverse capability of Language Integrated Query (LINQ), and looks at the brother of the select, grouping. LINQ supports ascending and descending sorts as well as secondary sorts. If you are familiar with something like Structured Query Language (SQL) sorting, the concepts will be familiar and the implementation will be relatively easy to grasp.
Grouping concepts are similar to SQL grouping concepts, too. Although the implementation looks a little different, the general result—create a resultset out of multiple sources—is the same.
As mentioned in previous chapters, all of LINQ sits squarely on top of extension methods. Some capabilities in LINQ have LINQ keywords and some are only available through extension methods. Where only an extension method exists, it is pointed out in the material.
Sorting Information
LINQ supports sorting in ascending and descending order with the orderby keyword. LINQ also supports secondary sorts in both ascending and descending order by adding comma-delimited lists of items on which to sort.
A collection of items can also be reversed by invoking the Reverse extension method on a collection. Reverse is not a supported LINQ keyword in C#.
Sorting in Ascending and Descending Order
.NET 3.5 introduces the extension methods OrderBy, OrderByDescending, ThenBy, ThenByDescending, and Reverse. These methods extend Enumerable and Queryable. Many LINQ keywords are nearly identical to their underlying extension methods and others are implicit. For example, sorts are ascending by default; if you add the descending keyword, the extension method methodnameDescending is invoked. Some LINQ capabilities use extension methods but don’t have LINQ keywords. For example, ThenBy performs secondary sorts but it is the LINQ query and context that implicitly indicate that ThenBy needs to be emitted. (Refer to the subsection “Performing Secondary Sorts” to see a scenario that brings ThenBy into play.)
Listing 7.1 demonstrates how to split a string of words and sort those words in ascending order (see Figure 7.1 for a visualization), and Listing 7.2 shows how to introduce the descending keyword to convert the LINQ to a reverse-sort order sort. The introduction of the descending keyword implicitly instructs the compiler to emit the OrderByDescending extension method call. (For the remainder of this chapter, the underlying extension method isn’t explicitly stated by name unless there is no LINQ keyword(s) that provides that behavior.)
Figure 7.1 The output from Listing 7.1 shows the words in the phrase sorted in ascending order.
Listing 7.1 Splitting a String of Words and Sorting Them in Ascending Order
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SortingDemo
{
class Program
{
static void Main(string[] args)
{
// inspired
string quote =
“Most people have the will to win, few have the will to prepare to win.
// make an array dropping empty items
string[] words = quote.Split(new char[]{‘ ’, ‘,’, ‘.’},
StringSplitOptions.RemoveEmptyEntries);
// sort ascending
var sorted = from word in words orderby word select word;
foreach (var s in sorted)
Console.WriteLine(s);
Console.ReadLine();
}
}
}
In Listing 7.1, the query is assigned to the anonymous type sorted, and the sort behavior is introduced by the orderby word clause. Can you guess the compiler-generated type of the variable sorted? If you answered IEnumerable<string>, you are on top of things. The actual type is IOrderedEnumerable<string>, but you wouldn’t know that without detailed exploration of the .NET Framework and the emitted Microsoft Intermediate Language (MSIL). Listing 7.2 shows the introduction of the descending modifier on the orderby clause.
Listing 7.2 Sorting the Same Array of Words in Descending Order
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SortDescending
{
class Program
{
static void Main(string[] args)
{
// inspired
string quote =
“Most people have the will to win, few have the will to prepare to win.”;
// make an array dropping empty items
string[] words = quote.Split(new char[] { ‘ ’, ‘,’, ‘.’ },
StringSplitOptions.RemoveEmptyEntries);
// sort descending
var descending = from word in words orderby word descending select word;
foreach (var s in descending)
Console.WriteLine(s);
Console.ReadLine();
}
}
}
The presence of the descending keyword in Listing 7.2 causes the compiler to emit a call to Enumerable.OrderByDescending. (Refer to Figure 7.2 to see the output from Listing 7.2.)
Figure 7.2 The same quote from Bobby Knight—basketball coach—sorted in descendingword order.
Sort in Descending Order Using the Extension Method Directly
To demonstrate the correlation between LINQ and extension methods, Listing 7.3 contains code that fundamentally is identical to Listing 7.2. The difference is that the code calls the OrderByDescending extension method directly with a Lambda Expression (s ⇒ s) indicating the Key that describes the Element to sort on.
Listing 7.3 Sorting in Descending Order by Invoking the Extension Method Directly and Providing the Lambda Expression That Indicates the Sort Key
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SortDescExtensionMethod
{
class Program
{
static void Main(string[] args)
{
// inspired
string quote =
“Most people have the will to win, few have the will to prepare to win.”;
// make an array dropping empty items
string[] words = quote.Split(new char[]{‘ ’, ‘,’, ‘.’},
StringSplitOptions.RemoveEmptyEntries);
// use extension method directly to sort descending
foreach (var s in words.OrderByDescending(s ⇒ s))
Console.WriteLine(s);
Console.ReadLine();
}
}
}
Performing Secondary Sorts
A primary sort is the first field or predicate in the orderby clause. A secondary sort in extension-method speak is the ThenBy extension method. For LINQ, just use a comma-delimited list; items after the first item in the orderby clause become secondary sort criteria. Listing 7.4 sorts anonymous gamblers in ascending order by LastName and Age (see Figure 7.3). Age is the secondary sort criteria. Listing 7.5 demonstrates the secondary sort with the secondary sort item in descending order (see Figure 7.4). A secondary sort field with the descending modifier emits the ThenByDescending extension method.
Figure 7.3 The gamblers with a secondary sort on age, both in ascending order. (The field names were shortened using compound type initialization to conserve page-space.)
Figure 7.4 Now the younger people with the same last name follow the older people.
Listing 7.4 Sorting on LastName and Age; Age Is the Secondary Sort Predicate
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SecondarySort
{
class Program
{
static void Main(string[] args)
{
var gamblers = new []{
new {LastName=“Kimmel”, First=“Paul”, Age=41},
new {LastName=“Swanson”, First=“Dena”, Age=26},
new {LastName=“Swanson”, First=“Joe”, Age=4},
new {LastName=“Kimmel”, First=“Noah”, Age=11}};
// thenby is implicit in second sort argument
var sordid = from gambler in gamblers orderby gambler.LastName, gambler.Age
select gambler;
foreach(var playa in sordid)
Console.WriteLine(playa);
Console.ReadLine();
}
}
}
Listing 7.5 A Secondary Sort Where the Secondary Criteria Is in Descending Order
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SecondarySort
{
class Program
{
static void Main(string[] args)
{
var gamblers = new []{
new {LastName=“Kimmel”, First=“Paul”, Age=41},
new {LastName=“Swanson”, First=“Dena”, Age=26},
new {LastName=“Swanson”, First=“Joe”, Age=4},
new {LastName=“Kimmel”, First=“Noah”, Age=11}};
// thenby is implicit in second sort argument
var sordid = from gambler in gamblers
orderby gambler.LastName, gambler.Age descending
select gambler;
foreach(var playa in sordid)
Console.WriteLine(playa);
Console.ReadLine();
}
}
}
In Figure 7.3, the sort is name order followed by age order within the same last name. Younger gamblers are shown first. It should be no surprise—if you are familiar with sorts—that Figure 7.4 has simply reversed the order within the same name, as in Listing 7.5, with the younger gamblers last. (An anonymous type was used to generate the actual images, shortening the field names LastName and FirstName to First and Last, to create a better page fit.)
Reversing the Order of Items
The reverse behavior simply changes the order in a set; with n elements, the nth becomes the first item, the nth–1 item is second, and so on, and the first item is the last item after the reverse behavior is invoked. This happens regardless of the ordering of the items.
There is no reverse keyword in LINQ, so if you want to use it, you have to invoke the extension method. Listing 7.6 reorders the words of inspiration from the irascible Bobby Knight (winningest NCAA basketball coach in history). Refer to Figure 7.5 for the output.
Figure 7.5 The extension method Reverse in action.
Listing 7.6 Invoking the Reverse Extension Method Reverses the Words of Inspiration from Texas Tech Coach Bobby Knight
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SortingDemo
{
class Program
{
static void Main(string[] args)
{
// inspired
string quote =
“Most people have the will to win, few have the will to prepare to win.”;
// make an array dropping empty items
string[] words = quote.Split(new char[] { ‘ ’, ‘,’, ‘.’ },
StringSplitOptions.RemoveEmptyEntries);
foreach (var s in words.Reverse())
Console.WriteLine(s);
Console.ReadLine();
}
}
}
In Listing 7.6, String.Split is used to split the quote by spaces, periods, and commas. The StringSplitOptions.RemoveEmptyEntries value removes whitespace or empty entries, such as would appear after the comma followed by a space (“…will to win, few have the will”) in the middle of the text.
Grouping Information
Many elements of LINQ are very similar to SQL programming concepts. It is important to remember, however, that LINQ works with custom objects, XML, and database data even though many of the Key constructs are like SQL constructs. One such construct is the ability to organize data by similarities into logical groupings.
Grouping behavior is supported by the group by clause in LINQ. In the code in Listing 7.7, the group by clause is used to organize an array of integers into logical groups by even and odd numbers (see Figure 7.6). Because the group by clause is pretty straightforward in the example, a nested Array.ForEach statement with nested Lambda Expressions were used to iterate over and display the two groups for added flavor. (You might encounter code like the nested Array.ForEach with a Lambda Expression with multiple statements, a ternary expression, and a second Lambda Expression, but this part of the code is not that straightforward.)
Figure 7.6 The group is represented as an instance of GroupedEnumerable containing multiple sequences referenced by a key.
Listing 7.7 A group by Clause Organizing an Array of Integers into Odd and Even Sequences
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace GroupBy
{
class Program
{
static void Main(string[] args)
{
var nums = new int[]{1,2,3,4,5,6,7,8,9,10};
var result = from n in nums group n by n % 2;
// really quite funky
Array.ForEach(result.ToArray(), ⇒{
Console.WriteLine(x.Key==0? “evens:” : “odds:”);
Array.ForEach(x.ToArray(), y⇒Console.WriteLine(y));});
Console.ReadLine();
}
}
}
In the grouping, results are organized as two sequences with a Key. The Key is described by the group predicate, which is the result of n mod 2, or 0 and 1. In the result, if the Key is zero, you are iterating over the evens and if the Key is 1, you are iterating over the odds. If the group by predicate were n mod 3 (n%3), the result would contain three sequences with keys 0, 1, and 2.
The results of a group by are organized in IGrouping objects that contain a Key and Element pair. The Key represents the group that the datum belongs to and the Element represents the data. Listing 7.8 shows a common grouping of data. In the example, products and categories are read from the Northwind database into a list of custom objects and are grouped and ordered. (Refer to Figure 7.7 for an example of the output. The LINQ query is shown in bold.)
Listing 7.8 Custom Objects Read from the Northwind Database and Grouped By a LINQ group… by…into Statement
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace GroupIntoDemo
{
class Program
{
// set up a typical SQL join
static string sql =
“SELECT Categories.CategoryName, Products.ProductID, ” +
“Products.ProductName, Products.UnitsInStock, Products.UnitPrice ” +
“FROM Categories INNER JOIN ” +
“Products ON Categories.CategoryID = Products.CategoryID”;
// use the default northwind connection string
static string connectionString =
“Data Source=BUTLER;Initial Catalog=Northwind;Integrated Security=True”;
// define a simple class using automatic properties
public class ProductItem
{
public string CategoryName{ get; set; }
public int ProductID{ get; set; }
public string ProductName{ get; set; }
public int UnitsInStock{ get; set; }
public decimal UnitPrice{ get; set; }
public override string ToString()
{
const string mask =
“Category Name: {0}, ” +
“Product ID: {1}, ” +
“Product Name: {2}, ” +
“Units In Stock: {3}, ” +
“Unit Price: {4}”;
return string.Format(mask, CategoryName,
ProductID, ProductName, UnitsInStock,
UnitPrice);
}
}
// a read helper
static T SafeRead<T>(IDataReader reader, string name, T defaultValue)
{
object o = reader[name];
if( o != System.DBNull.Value && o != null )
return (T)Convert.ChangeType(o, defaultValue.GetType());
return defaultValue;
}
static void Main(string[] args)
{
List<ProductItem> products = new List<ProductItem>();
// read all of the data into custom objects
using(SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
products.Add(new ProductItem{
CategoryName=SafeRead(reader, “CategoryName”, “”),
ProductID=SafeRead(reader, “ProductID”, −1),
ProductName=SafeRead(reader, “ProductName”, “”),
UnitsInStock=SafeRead(reader, “UnitsInStock”, 0),
UnitPrice=SafeRead(reader, “UnitPrice”, 0M)});
}
}
// make sure I have some data
Array.ForEach(products.ToArray(), y⇒Console.WriteLine(y));
Console.ReadLine();
Console.Clear();
string line = new string(‘−’, 40);
// define the LINQ group
var grouped = from p in products
orderby p.CategoryName, p.ProductName
group p by p.CategoryName into grp
select new {Category = grp.Key, Product = grp };
// dump each group
Array.ForEach(grouped.ToArray(), g⇒
{
Console.WriteLine(g.Category);
Console.WriteLine(line);
// dump each product in the group
Array.ForEach(g.Product.ToArray(), p⇒
Console.WriteLine(p));
Console.WriteLine(Environment.NewLine);
});
Console.ReadLine();
}
}
}
Figure 7.7 A partial view of the formatted result of the grouping query.
The projected result container grouped contains the product information sorted by CategoryName with a secondary sort on ProductName organized into groups by category (CategoryName). The CategoryName becomes the Key and each ProductItem object is stored in the Element named Product. The nested Array.ForEach dumped the grouped and ordered resultset. (You could try ObjectDumper for a simpler way to dump these projections. ObjectDumper was VB sample code. Look for the ObjectDumper sample code here: C:Program FilesMicrosoft Visual Studio 9.0Samples1033CSharpSamplesLinqSamplesObjectDumper.)
Finally, grouping is also supported by the extension method ToLookup. ToLookup creates a Dictionary of Key and value pairs. The difference is that with a Dictionary, you get a single Key and value pair, but ToLookup returns a Key and value pair with the value part being a collection of objects.
Summary
Sorting and grouping are an integral part of software development. LINQ has extensive support for both sorting and grouping. Primary and secondary sorts are supported directly and reversing the order of items is supported by the extension method Reverse. Grouping in LINQ creates Key and Element pairs. The Key defines the subgroup and the Element contains the sequence of items within that group.