New in Entity Framework 7: Bulk Operations with ExecuteDelete and ExecuteUpdate

profile
Tim Deschryver
timdeschryver.dev

Version 7 of Entity Framework includes some popular features that have been asked for, one of which is Bulk Operations. This feature came to my attention from a tweet by Julie Lerman, and I had to try it out for myself.

https://twitter.com/julielerman/status/1557743067691569156

Why link

So why is this feature needed if we already can update and delete entities? The key word here is performance. This is a theme that has been on the top of the list when it comes to new EF versions, and this time it is no different.

The added methods improve the performance in multiple ways. Instead of first retrieving the entities and having all the entities in memory before we can perform an action on them, and lastly committing them to SQL. We now can do this with just a single operation, which results in one SQL command.

Let's take a look at how this looks like in code.

Setting the stage link

Before we dive into the examples, let's first set up our SQL database and populate 3 tables:

ExecuteDelete and ExecuteDeleteAsync link

Now that we got that out of our way, let's dive into ExecuteDelete and ExecuteDeleteAsync.

To delete a set of entities in bulk, filter out the entities that you want to delete by using the Where method (this is similar to before). Then, invoke the ExecuteDelete method on the collection of entities to be deleted.

Let's also take a look at the SQL statement that this generates:

As you can see, it simply generates one SQL statement to delete the entities that match the condition. The entities also aren't kept in memory anymore. Nice, simple, and efficient!

Cascade delete link

Let's take a look at another example, and let's remove some persons that hold references to addresses and pets. By deleting the person, we also delete the address and pets because the delete statement cascades to the foreign tables.

Similar to before, this results in the following SQL statement:

Number of rows affected link

It's also possible to see how many rows were affected by the delete operation, ExecuteDelete returns the number of rows affected.

In the expression above, the personsDeleted variable is equal to 100.

ExecuteUpdate and ExecuteUpdateAsync link

Now that we've seen how to delete entities, let's explore how to update them. Just like ExecuteDelete, we first have to filter the entities that we want to update, and then invoke ExecuteUpdate.

To update entities we need to use the new SetProperty method. The first argument of SetProperty selects the property that has to be updated via a lambda, and the second argument is the new value of that property also by using a lambda.

For example, let's set the last name of the persons to "Updated".

Which results in the corresponding SQL statement:

We can also access the values of an entity and use that to create a new value.

Resulting in the following SQL statement:

Updating multiple values at once link

We can even update multiple properties at once by invoking SetProperty multiple times.

And again, the corresponding SQL statement:

Number of rows affected link

Just like ExecuteDelete, ExecuteUpdate also returns the number of rows affected.

Note that updating nested entities is not supported.

More Updates in Entity Framework 7 link

For the complete list of new features, see the EF 7 plan.

Feel free to update this blog post on GitHub, thanks in advance!

Join My Newsletter (WIP)

Join my weekly newsletter to receive my latest blog posts and bits, directly in your inbox.

Support me

I appreciate it if you would support me if have you enjoyed this post and found it useful, thank you in advance.

Buy Me a Coffee at ko-fi.com PayPal logo

Share this post

Twitter LinkedIn