In this chapter, we are going to talk about Queries in Entity Framework Core. In previous articles, we have already created and prepared the database with the required configuration. So, in this one, we are going to populate our additional tables with data and start writing some queries.
We are going to learn how to access the database via EF Core and how to read the data from it by using different query approaches.
You can download the source code for this article on our GitHub repository.
To see all the basic instructions and complete navigation for this series, visit Entity Framework Core with ASP.NET Core Tutorial.
Before we start with the next section of this article, we suggest downloading the starting project, adjusting the connection string, and running it. By doing so, it will seed all the required data which we need for this article.
Feel free to continue with the same project until the end of this article.
Understanding Queries in Entity Framework Core
Now, we can start querying data from the database by using the EF Core. Every query consists of three main parts:
- Connection to the database via the ApplicationContext’s DbSet property
- Series of LINQ and/or EF Core commands
- Query execution
The second part is used often, but sometimes it can be skipped if we want to return all the rows from a table we are connecting to via the DbSet property.
So, to explain the query basics, we are going to use the Values
controller, as we did in the first part of the series and only the Get action for the sake of simplicity. We are going to focus on the EF Core’s logic, not on Web API overall.
If you want to learn more about ASP.NET Core Web API, we strongly recommend reading our ASP.NET Core tutorial. Therefore let’s inject our context object in the Values constructor and write a first query in the Get action:
[HttpGet] public IActionResult Get() { var students = _context.Students .Where(s => s.Age > 25) .ToList(); return Ok(students); }
From this query, we can see all the mentioned parts. The “_context.Students
” is the first part where we access the Student
table in the database via the DbSet<Student> Students
property. The “.Where(s => s.Age > 25)
“ is a second part of the query where we use a LINQ command to select only required rows. Finally, we have ToList()
method which executes this query.
TIP: When we write only read-only queries in Entity Framework Core(the result of the query won’t be used for any additional database modification), we should always add AsNoTracking method to speed up the execution.
In the next article, we are going to talk about how EF Core modifies data in the database and track changes in the loaded entity. For now, just know that EF Core won’t track changes (when we apply AsNoTracking) on the loaded entity which will speed up the query execution:
[HttpGet] public IActionResult Get() { var students = _context.Students .AsNoTracking() .Where(s => s.Age > 25) .ToList(); return Ok(students); }
Different Ways of Building Relational Queries
There are different approaches to retrieve our data:
- Eager loading
- Explicit Loading
- Select (Projection) loading
- Lazy loading
We are going to talk more about each of them in this article. It is important to know that EF Core will include relationships in the result only if explicitly asked for. So, it doesn’t matter if our Student
object has navigational properties because, with the query like the one we’ve written above, they won’t be included.
As a result of our query, the navigational property values are null:
Relational Database Queries with Eager Loading in EF Core
With the Eager Loading approach, EF Core includes the relationships in the query result. It does that by using two different methods Include()
and ThenInclude()
. In the next example, we are going to return only one student with all the related evaluations, to show how the Include()
method works:
var students = _context.Students .Include(e => e.Evaluations) .FirstOrDefault();
Before we send request to execute this query, we should install the Microsoft.AspNetCore.Mvc.NewtonsoftJson
library and modify the Startup.cs
class:
public void ConfigureServices(IServiceCollection services) { services.AddDbContext<ApplicationContext>(opts => opts.UseSqlServer(Configuration.GetConnectionString("sqlConnection"), options => options.MigrationsAssembly("EFCoreApp"))); services.AddControllers() .AddNewtonsoftJson(o => o.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore); }
This is the protection for the “Self-referencing loop” error while returning the result from our API (which does happen in real-world projects). You can use DTO objects, as we did in our ASP.NET Core Web API project, but this is a faster solution and serves the purpose (we are not saying this is a better approach, just as opposite).
Now, we should receive a required result after sending the request to http://localhost:5001/api/values
:
We can take a look in the console window to see how EF Core translates this query into the SQL command:
We can see that EF core selects the first student from a Student
table and then selects all the relational evaluations.
The important thing to know is that we can include all the entities in our queries via the Student
entity because it has relationships with other entities. That is the reason why we have only one DbSet property of type DbSet<Student>
in the ApplicationContext
class.
But if we want to write a separate query for other entities, Evaluation, for example, we have to add an additional DbSet<Evaluation> property.
ThenInclude
To additionally modify our query for including the second-level relationship properties, we can attach the ThenInclude
method right after the Include
method. So, with the Include
method, we are loading the first-level relationship properties and once we attach ThenInclude
, we can go even deeper into the relationship graph.
Having this in mind, let’s additionally include all the subjects for the selected student:
var students = _context.Students .Include(e => e.Evaluations) .Include(ss => ss.StudentSubjects) .ThenInclude(s => s.Subject) .FirstOrDefault();
The Student
entity doesn’t have a direct navigational property to the Subject
entity and therefore, we are including the first-level navigational property StudentSubjects
and then include the second-level navigational property Subject
:
We can go to any depth with the ThenInclude
method because if the relationship doesn’t exist, the query doesn’t fail it simply doesn’t return anything. This also applies to the Include
method.
Eager Loading Advantages and Disadvantages and Console Warnings
The advantage of this approach is that EF Core includes relational data, with Include or ThenInclude, in an efficient manner, using a minimum of database access (database roundtrips).
The downside of this approach is that it always loads all the data even though we don’t need some of them.
As we’ve seen, when we execute our query, EF Core logs the translated query into the console window. That is a great debugging feature provided by EF Core because we can always decide whether we have created an optimal query in our application by just looking at the translated result.
Explicit Loading in Entity Framework Core
With this approach Entity Framework Core explicitly loads relationships into the already loaded entity. So, let’s take a look at different ways to explicitly load relationships:
var student = _context.Students.FirstOrDefault(); _context.Entry(student) .Collection(e => e.Evaluations) .Load(); _context.Entry(student) .Collection(ss => ss.StudentSubjects) .Load(); foreach (var studentSubject in student.StudentSubjects) { _context.Entry(studentSubject) .Reference(s => s.Subject) .Load(); }
In this example, we’re loading the student entity first. Then we’re including all the evaluations related to the retrieved student. Additionally, we’re including all related subjects via the StudentSubjects
navigational property.
The important thing to notice is when we want to include a collection into the main entity, we have to use the Collection
method, but when we include a single entity as a navigational property, we have to use the Reference
method.
So, the student object contains ICollection<Evaluation>
and ICollection<StudentSubject>
properties and both are populated by using the Collection
method. On the other hand, the StudentSubject
entity contains a single reference towards the Subject
entity and therefore we are populating the Subject
property with the Reference
method.
Queries in Entity Framework Core with Explicit Loading
When working with Explicit Loading in Entity Framework Core, we have an additional command available. It allows the query to be applied to the relationship. So, instead of using the Load
method, as we did in the previous example, we are going to use the Query
method:
var student = _context.Students.FirstOrDefault(); var evaluationsCount = _context.Entry(student) .Collection(e => e.Evaluations) .Query() .Count(); var gradesPerStudent = _context.Entry(student) .Collection(e => e.Evaluations) .Query() .Select(e => e.Grade) .ToList();
The advantage of the Explicit Loading is that we can load a relationship on an entity class later when we really need it. Another benefit is that because we can separately load relationships if we have complex business logic. The relationship loading could be moved to another method or even a class, thus making the code easier to read and maintain.
The downside of this approach is that we have more database roundtrips to load all the required relationships. Thus making the query less efficient.
Select (Projection) Loading
This approach uses the Select
method to pick only the properties we need in our result. Let’s take a look at the following example:
var student = _context.Students .Select(s => new { s.Name, s.Age, NumberOfEvaluations = s.Evaluations.Count }) .ToList();
This way we project only the data that we want to return in a response. Of course, we don’t have to return an anonymous object as we did here. We can create our own DTO object and populate it in the projection query.
The advantage of this approach is that we can select the data we want to load, but the disadvantage is that we have to write code for every property we want to include in a result.
Lazy Loading in Entity Framework Core
Lazy Loading was introduced in EF Core 2.1 and we can use it to postpone the retrieval of data from the database until it is actually needed. This feature can help in some situations but it can degrade our application performance as well and this is the main reason for making it an opt-in feature in EF Core 2.1.
You can read more about this feature in the DataModelling section of the ASP.NET Core Web API with EF Core DB-First Approach article.
Client vs Server Evaluation
All the queries we’ve written so far are the ones that EF Core can translate to the SQL commands (as we’ve seen from the console window). But EF Core has a feature called Client vs Server Evaluation which allows us to include methods in our query that can’t be translated into the SQL commands. Those commands will be executed as soon as the data has been retrieved from the database.
For example, let’s imagine that we want to show a single student with evaluation explanations as a single string:
var student = _context.Students .Where(s => s.Name.Equals("John Doe")) .Select(s => new { s.Name, s.Age, Explanations = string.Join(",", s.Evaluations .Select(e => e.AdditionalExplanation)) }) .FirstOrDefault();
From EF Core 3.0, client evaluation is restricted to only happen on the top-level projection (essentially, the last call to Select()
).
And this is the query result:
Even though Client vs Server Evaluation allows us to write complex queries, we need to pay attention to the number of rows we return from the database. If we return 20 000 rows our method will run on every row on the client. That can be time-consuming in some cases.
Raw SQL Commands
EF Core has methods that we can use to write raw SQL commands to fetch the data from the database. These methods are very useful when:
- we can’t create our queries with the standard LINQ methods
- if we want to call a stored procedure
- if the translated LINQ query is not that efficient as we would like to be
FromSqlRaw Method
This method allows us to add raw sql commands to the EF Core queries:
var student = _context.Students .FromSqlRaw(@"SELECT * FROM Student WHERE Name = {0}", "John Doe") .FirstOrDefault();
We can also call stored procedures from a database:
var student = _context.Students .FromSqlRaw("EXECUTE dbo.MyCustomProcedure") .ToList();
The FromSqlRaw
method is a very useful method but it has some limitations:
- The column names in our result must match the column names that the properties are mapped to
- Our query must return data for all properties of the entity or query type
- The SQL query can’t contain relationships, but we can always combine
FromSqlRaw
with theInclude
method
So, if we want to include relationships to our query, we can do it like this:
var student = _context.Students .FromSqlRaw("SELECT * FROM Student WHERE Name = {0}", "John Doe") .Include(e => e.Evaluations) .FirstOrDefault();
ExecuteSqlRaw Method
The ExecuteSqlRaw
method allows us to execute SQL commands like Update, Insert, Delete. Let’s see how we can use it:
var rowsAffected = _context.Database .ExecuteSqlRaw( @"UPDATE Student SET Age = {0} WHERE Name = {1}", 29, "Mike Miles"); return Ok(new { RowsAffected = rowsAffected});
This command executes required command and returns a number of affected rows. This works the same whether we Update, Insert or Delete rows from the database. In this example, the ExecuteSqlRaw
will return 1 as a result because only one row is updated:
It is quite important to notice that we are using the Database property to call this method, while in a previous example we had to use the Student
property for the FromSqlRaw
method.
Another important thing to notice is that we are using the string interpolation feature for queries in both FromSqlRaw
and ExecuteSqlRaw
methods because it allows us to place a variable name in the query string, which EF Core then checks and turns into parameters. Those parameters will be checked to prevent SQL injection attacks. We shouldn’t use string interpolation outside of the EF Core’s raw query methods because then we will lose the Sql injection attack detections.
Reload Method
If we have an entity which is already loaded and then we use the ExecuteSqlRaw
method to make some changes to that entity in the database, our loaded entity is going to be outdated for sure. Let’s modify our previous example:
var studentForUpdate = _context.Students .FirstOrDefault(s => s.Name.Equals("Mike Miles")); var age = 28; var rowsAffected = _context.Database .ExecuteSqlRaw(@"UPDATE Student SET Age = {0} WHERE Name = {1}", age, studentForUpdate.Name); return Ok(new { RowsAffected = rowsAffected});
As soon as we execute this query, the Age
column will change to 28, but let’s see what is going to happen with the studentForUpdate
loaded object:
There it is, the Age
property hasn’t changed even though it has been changed in the database. Of course, this is the expected behavior.
So now, the question is: „What if we want it to change after the execution of the ExecuteSqlRaw method?“.
Well, to accomplish that, we have to use the Reload
method:
var rowsAffected = _context.Database .ExecuteSqlRaw(@"UPDATE Student SET Age = {0} WHERE Name = {1}", age, studentForUpdate.Name); _context.Entry(studentForUpdate).Reload();
Now, when we execute the code again:
The age property, of the loaded entity, is changed.
Conclusion
We did a great job here. We’ve covered a lot of topics and learned a lot about queries in Entity Framework Core.
So, to summarize, we have learned:
- How queries work in EF Core
- About different query types and how to use each of them
- The way to use Raw SQL commands with different EF Core methods
In the next article, we are going to learn about EF Core actions that will modify the data in the database.
Very good article as always. Thank you Marinko Spasojevic
Thanks a lot. It is always great to hear that.