Elevate your Entity Framework Core strategies with advanced techniques in bulk updates and deletions. Our comprehensive guide on ExecuteUpdate and ExecuteDelete empowers developers to refine database interactions for maximum efficiency and performance.
In the evolving landscape of Entity Framework Core (EF Core), the processes of updating and deleting entities remain central to database operations. As EF Core progresses with each version, these operations are refined for efficiency and ease of use.
This article aims to shed light on two significant enhancements in the latest EF Core: the ExecuteUpdate and ExecuteDelete methods. Introduced to streamline batch update and delete operations, these methods represent a leap in how developers interact with data, emphasizing performance and simplicity.
Understanding ExecuteUpdate and ExecuteDelete in EFCore
ExecuteUpdate and ExecuteDelete were introduced in EF Core 6.0 as part of its continued commitment to improving data manipulation capabilities. These methods were designed to address the performance concerns associated with large-scale data operations.
Previously, developers had to rely on less efficient ways to update or delete multiple records, which often led to performance bottlenecks. With these new methods, EF Core provides a direct way to execute update and delete operations in the database, bypassing the need for retrieving and processing records in memory.
While both ExecuteUpdate and ExecuteDelete offer similar improvements in EF Core, their internal behavior and implementation are alike. For the sake of brevity and focus, this article will concentrate on ExecuteUpdate.
The understanding and concepts derived from this method are easily transferable to ExecuteDelete, providing a comprehensive view of their capabilities.
Consider the Goat entity, which has three properties: Id, Name, Age, and LastUpdateDateTime. In C#, the class definition would look like this:
public class Goat
{
public int Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public DateTime LastUpdateDateTime { get; set; }
}
To demonstrate ExecuteUpdate in action, let's start by setting up a connection to a SQLite database and creating the Goat table. This involves defining a DbContext and configuring it for SQLite:
public class GoatDbContext : DbContext
{
public DbSet<Goat> Goats { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlite("Data Source=goats.db");
}
}
The first example uses a traditional approach, iterating over a list of Goat instances and updating each one:
using (var context = new GoatDbContext())
var goats = context.Goats.ToList();
foreach (var goat in goats)
{
goat.Name = "XXX";
goat.LastUpdateDateTime = DateTime.UtcNow;
}
context.SaveChanges();
The second example leverages ExecuteUpdate:
context.Goats.ExecuteUpdate(b => b.SetProperty(g => g.Name, "XXX")
.SetProperty(g => g.LastUpdateDateTime, DateTime.UtcNow));In the first example, each Goat entity is loaded into memory, modified, and then saved back to the database. This process is resource-intensive, especially with a large dataset.
To give you an idea, during a mass modification, EFCore will produce the following query for each modified line. If you modify 1000 lines, 1000 SQL queries will be executed.
-- First update
UPDATE "Goats" SET "LastUpdateDateTime" = @p0, "Name" = @p1
WHERE "Id" = @p2
RETURNING 1;
-- Second update
UPDATE "Goats" SET "LastUpdateDateTime" = @p0, "Name" = @p1
WHERE "Id" = @p2
RETURNING 1;
-- And so on
In contrast, the ExecuteUpdate method directly translates the update operation into SQL, executing it at the database level without loading entities into memory. This results in significant performance gains, particularly noticeable in large-scale operations.
-- One query to update all lines
UPDATE "Goats" AS "g"
SET "LastUpdateDateTime" = rtrim(rtrim(strftime('%Y-%m-%d %H:%M:%f', 'now'), '0'), '.'),
"Name" = 'XXX'Advanced Topics in Execute
Affected Rows
EF Core's ExecuteUpdate method returns an integer representing the number of rows affected. This can be useful for logging or validation purposes. Here's an example:
int affectedRows = context.Goats.ExecuteUpdate(b => b.SetProperty(g => g.Name, "NewName"));
Console.WriteLine($"{affectedRows} rows updated.");
Asynchronous Operations
EF Core also provides asynchronous versions of these methods, enhancing performance in scenarios that benefit from non-blocking database operations. Here's a combined example for both ExecuteUpdateAsync and ExecuteDeleteAsync:
await context.Goats.ExecuteUpdateAsync(b => b.SetProperty(g => g.Name, "AsyncUpdatedName"));
await context.Goats.Where(g => g.Name == "AsyncUpdatedName").ExecuteDeleteAsync();Tracking Management
One of the nuanced aspects of ExecuteUpdate in EF Core is its interaction with the ORM's tracking mechanism. Unlike traditional update methods where entities are tracked for changes, ExecuteUpdate operates outside this tracking scope.
To illustrate, let's consider an example where we first use ExecuteUpdate and then attempt to modify the same entities using the standard tracking mechanism:
var goat = context.Goats.FirstOrDefault();
// Here goat.Age == 10
// ExecuteUpdate without tracking
context.Goats.ExecuteUpdate(b => b.SetProperty(g => g.Age, 15));
// goat.Name still
goat.Age += 2;
context.SaveChanges(); // This won't affect entities previously updated by ExecuteUpdate
// goat.Age will be 12 and not 17 in databaseIn this scenario, the changes made by ExecuteUpdate are not tracked by EF Core's change tracker. Therefore, subsequent modifications to the same entities are not aware of the previous ExecuteUpdate operations. In the example, since the value is retrieved before the ExecuteUpdate is executed, the age is not associated with the Goat variable, since there is no tracking. Only the addition of the base value and the increment gives the final result, which is 12.
-- Microsoft documentation
Atomicity in transaction
An important consideration when using ExecuteUpdate in EF Core is ensuring atomicity, especially in operations involving multiple data changes. Atomicity guarantees that a series of database operations either all succeed or all fail, maintaining data integrity.
The most reliable way to ensure atomicity with ExecuteUpdate is through the use of database transactions. Transactions encapsulate multiple operations into a single unit, which is either entirely committed or entirely rolled back. This ensures that partial updates do not occur, preserving the consistency of your database.
No ExecuteAdd
The absence of an ExecuteAdd method in EF Core is primarily due to the nature of insert operations. Unlike updates and deletes, inserts typically require individual handling of each entity for identity generation, relationships, and other constraints.
EF Core's design philosophy emphasizes the importance of context in insert operations, which doesn't lend itself well to a bulk operation approach akin to ExecuteUpdate or ExecuteDelete.
Limitations in TPC and TPT
ExecuteUpdate and ExecuteDelete are not applicable for entities that are not leaf nodes in Table-per-Concrete Type (TPC) and Table-per-Type (TPT) hierarchies in EF Core. This limitation arises from the complexity of generating SQL statements for entities in a hierarchy.
In TPC and TPT, each type in the hierarchy can have its own table structure, making it challenging to create a single SQL statement that can universally apply updates or deletes across the hierarchy.
Therefore, these methods are constrained to entities that represent leaf nodes in these inheritance models, where such SQL generation is straightforward and unambiguous.
Conclusion
The ExecuteUpdate method in EF Core represents a significant advancement in handling bulk data operations. By enabling direct database-level updates, it offers a more efficient and performant approach compared to traditional entity loading and modification methods. As EF Core continues to evolve, such enhancements play a crucial role in simplifying and optimizing data manipulation tasks for developers.
To go further:
Have a goat day 🐐
Join the conversation.