How to Update and Delete Multiple Rows in Entity Framework Core

There are scenarios where we need to update or delete multiple rows using Entity Framework.

Normally, we have to retrieve all records from the database first before we can update or delete them. This leads to multiple roundtrips to the database, which impacts performance.

However, in .NET 8, we can update or delete multiple rows without retrieving those records first from the database. It can be done by using the ExcecuteUpdate and ExecuteDelete extensions methods.

Updating Multiple Rows

We can use the ExecuteUpdate or ExecuteUpdateAsync method to update database rows for the entity instance that matches the LINQ query.

context.Products
    .Where(x => x.Category == "Coffee" && x.Price < 10)
    .ExecuteUpdate(x => x.SetProperty(p => p.Category, "Cheap Coffee"));

The code above is equivalent to this SQL:

UPDATE Products
SET Category = 'Cheap Coffee'
WHERE Category = 'Coffee' AND Price < 10

We can update multiple columns by chaining the SetProperty method:

context.Products
    .Where(x => x.Category == "Coffee" && x.Price < 10)
    .ExecuteUpdate(x => x.SetProperty(p => p.Category, "Cheap Coffee")
    .SetProperty(p => p.Price, 10));

And it will be translated to this SQL:

UPDATE Products
SET Category = 'Cheap Coffee', Price = 10
WHERE Category = 'Coffee' AND Price < 10

We can also use the async version as well:

await context.Products
    .Where(x => x.Category == "Coffee" && x.Price < 10)
    .ExecuteUpdateAsync(x => x.SetProperty(p => p.Category, "Cheap Coffee"), 
    cancellationToken);

Deleting Multiple Rows

We can use ExecuteDelete or ExecuteDeleteAsync method to delete database rows for the entity instance that matches the LINQ query.

context.Product
    .Where(x => x.Category == "Coffee" && Price == 0)
    .ExecuteDelete();

The code above is equivalent to this SQL:

DELETE FROM Products
WHERE Category = 'Coffee' AND Price = 0

We can also use the async version as well:

await context.Product
    .Where(x => x.Category == "Coffee" && Price == 0)
    .ExecuteDeleteAsync(cancellationToken);

Database Transaction

The ExecuteUpdate and ExecuteDelete method will be executed immediately against the database without needing to call the SaveChanges method.

If we have multiple database commands and want to encapsulate them into a single transaction, we need to create the transaction manually:

// create new transaction
using var transaction = await context.Database.BeginTransactionAsync(cancellationToken);

try
{
    // insert new data using standard approach
    var newProduct = Product
    {
        Name = "Caramel Java Chip",
        Category = "Coffee"
        Price = 50
    };
    context.Add(newProduct);
    context.SaveChangesAsync(cancellationToken);

    // update using ExecuteUpdate
    await context.Products
        .Where(x => x.Category == "Coffee" && x.Price < 10)
        .ExecuteUpdateAsync(x => x.SetProperty(p => p.Category, "Cheap Coffee"), 
        cancellationToken);

    // delete using ExecuteDelete
    await context.Product.Where(x => x.Category == "Coffee" && Price == 0)
        .ExecuteDeleteAsync(cancellationToken);

    // commit transaction
    await transaction.CommitAsync(cancellationToken);
}
catch (Exception)
{
    // rollback if error
    await transaction.RollbackAsync(cancellationToken);
    throw;
}

For operations involving large datasets, using ExecuteUpdate or ExecuteDelete can produce a better performance.

Thank you for reading 👍