Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic trial
to watch this video
Let's see how we can filter the results of a query using the `Where` LINQ operator.
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.2 -b filtering-queries
-
0:00
Our list query is currently retrieving all of the comic books from the database.
-
0:05
A typical requirement is to retrieve a list of entities that match
-
0:08
a set of criteria.
-
0:10
This is known as filtering.
-
0:12
We can filter the results of a query by using the where link operator.
-
0:20
.Where(cb => cb.IssueNumber == 1).
-
0:28
This query will return all of the comic books that have an issue number of 1.
-
0:33
Before we test our query, let's use a for
-
0:36
each loop to write each comic book's display text property to the console.
-
0:42
For each var comicBook in
-
0:47
comicBooks console.WriteLine
-
0:54
comicBook DisplayText.
-
0:59
Let's also include the comic book series property.
-
1:04
.include cb goes to cb.series.
-
1:11
And lastly add an additional Console.WriteLine method call in order to
-
1:16
add a blank line between the list of comic books and the comic book count.
-
1:24
Console.WriteLine.
-
1:30
Our query returned a total of three comic books.
-
1:34
And the generated query now contains a WHERE clause.
-
1:46
We can add multiple calls to the where
-
1:51
method in order to filter by more than
-
1:56
one expression .where cb goes to
-
2:01
cb.Series.Title = The Amazing Spider-Man.
-
2:07
This query will return all coming comic books whose issue number is one and
-
2:12
whose series title is The Amazing Spider-Man.
-
2:15
An alternative way to write this query,
-
2:17
is to combine the filtering expressions into a single conditional statement.
-
2:24
If we wanted to get all the comic books whose issue number is one or who's
-
2:29
series title is The Amazing Spider-Man we can change the conditional and
-
2:33
operator to a conditional or.
-
2:40
Our query now returns a total of five comic books.
-
2:47
And the query's WHERE clause now contains two conditions separated by the OR
-
2:52
keyword.
-
2:57
Using the equality operator,
-
2:59
we'll require that the comic book series title match a provided string.
-
3:03
If we want all of the comic books whose series titles contained the string man,
-
3:07
we can switch to using the string contains method.
-
3:12
Where cb goes to cb.series.Title.Contains.
-
3:24
Man.
-
3:29
Now, our query returns a total of 6 comic books.
-
3:35
And the queries where clause is using the like operator with wildcards surrounding
-
3:40
our search text, which is the SQL way of performing a contained search.
-
3:45
In this query, our man's search text with a lowercase m
-
3:49
matched column values containing man with an uppercase M.
-
3:54
Generally speaking when using EF the casing of the string
-
3:57
doesn't matter when filtering data.
-
4:00
Though that behavior depends on how your database server is configured.
-
4:04
By default SQL Server is configured to be case insensitive.
-
4:08
Though it's possible to change this configuration.
-
4:12
For more information on case sensitivity and EF and
-
4:14
SQL Server, see the teacher's notes.
-
4:17
After a short Co challenge we'll see how we can order the results of our query.
You need to sign up for Treehouse in order to download course files.
Sign up