MNW #013: How To Use The New Bulk Update Feature In EF Core 7

A big thank you to this week's sponsor who helps keep this newsletter free to the reader:

Sick of staying up late firefighting deployments? Reclaim your nights with a database that keeps your app up even when things go sideways. Outsource hosting to the experts with RavenDB Cloud today so you can sleep well tomorrow.

In this week's newsletter, we're going to explore the new ExecuteUpdate and ExecuteDelete methods that were released with EF7.

ExecuteUpdate allows us to write a query and run a bulk update operation on the entities matching that query.

Similarly, ExecuteDelete allows us to write a query and delete the entities matching that query.

We can significantly improve performance using the new methods in some scenarios, and I'm going to show you what those scenarios are.

Updating And Deleting Entities Before EF Core 7

If you want to update a collection of entities before EF7, you need to load the entities into memory using the DatabaseContext.

The EF ChangeTracker will then track any changes made to these entities. When you are ready to commit the changes to the database, you simply call the SaveChanges method.

Here's an example where we load a few notifications, and we want to snooze them so they aren't sent:

var notifications = dbContext
    .Notifications
    .Where(n => !n.Snoozed)
    .ToList();

foreach(var notification in notifications)
{
    notification.Snoozed = true;
}

dbContext.SaveChanges();

EF7 will generate the following SQL statement to update the records in the database:

UPDATE [Notifications] n
SET n.[Snoozed] = TRUE
WHERE n.[Id] = @notificationId_1;

...

UPDATE [Notifications] n
SET n.[Snoozed] = TRUE
WHERE n.[Id] = @notificationId_N;

Notice that for every notification we end up with one SQL UPDATE statement. This won't scale well as the number of notifications increases.

Updating Entities With ExecuteUpdate

With EF7, we now have access to the new ExecuteUdpate method. It also has an asynchronous version - ExecuteUpdateAsync.

How do you use it?

You need to write a query that will select the records you want to update, and then call the ExecuteUpdate method on the resulting IQueryable.

Let's rewrite the previous example using the new approach:

dbContext
    .Notifications
    .Where(n => !n.Snoozed)
    .ExecuteUpdate(s => s.SetProperty(
        n => n.Snoozed,
        n => true));

In the call to ExecuteUpdate we call the SetProperty method to specify which properties we want to update, and what values we want to set. The SetProperty method can be called multiple times, if you need to update more than one property.

In this case, EF7 will generate the following SQL query:

UPDATE n
SET n.[Snoozed] = TRUE
FROM [Notifications] AS n
WHERE n.[Snoozed] = FALSE;

Notice that this time we only have one SQL query being sent to the database. This is a major performance improvement. It can be as much as 10x faster than the old version, from my testing.

Deleting Entities With ExecuteDelete

Let's also see how we can do bulk deletes using the ExecuteDelete and ExecuteDeleteAsync methods.

Again, you have to write a query that will select the records you want to delete, and then call the ExecuteDelete method on the resulting IQueryable.

If you want to delete all snoozed notifications:

dbContext
    .Notifications
    .Where(n => n.Snoozed)
    .ExecuteDelete();

And EF7 will generate the following SQL query:

DELETE FROM n
FROM [Notifications] AS n
WHERE n.[Snoozed] = TRUE;

I think this will be incredibly useful when you want to delete records in the database based on a specific condition.

Transactions, Change Tracking And Query Filters With Bulk Methods

You need to be aware how transactions and change tracking work with the new bulk methods. ExecuteUpdate and ExecuteDelete will immediately go to database, and run the SQL query.

What does this mean for transactions?

If you want to run a bulk method together with other updates applied with SaveChanges, by default they won't run in the same transaction. You need to open an explicit transaction using the DatabaseContext to keep everything consistent.

What does this mean for change tracking?

ExecuteUpdate and ExecuteDelete run directly on the database, without loading any entities into memory. EF7 will not track these entities in the ChangeTracker.

If you have any database interceptors defined, they won't execute after calling one of the bulk update methods. This also means that if you override SaveChanges to add custom behavior, it won't be called.

Do Query Filters still work?

Yes, query filters will be correctly applied when calling ExecuteUpdate or ExecuteDelete.

When Should You Use The New Bulk Methods?

I think this is an excellent new addition to EF7, and it solves a real problem when you need to run a typical UPDATE or DELETE query with a WHERE statement applied.

Previously, you had to write raw SQL and execute it using something like Dapper.

I will likely use this approach when it applies to my projects.