Welcome to today’s post.
In today’s post I will be discussing and showing how to make use of the new Bulk data update and deletion features of Entity Framework Core 7.
In the previous post I discussed how to use new update and deletion extension methods ExecuteUpdateAsync() and ExecuteDeleteAsync() within Entity Framework Core 7. The way we used them in that instance was to update or delete a particular record.
In this post I will show how to use the same extension methods to update or delete more than one record in the same query command (a batch of records). I will also compare using the new methods against functionally identical record batch operation queries used in previous versions of Entity Framework Core (version 6 and before).
For basic SQL updates and deletions, we could also use raw SQL queries through Entity Framework Core as I showed in a previous post, but in most cases we want to avoid using raw SQL to perform these data manipulations and abstract these operations through the Entity Framework Core API extension methods.
I will first show how we would normally update a batch of records using Entity Framework Core 6 extension methods, then do likewise with the new extension methods in using Entity Framework Core 7. I will then compare the output of commands in each case and performance.
Running Bulk Deletions using ExecuteUpdateAsync()
The API controller method that will receive the locations of the records to update with a new location is shown below:
[HttpPost("api/[controller]/MoveLocationOfBooks")]
public async Task<ActionResult> MoveLocationOfBooks(
string fromLocation,
string toLocation)
{
try
{
await _bookService.UpdateLocationOfBooks(fromLocation, toLocation);
return Ok();
}
catch (Exception ex)
{
return BadRequest($"Cannot move books record between locations : {ex.Message}");
}
}
The original service method UpdateLocationOfBooks() that performs the calculation of affected records, updating the record location column to a new value location, and saving changes to the database using Entity Framework Core 6 (and before) is shown below:
public async Task<int> UpdateLocationOfBooks(
string fromLocation,
string toLocation)
{
var booksToUpdate = await _db.Books.Where(
b => b.Location == fromLocation).ToListAsync();
var count = booksToUpdate.Count();
foreach (var book in booksToUpdate)
{
book.Location = toLocation;
}
_db.UpdateRange(booksToUpdate);
await _db.SaveChangesAsync();
return count;
}
Before I run the update on the table records, I have prepared some records (with SQL or API updates) within my table to have location fields with value “melbourne”. I then run the API and open Swagger UI to run the API method to update the location of books from “melbourne” location to “canberra”. I could also have run the update through CURL or Postman. Either method can achieve the same outcome.
After the update is executed, the console and file logs show the following HTTP and SQL commands that have been executed as part of the bulk update:
Request started: "POST" http://localhost:25138/api/Book/MoveLocationOfBooks?fromLocation=melbourne&toLocation=canberra
[22:02:03 INF] Executed DbCommand (81ms) [Parameters=[@__fromLocation_0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT [b].[ID], [b].[Author], [b].[DateCreated], [b].[DateUpdated], [b].[Edition], [b].[Genre], [b].[ISBN], [b].[Location], [b].[MediaType], [b].[Title], [b].[YearPublished]
FROM [Books] AS [b]
WHERE [b].[Location] = @__fromLocation_0
[22:02:04 INF] Executed DbCommand (151ms) [Parameters=[@p10='?' (DbType = Int32), @p0='?' (Size = 4000), @p1='?' (DbType = DateTime2), @p2='?' (DbType = DateTime2), @p3='?' (Size = 4000), @p4='?' (Size = 4000), @p5='?' (Size = 4000), @p6='?' (Size = 4000), @p7='?' (Size = 4000), @p8='?' (Size = 4000), @p9='?' (DbType = Int32), @p21='?' (DbType = Int32), @p11='?' (Size = 4000), @p12='?' (DbType = DateTime2), @p13='?' (DbType = DateTime2), @p14='?' (Size = 4000), @p15='?' (Size = 4000), @p16='?' (Size = 4000), @p17='?' (Size = 4000), @p18='?' (Size = 4000), @p19='?' (Size = 4000), @p20='?' (DbType = Int32), @p32='?' (DbType = Int32), @p22='?' (Size = 4000), @p23='?' (DbType = DateTime2), @p24='?' (DbType = DateTime2), @p25='?' (Size = 4000), @p26='?' (Size = 4000), @p27='?' (Size = 4000), @p28='?' (Size = 4000), @p29='?' (Size = 4000), @p30='?' (Size = 4000), @p31='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Books] SET [Author] = @p0, [DateCreated] = @p1, [DateUpdated] = @p2, [Edition] = @p3, [Genre] = @p4, [ISBN] = @p5, [Location] = @p6, [MediaType] = @p7, [Title] = @p8, [YearPublished] = @p9
OUTPUT 1
WHERE [ID] = @p10;
UPDATE [Books] SET [Author] = @p11, [DateCreated] = @p12, [DateUpdated] = @p13, [Edition] = @p14, [Genre] = @p15, [ISBN] = @p16, [Location] = @p17, [MediaType] = @p18, [Title] = @p19, [YearPublished] = @p20
OUTPUT 1
WHERE [ID] = @p21;
UPDATE [Books] SET [Author] = @p22, [DateCreated] = @p23, [DateUpdated] = @p24, [Edition] = @p25, [Genre] = @p26, [ISBN] = @p27, [Location] = @p28, [MediaType] = @p29, [Title] = @p30, [YearPublished] = @p31
OUTPUT 1
WHERE [ID] = @p32;
Response sent: http://localhost:25138/api/Book/MoveLocationOfBooks?fromLocation=melbourne&toLocation=canberra with HTTP status 200.0
[22:02:05 INF] HTTP POST /api/Book/MoveLocationOfBooks responded 200 in 1526.2952 ms
Notice that there are a total of 4 SQL commands that were run to perform the bulk update: one SELECT and three UPDATE commands. The number of individual update commands could be quite high for a larger number of records matching the criteria in the WHERE clause.
I will then modify the service method UpdateLocationOfBooks() to use the new Entity Framework 7 extension method ExecuteUpdateAsync(). The updated service method is shown below:
public async Task<int> UpdateLocationOfBooks(
string fromLocation,
string toLocation)
{
return await _db.Books
.Where(b => b.Location == fromLocation)
.ExecuteUpdateAsync(s =>
s.SetProperty(
t => t.Location,
t => toLocation
)
);
}
Notice the above functionality combines the selection of data with the update of data and result of operation (number of records) in one command.
Given our record locations in the previous update were updated to a location of “canberra”, I will now re-run the API call MoveLocationOfBooks() to update the location back to “melbourne”.
The three records that are to be updated are shown below in JSON format:
…
{
"id": 223,
"title": "Test Book 20",
"author": "Test20, Author",
"yearPublished": 1993,
"genre": "Fiction",
"edition": "Penguin",
"isbn": "20",
"location": "canberra",
"mediaType": "book",
"dateCreated": "2023-04-10T21:53:15.9054331",
"dateUpdated": "2023-04-10T21:53:16.1512706"
},
{
"id": 226,
"title": "Test Book 21",
"author": "Test21, Author",
"yearPublished": 1814,
"genre": "Fiction",
"edition": "Penguin",
"isbn": "21",
"location": "canberra",
"mediaType": "book",
"dateCreated": "2023-04-10T21:53:44.7051506",
"dateUpdated": "2023-04-10T21:53:44.7065625"
},
{
"id": 227,
"title": "Test Book 22",
"author": "Test22, Author",
"yearPublished": 1865,
"genre": "Fiction",
"edition": "Penguin",
"isbn": "22",
"location": "canberra",
"mediaType": "book",
"dateCreated": "2023-04-10T21:54:15.305917",
"dateUpdated": "2023-04-10T21:54:15.3074185"
}
…
The execution using Swagger UI is shown below:
After execution, the console and file logs show the following output:
Request started: "POST" http://localhost:25138/api/Book/MoveLocationOfBooks?fromLocation=canberra&toLocation=melbourne
[22:46:08 INF] Executed DbCommand (657ms) [Parameters=[@__toLocation_1='?' (Size = 4000), @__fromLocation_0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
UPDATE [b]
SET [b].[Location] = @__toLocation_1
FROM [Books] AS [b]
WHERE [b].[Location] = @__fromLocation_0
Response sent: http://localhost:25138/api/Book/MoveLocationOfBooks?fromLocation=canberra&toLocation=melbourne with HTTP status 200.0
[22:46:10 INF] HTTP POST /api/Book/MoveLocationOfBooks responded 200 in 1724.0222 ms
What you notice from the above output is the single SQL UPDATE command instead of four commands we had in the previous invocation that used the previous version of the service method. Using the new bulk update extension method has reduced the number of commands down from four to one. With a single record update, there would still be two commands. This is a significant saving when we use the update within a single committed transaction scope.
Running Bulk Deletions using ExecuteDeleteAsync()
I will next go through the comparison of running a bulk deletion using Entity Framework 6 extension methods against running functionally similar bulk deletion using the new Entity Framework 6 extension method.
I will first execute an original API controller method and service method to bulk delete records from location “brisbane”.
The API controller method DeleteBooksFromLocation() takes a single parameter, location, which matches all records of that location of which we wish to have bulk deleted from the table. It is shown below:
[HttpPost("api/[controller]/DeleteBooksFromLocation")]
public async Task<ActionResult> DeleteBooksFromLocation(string location)
{
try
{
await _bookService.DeleteBooksFromLocation(location);
return Ok();
}
catch (Exception ex)
{
return BadRequest($"Cannot delete books record from location : {ex.Message}");
}
}
The service method that performs the bulk deletion of records using a combination of Entity Framework 6 extension methods is shown below:
public async Task<int> DeleteBooksFromLocation(string location)
{
var booksToDelete = await _db.Books
.Where(b => b.Location == location).ToArrayAsync();
var count = booksToDelete.Count();
_db.RemoveRange(booksToDelete);
await _db.SaveChangesAsync();
return count;
}
Before I could run the bulk deletion on the table records, I prepared some records (with SQL or API updates) within my table to have location fields with value “brisbane”. I then run the API and open Swagger UI to run the API method to bulk delete the books from “brisbane”. As I explained earlier, I could also have run the update through CURL or Postman. Either method can achieve the same outcome.
After execution, the console and file logs show the following output:
Request started: "POST" http://localhost:25138/api/Book/DeleteBooksFromLocation?location=brisbane
2023-04-10 23:09:30.316 +10:00 [INF] Executed DbCommand (77ms) [Parameters=[@__location_0='?' (Size = 4000)], CommandType='"Text"', CommandTimeout='30']
SELECT [b].[ID], [b].[Author], [b].[DateCreated], [b].[DateUpdated], [b].[Edition], [b].[Genre], [b].[ISBN], [b].[Location], [b].[MediaType], [b].[Title], [b].[YearPublished]
FROM [Books] AS [b]
WHERE [b].[Location] = @__location_0
2023-04-10 23:09:30.579 +10:00 [INF] Executed DbCommand (2,394ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (DbType = Int32)], CommandType='"Text"', CommandTimeout='30']
SET NOCOUNT ON;
DELETE FROM [Books]
OUTPUT 1
WHERE [ID] = @p0;
DELETE FROM [Books]
OUTPUT 1
WHERE [ID] = @p1;
2023-04-10 23:09:31.067 +10:00 [INF] HTTP POST /api/Book/DeleteBooksFromLocation responded 200 in 1653.2718 ms
Notice that there are a total of 3 SQL commands that were run to perform the bulk deletion: one SELECT and two UPDATE commands. The number of individual update commands could be quite high for a larger number of records matching the criteria in the WHERE clause.
I will then modify the DeleteBooksFromLocation() service method to use the new Entity Framework 7 extension method ExecuteDeleteAsync(). The updated service method is shown below:
public async Task<int> DeleteBooksFromLocation(string location)
{
return await _db.Books
.Where(b => b.Location == location)
.ExecuteDeleteAsync();
}
Notice the above functionality combines the selection of data with the deletion of data and result of operation (number of records) in one command.
I then prepare another two records in the same table with matching locations “brisbane” (as the previous two were deleted in the previous API execution).
After execution, the console and file logs show the output as shown:
Request started: "POST" http://localhost:25138/api/Book/DeleteBooksFromLocation?location=brisbane
[23:51:53 INF] Executed DbCommand (116ms) [Parameters=[@__location_0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
DELETE FROM [b]
FROM [Books] AS [b]
WHERE [b].[Location] = @__location_0
[23:51:54 INF] HTTP POST /api/Book/DeleteBooksFromLocation responded 200 in 1404.2676 ms
Response sent: http://localhost:25138/api/Book/DeleteBooksFromLocation?location=brisbane with HTTP status 200.0
What you notice from the above output is the single SQL DELETE command instead of three commands we had in the previous invocation that used the previous version of the service method. Using the new bulk delete extension method has reduced the number of commands down from three to one. With a single record deletion, there would still be two commands. This is a significant saving when we use the deletion within a single committed transaction scope.
In summary, we have seen how to use both the new update and deletion extension methods ExecuteUpdateAsync() and ExecuteDeleteAsync() within Entity Framework Core 7. We have also seen how the previous version of Entity Framework Core would have been used to perform the equivalent bulk operations with more code. The additional code was used to compute record counts, update or remove records, then commit changes to the database. All of this overhead has been efficiently replaced with one single command that performs of these commands transparently and more efficiently in terms of fewer SQL commands on the backend. Where multiple entities are involved within the same transaction, the old methods can be employed. This just depends on what we are achieving in different parts of our application.
That completes my overview of the data manipulation and bulk data manipulation features of Entity Framework Core 7.
That is all for today’s post.
I hope you have found this post useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
