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 👍