Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

Preview

Start a free Basic trial
to watch this video

Sign up for Treehouse

Sorting Queries

4:57 with James Churchill

Let's see how we can explicitly sort the results of a query using the `OrderBy` or `OrderByDescending` LINQ operators.

Follow Along

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

Case Sensitivity

Here are some additional resources for learning more about configuring SQL Server collations in order to control case-sensitivity.

Here's a resource that shows a way to selectively enabled case-sensitive search.

Additional Learning

  • 0:00

    Up to this point, we've been retrieving entities in their implicit or

  • 0:04

    natural order.

  • 0:05

    This can cause the entities to be displayed in unexpected ways.

  • 0:09

    Because of this, it's typically a requirement for

  • 0:12

    results to be explicitly sorted by one or more properties.

  • 0:16

    We can explicitly sort the results of a query using the OrderBy or

  • 0:20

    OrderByDescending link operators.

  • 0:23

    Let's start with removing the Where link operator so

  • 0:26

    our query will retrieve all of the comic books from the database.

  • 0:31

    Then, let's sort the results of our query by issue number, in descending order.

  • 0:36

    .OrderByDescending(cb => cb.IssueNumber).

  • 0:49

    Here's the list of our comic books sorted in descending order by issue number.

  • 1:00

    Now our query contains a SQL ORDER BY clause.

  • 1:05

    It's important to note that you can't add multiple calls to the OrderBy or

  • 1:10

    OrderByDescending operators like we could with the Where operator.

  • 1:16

    .OrderBy(cb => cb.PublishedOn).

  • 1:23

    The last call made will be the one that is used.

  • 1:26

    If you need to sort on more than one column, you can use the ThenBy or

  • 1:31

    ThenByDescending operators.

  • 1:36

    TheBy.

  • 1:39

    Now our query will be sorted by the IssueNumber column values in descending

  • 1:44

    order, then by the PublishedOn column values in ascending order.

  • 1:52

    Let's look at an example of how we can leverage deferred execution to extend

  • 1:56

    a query.

  • 1:58

    We can start by defining our base query.

  • 2:00

    That includes a descending sort on issue number.

  • 2:03

    var comicBooksQuery = context.ComicBooks.Include,

  • 2:10

    so our series navigation property will be populated.

  • 2:17

    (cb => cb.Series).

  • 2:20

    Then .OrderByDescending(cb => cb.IssueNumber).

  • 2:29

    Then we could execute that query to retrieve a list of all of the comic books.

  • 2:33

    var comicBooks = comicBooksQuery.ToList().

  • 2:41

    We could also add a Where operator to the base query and execute that query

  • 2:45

    to retrieve the comic books that have an average rating less than seven.

  • 2:49

    var comicBooks2 =

  • 2:53

    comicBooksQuery.Where(cb =>

  • 2:59

    cb.AverageRating < 7).ToList().

  • 3:11

    Because the base query includes an OrderByDescending operator,

  • 3:15

    both queries will include an OrderBy clause.

  • 3:18

    Before we test our queries, let's add a second foreach loop to output the second

  • 3:23

    list of comic books to the Console.

  • 3:27

    Foreach (var comicBook in comicBooks2),

  • 3:34

    then Console.WriteLine(comicBook.DisplayText).

  • 3:44

    Let's also write the count of the second comic books collection to the Console..

  • 3:54

    And let's output an extra blank line just above the second list.

  • 4:06

    And here's our two lists of comic books.

  • 4:09

    The first list is a complete list of comic books sorted by issue number

  • 4:13

    in descending order.

  • 4:14

    The second list is a list of the comic books that have an average rating less

  • 4:18

    than seven.

  • 4:19

    Also sorted by issue number in descending order.

  • 4:30

    Here's the first query that retrieves all of the comic books.

  • 4:38

    Notice that it has an ORDER BY clause.

  • 4:45

    And here's the second query that is filtering by the average rating.

  • 4:52

    As expected, it also has an ORDER BY clause.

You need to sign up for Treehouse in order to download course files.

Sign up