Perform Bulk Updates and Deletes in Entity Framework Core
If you are keeping an eye on the new features of EF Core 7, you are probably aware that it includes what is known as Bulk Updates. To that end, this article discusses what bulk updates are and how to use them in your ASP.NET Core applications.
Typically for inserting, updating, and deleting entities we use EF Core methods such as Add(), Update() and Remove(), followed by a call to SaveChanges(). This approach works well in most of the cases and involves change tracking. However, at times we may want to execute UPDATE and DELETE queries directly against the database bypassing the change tracking mechanism. For example, this might be desirable when you are updating or deleting many rows of a table based on a common criteria.
To take care of such requirements EF Core 7 has added two extension methods namely ExecuteUpdate() and ExecuteDelete(). These methods are also available in their async form - ExecuteUpdateAsync() and ExecuteDeleteAsync(). These extension methods have been added to IQueryable :
public static int ExecuteUpdate<TSource>
(this IQueryable<TSource> source,
Expression<Func<SetPropertyCalls<TSource>,
SetPropertyCalls<TSource>>> setPropertyCalls)
{
return source.Provider.Execute<int>
(Expression.Call(ExecuteUpdateMethodInfo
.MakeGenericMethod(typeof(TSource)),
source.Expression, setPropertyCalls));
}
public static int ExecuteDelete<TSource>
(this IQueryable<TSource> source)
{
return source.Provider.Execute<int>
(Expression.Call(ExecuteDeleteMethodInfo
.MakeGenericMethod(typeof(TSource)),
source.Expression));
}
That means you can call them on any LINQ query to get the job done. Let's see how.
Begin by creating a new ASP.NET Core Razor Pages application. Then add NuGet package for EF Core 7 using NuGet Package Manager dialog.
Then open appsettings.json and specify the database connection string for Northwind database.
"ConnectionStrings": {
"Northwind": "data source=.;initial catalog=Northwind;
integrated security=true;Encrypt=False"
}
Next, add Customer entity class and AppDbContext class as shown below:
public class Customer
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string Country { get; set; }
}
public class AppDbContext:DbContext
{
public DbSet<Customer> Customers { get; set; }
public AppDbContext(DbContextOptions
<AppDbContext> options):base(options)
{
}
}
Then open the Index.cshtml.cs page model file and add the following code in it.
public class IndexModel : PageModel
{
private readonly ILogger<IndexModel> _logger;
private readonly AppDbContext db;
public List<Customer> Items { get; set; }
public IndexModel(AppDbContext db, ILogger<IndexModel> logger)
{
this.db = db;
_logger = logger;
}
public void OnGet()
{
Items = db.Customers.ToList();
}
...
...
}
Notice the code shown in bold letters. We inject AppDbContext in the constructor and also set the Items property to all the Customer entities in the OnGet() page handler.
Then add two empty POST page handlers namely OnPostUpdate() and OnPostDelete(). These page handlers will be called by two buttons on the UI (more on that shortly).
public void OnPostUpdate()
{
}
public void OnPostDelete()
{
}
We will write code inside these page handlers in a minute.
Now open Index.cshtml razor page file and add the following markup and code.
<form method="post">
<button type="submit"
asp-page-handler="Update">
Bulk Update</button>
<button type="submit"
asp-page-handler="Delete">
Bulk Delete</button>
<br /><br />
<table border="1">
@foreach(var item in Model.Items)
{
<tr>
<td>@item.CustomerID</td>
<td>@item.CompanyName</td>
<td>@item.Country</td>
</tr>
}
</table>
</form>
There are two submit buttons at the top of the form for testing the bulk updates and bulk deletes. And their asp-page-handler is set to Update (OnPostUpdate) and Delete (OnPostDelete) respectively. Below we render the Customers data in a table by iterating through the Items page model property.
Here is a sample run of this page:
Now let's check the working of ExecuteUpdate().
Go to OnPostUpdate() page handler and write the following code:
public void OnPostUpdate()
{
var query = from c in db.Customers
where c.Country == "India"
select c;
var rowsAffected = query.ExecuteUpdate
(x => x.SetProperty
(x => x.Country, "Bharat"));
Items = db.Customers.ToList();
}
Here, we pick only those records where Country is India using a LINQ query. Then we call ExecuteUpdate() method on the query to change the Country to Bharat.
Notice the usage of ExecuteUpdate(). It uses the SetProperty() method. The SetProperty() takes two parameters - the column to change and the new value for the column. The ExecuteUpdate() method returns an integer indicating the number of rows affected by the query execution. In this case it will return 2 since there are two records with Country value India. Quick Watch windows confirms this:
If you run the application and hit the Bulk Update button you will get the following UPDATE query in the SQL Server Profiler.
UPDATE [c]
SET [c].[Country] = N'Bharat'
FROM [Customers] AS [c]
WHERE [c].[Country] = N'India'
And the UI will reflect the changed Country name as shown below:
Let's switch the Country back to India, this time using an alternate way of writing the query.
public void OnPostUpdate()
{
var query = var query = db.Customers
.Where(x => x.Country == "Bharat");
var rowsAffected = query.ExecuteUpdate
(x => x.SetProperty
(x => x.Country, "India"));
Items = db.Customers.ToList();
}
As before, the UI will reflect the changes. To update the whole table you would have selected all the Customer entities (queries without where clause).
You can also use the existing value of a column (or value of any other column) as a part of the new value like this:
public void OnPostUpdate()
{
var query = var query = db.Customers
.Where(x => x.Country == "India");
var rowsAffected = query.ExecuteUpdate
(x => x.SetProperty(x => x.Country,
x=> x.Country + " / Bharat"));
Items = db.Customers.ToList();
}
This will change the Country column to India / Bharat.
To perform bulk deletes you will follow a similar approach but you will use ExecuteDelete() method. Moreover, you won't specify any column name and the new value as you did with ExecuteUpdate().
Go to the OnPostDelete() page handler and write the following code in it.
public void OnPostDelete()
{
var query = from c in db.Customers
where c.Country == "India"
select c;
var rowsAffected = query.ExecuteDelete();
Items = db.Customers.ToList();
}
The above code will delete all the rows where Country is India. The ExecuteDelete() returns the number of rows deleted (2 in this case).
The SQL Server profiler shows the following DELETE statement when the above code is run.
DELETE FROM [c]
FROM [Customers] AS [c]
WHERE [c].[Country] = N'India'
The UI confirms that the records are deleted.
In order to delete all the records you would have picked all the rows in the LINQ query by omitted the where clause.
In the preceding examples we used synchronous methods. The following code shows a sample usage of ExecuteUpdateAsync() and ExecuteDeleteAsync() methods.
public async Task<IActionResult> OnPostUpdate()
{
var query = from c in db.Customers
where c.Country == "India"
select c;
var rowsAffected = await query.ExecuteUpdate
(x => x.SetProperty
(x => x.Country, "Bharat"));
Items = db.Customers.ToList();
return Page();
}
public async Task<IActionResult> OnPostDelete()
{
var query = from c in db.Customers
where c.Country == "India"
select c;
var rowsAffected = await
query.ExecuteDeleteAsync();
Items = db.Customers.ToList();
return Page();
}
Notice the method calls marked in bold letters. Since we are now using the async versions, the page handlers are marked with async keyword and they return Task<IActionResult> object.
That's it for now! Keep coding!!