Database Queries in Entity Framework Core

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 the Include 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.