Have you faced performance issues when performing mass updates or deletions in your EF Core applications?
EF Core offers efficient batch operations using ExecuteUpdate and ExecuteDelete methods, significantly enhancing performance.
These operations allow updating and deleting multiple entities in a single SQL query without retrieving them from the database.
In this blog post I will show you how to correctly use ExecuteUpdate and ExecuteDelete methods in EF Core to ensure data consistency.
On my website: antondevtips.com I share .NET and Architecture best practices.
Subscribe to my newsletter to improve your .NET skills.
Download the source code for this newsletter for free.
Default Approach to Updating And Deleting Entities
First, let's explore how updating and deletion of entities works in EF Core.
The default approach involves loading entities into the EF Core Change Tracker that holds them in memory.
This tracking mechanism is essential for EF Core to know which entities must be inserted, updated, or deleted in the database.
var users = await dbContext.Users.ToListAsync();
After querying users from the database, all entities are automatically added to the Change Tracker.
When updating the users - EF Core will compare the current users collection with the saved collection stored in Change Tracker.
EF Core will use the comparison result to decide what SQL commands to generate to update entities in the database:
Executed DbCommand (0ms) [Parameters=[@p1='****', @p0='test@mail.com' (Nullable = false) (Size = 13)], CommandType='Text', CommandTimeout='30']
UPDATE "users" SET "email" = @p0
WHERE "id" = @p1
RETURNING 1;
Let's explore an example of updating books' price for a given Author:
public sealed record UpdateBooksPriceRequest(decimal Delta);
app.MapPut("/authors/{authorId:guid}/books/update-price",
async (Guid authorId,
UpdateBooksPriceRequest request,
ApplicationDbContext dbContext) =>
{
var books = await dbContext.Books.Where(b => b.AuthorId == authorId).ToListAsync();
foreach (var book in books)
{
book.Price += request.Delta;
book.UpdatedAtUtc = DateTime.UtcNow;
}
await dbContext.SaveChangesAsync();
return Results.Ok(new { updated = books.Count });
});
This approach is straightforward: load entities from the database, update needed properties, and EF Core will figure out what SQL statements to generate to update entities:
UPDATE devtips_batch_operations.books SET price = @p0, updated_at_utc = @p1
WHERE id = @p2;
UPDATE devtips_batch_operations.books SET price = @p3, updated_at_utc = @p4
WHERE id = @p5;
UPDATE devtips_batch_operations.books SET price = @p6, updated_at_utc = @p7
WHERE id = @p8;
Let's explore another example of deleting multiple books for a given author:
app.MapDelete("/authors/{authorId:guid}/books",
async (Guid authorId, ApplicationDbContext dbContext) =>
{
var booksToDelete = await dbContext.Books
.Where(b => b.AuthorId == authorId)
.ToListAsync();
if (booksToDelete.Count == 0)
{
return Results.NotFound("No books found for the given author.");
}
dbContext.Books.RemoveRange(booksToDelete);
await dbContext.SaveChangesAsync();
return Results.Ok(new { deletedCount = booksToDelete.Count });
});
This approach is straightforward: load entities from the database, call RemoveRange method, and EF Core will figure out what SQL statements to generate to delete entities:
DELETE FROM devtips_batch_operations.books
WHERE id = @p0;
DELETE FROM devtips_batch_operations.books
WHERE id = @p1;
DELETE FROM devtips_batch_operations.books
WHERE id = @p2;
As you can see, both operations generate individual SQL commands for each updated and deleted entity, which can be inefficient.
While simple and effective for small datasets, this approach can be inefficient for medium and large numbers of records.
Let's explore a more efficient solution.
Using ExecuteUpdate and ExecuteDelete Methods
EF Core 7 introduced ExecuteUpdate and ExecuteDelete methods for batch operations.
These methods bypath the Change Tracker and allow to perform updates and deletions directly in the database with a single SQL statement.
These methods have the following advantages:
- Remove the overhead of loading entities from the database into ChangeTracker
- Update and delete operations are executed as a single SQL command, making such queries very efficient
Let's explore how we can rewrite the previous examples using these methods.
This is how you can update books' price with ExecuteUpdate:
app.MapPut("/authors/{authorId:guid}/books/batch-update-price",
async (Guid authorId,
UpdateBooksPriceRequest request,
ApplicationDbContext dbContext) =>
{
var updatedCount = await dbContext.Books
.Where(b => b.AuthorId == authorId)
.ExecuteUpdateAsync(s => s
.SetProperty(b => b.Price, u => u.Price + request.Delta)
.SetProperty(b => b.UpdatedAtUtc, DateTime.UtcNow));
return Results.Ok(new { updated = updatedCount });
});
First we filter books by a given Author identifier and update the needed properties by calling a SetProperty method.
This generates a single SQL command:
UPDATE devtips_batch_operations.books AS b
SET updated_at_utc = now(),
price = b.price + @__request_Delta_1
WHERE b.author_id = @__authorId_0
Let's explore a Book deletion example:
app.MapDelete("/authors/{authorId:guid}/books/batch",
async (Guid authorId, ApplicationDbContext context) =>
{
var deletedCount = await dbContext.Books
.Where(b => b.AuthorId == authorId)
.ExecuteDeleteAsync();
return Results.Ok(new { deleted = deletedCount });
});
This also generates a single SQL command:
DELETE FROM devtips_batch_operations.books AS b
WHERE b.author_id = @__authorId_0
These methods are significantly more efficient for larger modifications.
These methods can be beneficial even when updating or deleting a single entity.
You execute a single SQL command instead of two separate operations (loading and then updating or deleting).
And if you have multiple entities, you need to send 1 + N requests to the database.
This can slow your application significantly.
But keep in mind that ExecuteUpdate and ExecuteDelete methods have one major caveat.
They are detached from EF Core's Change Tracker.
If you call SaveChanges afterward, and it fails, changes made via ExecuteUpdate and ExecuteDelete won't be reverted.
Let's explore how to fix this problem!
How to Ensure Data Consistency with ExecuteUpdate and ExecuteDelete Methods
You need to ensure that data is consistent when executing multiple batch operations, or executing a batch operation together with SaveChanges.
You need to wrap all database commands manually in a transaction.
Let's explore an example:
app.MapPut("/authors/{authorId:guid}/books/multi-update",
async(Guid authorId,
UpdateBooksPriceRequest request,
ApplicationDbContext dbContext) =>
{
await using var transaction = await dbContext.Database.BeginTransactionAsync();
try
{
var authorBooks = await dbContext.Books
.Where(b => b.AuthorId == authorId)
.Select(x => new { x.Id, x.Price })
.ToListAsync();
var updatedCount = await dbContext.Books
.Where(b => b.AuthorId == authorId)
.ExecuteUpdateAsync(s => s
.SetProperty(b => b.Price, u => u.Price + request.Delta)
.SetProperty(b => b.UpdatedAtUtc, DateTime.UtcNow));
await dbContext.Authors
.Where(b => b.Id == authorId)
.ExecuteUpdateAsync(s => s
.SetProperty(b => b.UpdatedAtUtc, DateTime.UtcNow));
var priceRecords = authorBooks.Select(x => new PriceRecord
{
Id = Guid.NewGuid(),
BookId = x.Id,
OldPrice = x.Price,
NewPrice = x.Price + request.Delta,
CreatedAtUtc = DateTime.UtcNow
}).ToList();
dbContext.PriceRecords.AddRange(priceRecords);
await dbContext.SaveChangesAsync();
await transaction.CommitAsync();
return Results.Ok(new { updated = updatedCount });
}
catch (Exception)
{
await transaction.RollbackAsync();
return Results.BadRequest("Error updating books");
}
});
In this API endpoint, there are 3 update operations:
- Updating
BookPrices - Updating
Authorrow timestamp - Creating Price Change Records
Wrapping these operations in a transaction ensures that either all operations succeed or none do, thus maintaining database integrity.
Summary
ExecuteUpdate and ExecuteDelete methods significantly boost EF Core performance for batch operations.
However, to avoid data consistency issues, always wrap these methods within manual transactions if you combine them with other operations.
This ensures robust, fast and consistent database state management.
On my website: antondevtips.com I share .NET and Architecture best practices.
Subscribe to my newsletter to improve your .NET skills.
Download the source code for this newsletter for free.
Top comments (0)