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