Sorting Queries4:57 with James Churchill
Let's see how we can explicitly sort the results of a query using the `OrderBy` or `OrderByDescending` LINQ operators.
To follow along commiting your changes to this course, you'll need to fork the dotnet-comic-book-gallery-model repo. Then you can clone, commit, and push your changes to your fork like this:
git clone <your-fork> cd dotnet-comic-book-gallery-model git checkout tags/v4.3 -b sorting-queries
Up to this point, we've been retrieving entities in their implicit or 0:00 natural order. 0:04 This can cause the entities to be displayed in unexpected ways. 0:05 Because of this, it's typically a requirement for 0:09 results to be explicitly sorted by one or more properties. 0:12 We can explicitly sort the results of a query using the OrderBy or 0:16 OrderByDescending link operators. 0:20 Let's start with removing the Where link operator so 0:23 our query will retrieve all of the comic books from the database. 0:26 Then, let's sort the results of our query by issue number, in descending order. 0:31 .OrderByDescending(cb => cb.IssueNumber). 0:36 Here's the list of our comic books sorted in descending order by issue number. 0:49 Now our query contains a SQL ORDER BY clause. 1:00 It's important to note that you can't add multiple calls to the OrderBy or 1:05 OrderByDescending operators like we could with the Where operator. 1:10 .OrderBy(cb => cb.PublishedOn). 1:16 The last call made will be the one that is used. 1:23 If you need to sort on more than one column, you can use the ThenBy or 1:26 ThenByDescending operators. 1:31 TheBy. 1:36 Now our query will be sorted by the IssueNumber column values in descending 1:39 order, then by the PublishedOn column values in ascending order. 1:44 Let's look at an example of how we can leverage deferred execution to extend 1:52 a query. 1:56 We can start by defining our base query. 1:58 That includes a descending sort on issue number. 2:00 var comicBooksQuery = context.ComicBooks.Include, 2:03 so our series navigation property will be populated. 2:10 (cb => cb.Series). 2:17 Then .OrderByDescending(cb => cb.IssueNumber). 2:20 Then we could execute that query to retrieve a list of all of the comic books. 2:29 var comicBooks = comicBooksQuery.ToList(). 2:33 We could also add a Where operator to the base query and execute that query 2:41 to retrieve the comic books that have an average rating less than seven. 2:45 var comicBooks2 = 2:49 comicBooksQuery.Where(cb => 2:53 cb.AverageRating < 7).ToList(). 2:59 Because the base query includes an OrderByDescending operator, 3:11 both queries will include an OrderBy clause. 3:15 Before we test our queries, let's add a second foreach loop to output the second 3:18 list of comic books to the Console. 3:23 Foreach (var comicBook in comicBooks2), 3:27 then Console.WriteLine(comicBook.DisplayText). 3:34 Let's also write the count of the second comic books collection to the Console.. 3:44 And let's output an extra blank line just above the second list. 3:54 And here's our two lists of comic books. 4:06 The first list is a complete list of comic books sorted by issue number 4:09 in descending order. 4:13 The second list is a list of the comic books that have an average rating less 4:14 than seven. 4:18 Also sorted by issue number in descending order. 4:19 Here's the first query that retrieves all of the comic books. 4:30 Notice that it has an ORDER BY clause. 4:38 And here's the second query that is filtering by the average rating. 4:45 As expected, it also has an ORDER BY clause. 4:52
You need to sign up for Treehouse in order to download course files.Sign up