Preview
Video Player
00:00
00:00
Sorting Queries
4:57Let's see how we can explicitly sort the results of a query using the `OrderBy` or `OrderByDescending` LINQ operators.
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