Data Access in ASP.NET Core (Part 3: Tips)

In Chapter 6, we learned how to manage relationships between entities using the EF Core Fluent API. We introduced three types of relationships: one-to-one, one-to-many, and many-to-many. We also learned how to perform CRUD operations on related entities. With the knowledge we gained from Chapter 6, we can now build a simple data access layer for most web API applications. However, there are still some scenarios that we need to handle properly. For example, how do we improve the performance of data access? And what should we do if there are concurrency conflicts?

In this chapter, we will cover some advanced topics related to data access in ASP.NET Core, including DbContext pooling, performance optimization, raw SQL queries, and concurrency conflicts. We will also discuss some tips and tricks that can help you write better code.

We will cover the following topics:

• DbContext pooling
• Tracking versus no-tracking queries
• IQueryable...

Technical requirements

The code examples in this chapter can be found at https://github.com/PacktPublishing/Web-API-Development-with-ASP.NET-Core-8. You can use VS 2022 or VS Code to open the solutions.

Understanding DbContext pooling

In the previous chapter, we learned how to register the DbContext instance as a scoped service in the DI container using the AddDbContext() extension method. By default, a new DbContext instance is created for each request, which is generally not a problem since it is a lightweight object that does not consume many resources. However, in a high-throughput application, the cost of setting up various internal services and objects for each DbContext instance can add up. To address this, EF Core provides a feature called DbContext pooling, which allows the DbContext instance to be reused across multiple requests.

To enable DbContext pooling, you can replace the AddDbContext() method with the AddDbContextPool() method. This resets the state of the DbContext instance when it is disposed of, stores it in a pool, and reuses it when a new request comes in. By reducing the cost of setting up the DbContext instance, DbContext pooling can significantly improve...

Understanding the difference between tracking versus no-tracking queries

In this section, we will discuss the difference between tracking and no-tracking queries. What are tracking queries and no-tracking queries? Let us start from the beginning!

In the early days of .NET, the term SqlHelper was popular to refer to a static class that provided a set of methods to execute SQL queries. While SqlHelper simplified the process of executing SQL queries, developers still had to manage connection and transaction objects, write boilerplate code to map results to model objects, and work directly with the database.

ORM frameworks such as EF Core were created to solve these problems. They not only simplify the process of executing SQL queries and mapping the results to model objects but also provide the ability to track changes made to the entities returned by queries. When changes are saved, EF Core generates the appropriate SQL queries to update the database. This is called tracking and...

Understanding the difference between IQueryable and IEnumerable

When working with EF Core, you have two interfaces available to query the database: IQueryable and IEnumerable. Although these interfaces may seem similar at first glance, they have important differences that can affect your application’s performance. In this section, we will discuss the differences between IQueryable and IEnumerable, how they work, and when to use each of them.

You might be familiar with the IEnumerable interface. The IEnumerable interface is a standard .NET interface that is used to represent a collection of objects. It is used to iterate through the collection. Many .NET collections implement the IEnumerable interface, such as List, Array, Dictionary, and so on. The IEnumerable interface has a single method called GetEnumerator, which returns an IEnumerator object. The IEnumerator object is used to iterate through the collection.

The first difference between IQueryable and IEnumerable is...

Client evaluation versus server evaluation

In this section, we will discuss the difference between client evaluation and server evaluation. In the old versions of EF Core (earlier than EF Core 3.0), the wrong usage of LINQ queries that have client evaluation can cause significant performance issues. Let's see what client evaluation and server evaluation are.

When we use EF Core to query data from the database, we can just write LINQ queries, and EF Core will translate the LINQ queries into SQL queries and execute them against the database. However, sometimes, the LINQ operation must be executed on the client side. Check the following code in the SearchInvoices action method in the InvoicesController class:

var list = await context.Invoices    .Where(x => x.ContactName.Contains(search) || x.InvoiceNumber.Contains(search))
    .ToListAsync();

When we use the Contains() method, EF Core can translate the LINQ query into the following...

Using raw SQL queries

Although EF Core can translate most LINQ queries into SQL queries, which is very convenient, sometimes we need to write raw SQL queries if the required query cannot be written in LINQ, or the generated SQL query is not efficient. In this section, we will explore how to use raw SQL queries in EF Core.

EF Core provides several methods to execute raw SQL queries:

• FromSql()
• FromSqlRaw()
• SqlQuery()
• SqlQueryRaw()
• ExecuteSql()
• ExecuteSqlRaw()

When we execute raw SQL queries, we must be careful to avoid SQL injection attacks. Let's see when we should use raw SQL queries and how to use them properly. You can download the sample code from the /samples/chapter7/EfCoreDemo folder in the chapter's GitHub repository.

FromSql() and FromSqlRaw()

We can use the FromSql() method to create a LINQ query based on an interpolated string. The FromSql() method is available in EF Core 7.0 and later versions. There is a similar method...

Using bulk operations

In this section, we will explore how to effectively update/delete data using EF Core. EF Core 7.0 or later offers the ability of bulk operations, which are easy to use and can improve the performance of update/delete operations. To take advantage of this feature, ensure you are using the most recent version of EF Core.

As we mentioned in the previous section, EF Core tracks the changes in entities. To update an entity, normally, we need to load the entity from the database, update the entity properties, and then call the SaveChanges() method to save the changes to the database. This is a very common scenario. Deleting an entity is similar. However, if we want to update or delete a large number of entities, it is not efficient to load the entities one by one and then update or delete them. For these scenarios, it is not required to track the changes in the entities. So, it would be better to use the bulk operations feature to update or delete data.

We can...

Understanding concurrency conflicts

An API endpoint can be called by multiple clients at the same time. If the endpoint updates data, the data may be updated by another client before the current client completes the update. When the same entity is updated by multiple clients, it can cause a concurrency conflict, which may result in data loss or inconsistency, or even cause data corruption. In this section, we will discuss how to handle concurrency conflicts in EF Core. You can download the sample project ConcurrencyConflictDemo from the /samples/chapter7/ConcurrencyConflictDemo folder in the chapter's GitHub repository.

There are two ways to handle concurrency conflicts:

• Pessimistic concurrency control: This uses database locks to prevent multiple clients from updating the same entity at the same time. When a client tries to update an entity, it will first acquire a lock on the entity. If the lock is acquired successfully, only this client can update the entity, and...

Reverse engineering

So far, we have learned how to use EF Core to create a database schema from the entity classes. This is called code-first. However, sometimes we need to work with an existing database. In this case, we need to create the entity classes and DbContext from the existing database schema. This is called database-first or reverse engineering. In this section, we will discuss how to use EF Core to reverse engineer the entity classes and DbContext from an existing database schema. This is useful when we want to migrate an existing application to EF Core.

Let's use the EfCoreRelationshipsDemoDb database as an example. If you have not created this database, please follow the steps in Chapter 6 to create it. The sample code is located at the /samples/chapter7/EfCoreReverseEngineeringDemo folder in the chapter's GitHub repository.

1. First, let us create a new web API project. Run the following command in the terminal:

   dotnet new webapi -n EfCoreReverseEngineeringDemo...

Other ORM frameworks

In addition to EF Core, there are numerous other ORM frameworks available for .NET. Some of the most popular include the following:

• Dapper (https://dapperlib.github.io/Dapper/): Dapper is a micro-ORM framework that is designed to be fast and lightweight. Dapper does not support change tracking, but it is easy to use, and it is very fast. As the official documentation says, “Dapper’s simplicity means that many features that ORMs ship with are stripped out. It worries about the 95% scenario and gives you the tools you need most of the time. It doesn’t attempt to solve every problem.” The performance is one of the most important features of Dapper. Maybe it is not fair to compare Dapper’s performance with EF Core, because EF Core provides many more features than Dapper. If you are looking for a simple ORM framework that is fast and easy to use, Dapper is a good choice. In some projects, Dapper is used with EF Core to provide...

Summary

In this chapter, we delved into some advanced topics of Entity Framework. We started by exploring how to improve the performance of our application by using DbContext pooling and no-tracking queries. We then learned how to execute raw SQL queries safely and efficiently using parameterized queries, and how to leverage the new bulk operations feature in EF Core for faster data manipulation.

Next, we looked at how to handle concurrency scenarios using optimistic concurrency control, which allows multiple users to access and modify the same data simultaneously without conflicts. We also covered reverse engineering, a technique for generating entity classes and DbContext classes from an existing database schema, which can save time and effort in creating a data access layer.

To broaden our horizons beyond EF Core, we briefly introduced some other popular ORM frameworks, such as Dapper, NHibernate, and PetaPoco, and discussed their strengths and weaknesses. By the end of this...