As you saw in Chapter 20, you can also order the results of your queries, and join data from two different tables in your query. You have this same ability in your LINQ queries. For example, to retrieve the Book objects in your collection, ordered by author name (author’s first name, since the author’s full name is a single string), you’d use this query:
var resultList =
from myBook in bookList
orderby myBook.Author
select myBook;That output will look like this:
Books by author: Head First C#, by Andrew Stellman C# 3.0 in a Nutshell, by Ben Albahari C# 3.0 Cookbook, by Jay Hilyard Learning C# 3.0, by Jesse Liberty Programming C# 3.0, by Jesse Liberty Programming C#, fourth edition, by Jesse Liberty
The full code for this example is shown in Example 21-4.
Example 21-4. Ordering the results of a query is simple; just use the OrderBy keyword
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Example_21_4_ _ _ _Ordering_Results
{
// simple book class
public class Book
{
public string Title { get; set; }
public string Author { get; set; }
public string Publisher { get; set; }
public int PublicationYear { get; set; }
}
class Program
{
static void Main(string[] args)
{
List<Book> bookList = new List<Book>
{
new Book { Title = "Learning C# 3.0",
Author = "Jesse Liberty",
Publisher = "O'Reilly",
PublicationYear = 2008 },
new Book { Title = "Programming C# 3.0",
Author = "Jesse Liberty",
Publisher = "O'Reilly",
PublicationYear = 2008 },
new Book { Title = "C# 3.0 Cookbook",
Author = "Jay Hilyard",
Publisher = "O'Reilly",
PublicationYear = 2007 },
new Book { Title = "C# 3.0 in a Nutshell",
Author = "Ben Albahari",
Publisher = "O'Reilly",
PublicationYear = 2007 },
new Book { Title = "Head First C#",
Author = "Andrew Stellman",
Publisher = "O'Reilly",
PublicationYear = 2007 },
new Book { Title = "Programming C#, fourth edition",
Author = "Jesse Liberty",
Publisher = "O'Reilly",
PublicationYear = 2005 }
};
var resultList =
from myBook in bookList
orderby myBook.Author
select myBook;
Console.WriteLine("Books by author:");
foreach (var testBook in resultList)
{
Console.WriteLine("{0}, by {1}", testBook.Title,
testBook.Author);
}
}
}
}When you join two tables in SQL, as you did in Chapter 20, you retrieve fields from two or more tables based on their common columns (their foreign keys). To do the same for in-memory collections, you need to join two separate collections. For example, here’s a class that represents a book purchase order:
public class PurchaseOrder
{
public int OrderNumber { get; set; }
public string Title { get; set; }
public int Quantity { get; set; }
}You can imagine another collection using a List of these PurchaseOrder objects. Here’s a partial list:
List<PurchaseOrder> orderList = new List<PurchaseOrder>
{
new PurchaseOrder { OrderNumber = 23483,
Title = "C# 3.0 Cookbook",
Quantity = 57 },If you wanted to return the title, author, and quantity for a particular book or books, you’d need to join the information from the two collections. The Title property is the common field here. It’s the primary key in the bookList collection, and a foreign key in the orderList collection. The query you’d use looks like this:
var resultList =
from myBook in bookList
join myOrder in orderList on myBook.Title equals myOrder.Title
where myOrder.Quantity >= 50
select new {myBook.Title, myBook.Author, myOrder.Quantity};The from and select clauses aren’t any different from what you’ve seen so far; you’re defining an anonymous type here to hold the date you want. The join clause is a bit different, though. You’re joining the bookList collection with the orderList collection to create a new data set to search on. The first part of the join just looks like a from; you’re specifying the second collection to use:
join myOrder in orderList
You have to specify some way to correlate the information in the two collections, though—how can you tell which orders go with which book records? They have the Title property in common. If the Title property of the book is the same as the Title property of the order, that’s a match. The on part of the join clause indicates that you’re defining the condition:
join myOrder in orderList on myBook.Title equals myOrder.TitleNote that you have to use the keyword equals to define the join condition, rather than the == operator.
The where clause specifies that you want the records of orders with more than 50 copies sold. A full example using this query is shown in Example 21-5.
Example 21-5. The Join keyword allows you to combine the data from two different collections into a single result set
using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace Example_21_5_ _ _ _Joining_results { using System; using System.Collections.Generic; using System.Linq; using System.Text; // simple book class public class Book { public string Title { get; set; } public string Author { get; set; } public string Publisher { get; set; } public int PublicationYear { get; set; } } public class PurchaseOrder { public int OrderNumber { get; set; } public string Title { get; set; } public int Quantity { get; set; } } class Program { static void Main(string[] args) { List<Book> bookList = new List<Book> { new Book { Title = "Learning C# 3.0", Author = "Jesse Liberty", Publisher = "O'Reilly", PublicationYear = 2008 }, new Book { Title = "Programming C# 3.0", Author = "Jesse Liberty", Publisher = "O'Reilly", PublicationYear = 2008 }, new Book { Title = "C# 3.0 Cookbook", Author = "Jay Hilyard", Publisher = "O'Reilly", PublicationYear = 2007 }, new Book { Title = "C# 3.0 in a Nutshell", Author = "Ben Albahari", Publisher = "O'Reilly", PublicationYear = 2007 }, new Book { Title = "Head First C#", Author = "Andrew Stellman", Publisher = "O'Reilly", PublicationYear = 2007 }, new Book { Title = "Programming C#, fourth edition", Author = "Jesse Liberty", Publisher = "O'Reilly", PublicationYear = 2005 } }; List<PurchaseOrder> orderList = new List<PurchaseOrder> { new PurchaseOrder { OrderNumber = 23483, Title = "C# 3.0 Cookbook", Quantity = 57 }, new PurchaseOrder { OrderNumber = 57284, Title = "Head First C#", Quantity = 42 }, new PurchaseOrder { OrderNumber = 56389, Title = "Programming C# 3.0", Quantity = 12 }, new PurchaseOrder { OrderNumber = 95639, Title = "C# 3.0 Cookbook", Quantity = 122 }, new PurchaseOrder { OrderNumber = 57493, Title = "C# 3.0 in a Nutshell", Quantity = 43 }, new PurchaseOrder { OrderNumber = 73558, Title = "Programming C# 3.0", Quantity = 99 }, new PurchaseOrder { OrderNumber = 45385, Title = "C# 3.0 Cookbook", Quantity = 35 }, }; var resultList = from myBook in bookList join myOrder in orderList on myBook.Title equals myOrder.Title where myOrder.Quantity >= 50 select new {myBook.Title, myBook.Author, myOrder.Quantity}; Console.WriteLine("Book orders with quantities greater than 50:"); foreach (var testBook in resultList) { Console.WriteLine("Title: {0} Author: {1} Quantity: {2}", testBook.Title, testBook.Author, testBook.Quantity); } } } }
The results of the query look like this:
Book orders with quantities greater than 50: Title: Programming C# 3.0 Author: Jesse Liberty Quantity: 99 Title: C# 3.0 Cookbook Author: Jay Hilyard Quantity: 57 Title: C# 3.0 Cookbook Author: Jay Hilyard Quantity: 122