List Queries

[MUSIC] 0:00 Linked entities allows us to write queries against our model using C#. 0:05 This allows Visual Studio IntelliSense to assist us with the syntax of our queries 0:10 and with discovering the properties that are available on our entities. 0:15 So how does link to entities work? 0:19 LINQ Queries, either in fluent or 0:22 queries syntax, are written against one of the contexts as DbSet properties. 0:24 EF translates those link queries into the appropriate SQL statements, 0:29 which are then executed against the database. 0:33 EF then returns the query results using entity data model classes, 0:37 value types or projections into anonymous types. 0:42 In most cases, EF shields you from having to write SQL Queries, 0:47 though having a basic understanding of databases in SQL can be helpful. 0:51 That's especially true when you need to debug or troubleshoot a problem. 0:56 Before we start writing queries, let's see how we can monitor EF's database activity 1:01 so we can audit the SQL that EF generates from our link queries. 1:05 Luckily, this is easy to do. 1:10 We just need to set the context as database log property 1:12 to a method that will log messages. 1:15 We can define an anonymous method that accepts a single message parameter and 1:19 writes that parameter value to the output window using the Debug.WriteLine method. 1:24 context \.Database.Log 1:30 = (message) => 1:35 Debug.WriteLine(message). 1:39 Be sure to add the using directive for the System.Diagnostics namespace. 1:46 Before we write our first query, let's comment out all of this 1:52 existing code by selecting the code and pressing Ctrl+K, Ctrl+C. 1:56 Let's start with the simplest query that we can write, 2:03 calling ToList on a DbSet property. 2:07 We just have the one DbSet property, comicBooks, so we'll use that one. 2:10 var comicBooks = context ComicBooks.ToList. 2:15 And let's write the number of comic books that are returned 2:24 from our query to the console. 2:28 Console.WriteLine, (" 2:31 # of comic books, (0), 2:37 comicBooks.Count"). 2:42 Let's run the app. 2:48 Our query returned a total of nine comic books. 2:52 If we scroll up a bit in the output window, 3:04 we can see the queries that EF executes in order to determine if the database exists. 3:06 And if it's in sync with our model. 3:11 The last query is our comic books query. 3:19 This is a straightforward query that selects all the comic book table columns. 3:22 Every row from the table will be returned, 3:27 which in our case is nine rows as we aren't doing any filtering. 3:29 We can rewrite this query using link queries syntax. 3:38 var comicBooks = from cb 3:41 in context.ComicBooks select cb. 3:46 Once we finish rewriting the query, notice that we get an error trying to access 3:54 the Count Property on the comicBooks variable. 3:59 If we hover over the comicBooks variable, 4:02 we can see that the variables type is IQueryable of type comicBook. 4:04 The IQueryable interface extends the IEnumerable interface. 4:09 The query that is represented by the comic book's variable won't be executed until 4:14 it's enumerated, either by using a loop or by calling a method like to list or Count. 4:19 This behavior is called deferred execution. 4:25 Let's run an experiment to see deferred execution in action. 4:29 Rename the comic books variable to comingBooksQuery, and 4:33 call the to list method return in the results into the comicBooksVariable, 4:38 var comicBooks = comicBooksQuery.ToList Set a breakpoint 4:45 on the line of code containing the ToList method call. 4:50 And run the app. 4:57 Here we are at our breakpoint. 5:07 if we look in the output window, we can see that the last query that was executed 5:10 was ES query against the migration history table. 5:15 So, while we've defined our query, it hasn't been executed yet and it won't. 5:20 Not until we call the ToList method. 5:25 Press F10 to execute the next line of code. 5:28 Now in the output window, we can see that EFS executed our query. 5:34 Go ahead and press F5, and then Enter to continue execution. 5:39 Deferred execution on the surface can seem unintuitive or even disadvantageous. 5:45 Deferred execution does have its advantages. 5:52 Including the ability to execute a query more than once, or to combine or 5:55 extend the query before it's executed. 5:59 We'll see an example of the latter later in the section. 6:02 Next, let's see how we can filter the results of our query. 6:06