1
00:00:00,000 --> 00:00:05,034
[MUSIC]

2
00:00:05,034 --> 00:00:10,300
Linked entities allows us to write
queries against our model using C#.

3
00:00:10,300 --> 00:00:15,200
This allows Visual Studio IntelliSense to
assist us with the syntax of our queries

4
00:00:15,200 --> 00:00:19,380
and with discovering the properties
that are available on our entities.

5
00:00:19,380 --> 00:00:21,440
So how does link to entities work?

6
00:00:22,675 --> 00:00:24,700
LINQ Queries, either in fluent or

7
00:00:24,700 --> 00:00:29,700
queries syntax, are written against one
of the contexts as DbSet properties.

8
00:00:29,700 --> 00:00:33,940
EF translates those link queries
into the appropriate SQL statements,

9
00:00:33,940 --> 00:00:37,040
which are then executed
against the database.

10
00:00:37,040 --> 00:00:42,220
EF then returns the query results
using entity data model classes,

11
00:00:42,220 --> 00:00:45,650
value types or
projections into anonymous types.

12
00:00:47,310 --> 00:00:51,268
In most cases, EF shields you
from having to write SQL Queries,

13
00:00:51,268 --> 00:00:56,370
though having a basic understanding
of databases in SQL can be helpful.

14
00:00:56,370 --> 00:00:59,840
That's especially true when you need
to debug or troubleshoot a problem.

15
00:01:01,040 --> 00:01:05,770
Before we start writing queries, let's see
how we can monitor EF's database activity

16
00:01:05,770 --> 00:01:10,040
so we can audit the SQL that EF
generates from our link queries.

17
00:01:10,040 --> 00:01:12,140
Luckily, this is easy to do.

18
00:01:12,140 --> 00:01:15,650
We just need to set the context
as database log property

19
00:01:15,650 --> 00:01:17,480
to a method that will log messages.

20
00:01:19,780 --> 00:01:24,630
We can define an anonymous method that
accepts a single message parameter and

21
00:01:24,630 --> 00:01:30,087
writes that parameter value to the output
window using the Debug.WriteLine method.

22
00:01:30,087 --> 00:01:35,879
context \.Database.Log

23
00:01:35,879 --> 00:01:39,981
= (message) =>

24
00:01:39,981 --> 00:01:46,990
Debug.WriteLine(message).

25
00:01:46,990 --> 00:01:52,218
Be sure to add the using directive for
the System.Diagnostics namespace.

26
00:01:52,218 --> 00:01:56,634
Before we write our first query,
let's comment out all of this

27
00:01:56,634 --> 00:02:01,568
existing code by selecting the code and
pressing Ctrl+K, Ctrl+C.

28
00:02:03,697 --> 00:02:07,587
Let's start with the simplest
query that we can write,

29
00:02:07,587 --> 00:02:10,180
calling ToList on a DbSet property.

30
00:02:10,180 --> 00:02:15,725
We just have the one DbSet property,
comicBooks, so we'll use that one.

31
00:02:15,725 --> 00:02:24,030
var comicBooks = context
ComicBooks.ToList.

32
00:02:24,030 --> 00:02:28,956
And let's write the number of
comic books that are returned

33
00:02:28,956 --> 00:02:31,528
from our query to the console.

34
00:02:31,528 --> 00:02:37,101
Console.WriteLine, ("

35
00:02:37,101 --> 00:02:42,672
# of comic books, (0),

36
00:02:42,672 --> 00:02:48,023
comicBooks.Count").

37
00:02:48,023 --> 00:02:49,128
Let's run the app.

38
00:02:52,650 --> 00:02:56,415
Our query returned a total
of nine comic books.

39
00:03:04,524 --> 00:03:06,858
If we scroll up a bit
in the output window,

40
00:03:06,858 --> 00:03:11,980
we can see the queries that EF executes in
order to determine if the database exists.

41
00:03:11,980 --> 00:03:14,357
And if it's in sync with our model.

42
00:03:19,263 --> 00:03:22,460
The last query is our comic books query.

43
00:03:22,460 --> 00:03:27,050
This is a straightforward query that
selects all the comic book table columns.

44
00:03:27,050 --> 00:03:29,702
Every row from the table will be returned,

45
00:03:29,702 --> 00:03:33,690
which in our case is nine rows as
we aren't doing any filtering.

46
00:03:38,066 --> 00:03:41,582
We can rewrite this query
using link queries syntax.

47
00:03:41,582 --> 00:03:46,727
var comicBooks = from cb

48
00:03:46,727 --> 00:03:54,960
in context.ComicBooks select cb.

49
00:03:54,960 --> 00:03:59,070
Once we finish rewriting the query, notice
that we get an error trying to access

50
00:03:59,070 --> 00:04:02,390
the Count Property on
the comicBooks variable.

51
00:04:02,390 --> 00:04:04,880
If we hover over the comicBooks variable,

52
00:04:04,880 --> 00:04:09,210
we can see that the variables type
is IQueryable of type comicBook.

53
00:04:09,210 --> 00:04:14,660
The IQueryable interface extends
the IEnumerable interface.

54
00:04:14,660 --> 00:04:19,030
The query that is represented by the comic
book's variable won't be executed until

55
00:04:19,030 --> 00:04:25,340
it's enumerated, either by using a loop or
by calling a method like to list or Count.

56
00:04:25,340 --> 00:04:28,000
This behavior is called
deferred execution.

57
00:04:29,810 --> 00:04:33,550
Let's run an experiment to see
deferred execution in action.

58
00:04:33,550 --> 00:04:38,632
Rename the comic books variable
to comingBooksQuery, and

59
00:04:38,632 --> 00:04:45,137
call the to list method return in
the results into the comicBooksVariable,

60
00:04:45,137 --> 00:04:50,425
var comicBooks =
comicBooksQuery.ToList Set a breakpoint

61
00:04:50,425 --> 00:04:55,120
on the line of code containing
the ToList method call.

62
00:04:57,560 --> 00:04:59,097
And run the app.

63
00:05:07,170 --> 00:05:08,740
Here we are at our breakpoint.

64
00:05:10,810 --> 00:05:15,580
if we look in the output window, we can
see that the last query that was executed

65
00:05:15,580 --> 00:05:18,680
was ES query against
the migration history table.

66
00:05:20,080 --> 00:05:25,120
So, while we've defined our query,
it hasn't been executed yet and it won't.

67
00:05:25,120 --> 00:05:26,950
Not until we call the ToList method.

68
00:05:28,010 --> 00:05:30,470
Press F10 to execute
the next line of code.

69
00:05:34,090 --> 00:05:38,650
Now in the output window,
we can see that EFS executed our query.

70
00:05:39,670 --> 00:05:43,340
Go ahead and press F5, and
then Enter to continue execution.

71
00:05:45,970 --> 00:05:52,320
Deferred execution on the surface can
seem unintuitive or even disadvantageous.

72
00:05:52,320 --> 00:05:55,390
Deferred execution does
have its advantages.

73
00:05:55,390 --> 00:05:59,780
Including the ability to execute a query
more than once, or to combine or

74
00:05:59,780 --> 00:06:02,440
extend the query before it's executed.

75
00:06:02,440 --> 00:06:06,280
We'll see an example of
the latter later in the section.

76
00:06:06,280 --> 00:06:09,730
Next, let's see how we can
filter the results of our query.