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.

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.

csharp
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:

sql
Executed DbCommand (0ms) [Parameters=[@p1='****', @p0='[email protected]' (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:

csharp
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:

sql
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:

csharp
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:

sql
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.

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:

csharp
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:

sql
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:

csharp
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:

sql
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!

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:

csharp
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:

1. Updating Book Prices
2. Updating Author row timestamp
3. Creating Price Change Records

Wrapping these operations in a transaction ensures that either all operations succeed or none do, thus maintaining database integrity.

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.

Hope you find this newsletter useful. See you next time.