In this section, Phil explains the process of when Entity Framework Core actually executes LINQ statements against the database.
- [Narrator] It's important to understand the query execution process. We write queries by writing LINQ statements against the DbSet. This LINQ is then, converted into data storage specific queries and then, the results are cast for reuse. Something new in the EF Core which we'll cover later in this class, is using SQL and LINQ together. Regardless, the results are cashed and then, can be reused later.
We saw the results of that in our performance test we ran earlier, that when a query is warmed up, it responds a little quicker. The provider then, executes what it can, and then returns the data from the data store. Now, this is different in EF6, in the EF6 we had to completely run all of this statement on the server. With EF Core, we can have mixed client and server evaluation. And we'll cover those details later. The data returned is then converted to entities.
Now, the way this works in SQL server is very simple. An ADO.NET DataReader is created and entities are materialized and then, added to the DbSet collection. Now, when do queries execute? The LINQ itself is just an in-memory representation of what you would like to do or ask the database for. Nothing is executed when you're just building up a query. This allows you to build up a query through multiple steps, for example, maybe you have some conditional WHERE clauses or you want to change the sort order.
You can just build up the query, and then execute it when one of the following happens. You iterate the results in a loop. You bind the results to a user interface. Or when you call either ToList, ToArray, Single, or First. So this allows you to build up queries without adding a whole bunch of chattiness. It's not going to keep querying the database every time you change your query, it only executes when you want it to.
So let's see this in action. In the DataAccessLayerTests project under QueryingData, LinqExecution is a file called LinqExecutionTests. Now I want to explain a little bit what's going on here. So, we're implementing Idisposable which means that before every test, the constructor fires and I'm creating a new instance of the AventureWorksContext. Dispose fires after every test completes, so I am cleaning the slate, if you will.
I am making sure that we don't have anything left in memory, we've probably disposed of the context so that every test has a totally clean context. Nothing is being tracked and nothing that could pollute the tests. So I have two tests here. First one is called LinqNeverExecutesWithoutIteratingValues and what I'm going to do is I'm just going to write a very simple query and that query is just going to be using the name of the product DbSet, which is an Iqueryable.
And if we run this test right now, it will never execute against the database. And I'm not going to ask you to take my word for it. Let's go ahead and in SQL Server Management Studio, Go to Tools, SQL Server Profiler, and we connect, it's going to ask us for the Trace properties, we can just do the default, that's fine. We'll hit Run, let's resize this, then I'm going to pause it right now.
Go back into my test, let's add another statement in here. Just so we can have a breakpoint set. And we'll break it right here and here, and just for expedience sake, I'm going to run through ReSharper. I'm going to debug into this test, now the reason I paused the SQL Server Profiler is it's creating a DbContext and that's a little bit chatty. We'll talk about that in the configuration section later in this course.
Alright, so let's take a look at Profiler, go ahead and run it now. Let's clear out the entries that we have. Go back into our test, F10, go back into Profiler and nothing happened. Just to prove it, we're going to continue the test the rest of the way. Look at Profiler, nothing happened. Okay, we're going to pause Profiler again, go back into our test, we can go ahead and remove these breakpoints.
Let's go ahead and do the same thing but then, we're going to declare another variable here, results = query.ToList and we'll just write an Assert, results.Count>1 make sure you're actually pulling back data.
And I'm going to debug this as well. So we hit Debug and we hit our first breakpoint. Go back in to Profiler, let's run it. Once again, clear out that information. So we built a query, look at Profiler, still nothing. This next line is calling ToList, now we go back in to Profiler, what we see is our select statement that's generated pulling back all the records and when we look in the code, we see 504 results.
- Entity Framework Core components and projects
- Working with scaffolded files
- Testing with xUnit
- Viewing generated SQL
- Composing queries
- Sorting and filtering results
- Working with aggregates
- Loading related data
- Logging and tracking
- Mapping functions
- Generics and delegates
- Checking concurrency
- Resiliency and transactions