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 write a LINQ to Entities list query against our model using C#.
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.1 -b list-queries
Additional Learning
[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
You need to sign up for Treehouse in order to download course files.
Sign up