Filtering Queries4:22 with James Churchill
Let's see how we can filter the results of a query using the `Where` LINQ operator.
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
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.
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