• Home
• Entity Framework Core
• Common EF Core mistake: inefficient update and delete

Common EF Core mistake: inefficient update and delete

Before Entity Framework Core 7 we had to fetch any data we wanted to update or delete but EF Core 7 came with two awesome features: ExecuteDelete and ExecuteUpdate.

These two methods allow you to perform data operations in the database without using the traditional EF Core change tracking and the SaveChanges method, which results in a huge performance gain.

Let's compare these two methods.

ExecuteDelete

Suppose we want to delete from our database all customers who have not ordered from us for 1 year.

Before EF 7 we would have written something like this:

foreach (var customer in context.Customers.Where(c => c.HadOrderedThisYear == false ))
{
    context.Customers.Remove(customer);
}

context.SaveChanges();

In this code we fetch all the customers we want to delete (a select query is sent to the database) then we iterate over each of them to delete it; a delete SQL query is sent for each customer, which can be very inefficient with a large amount of data.

Since EF Core 7 and thanks to the ExecuteDelete method, we can now do the same thing in a more efficient way:

context.Customers.Where(c => c.HadOrderedThisYear == false).ExecuteDelete();

This way there is no query to select the data we will manipulate, EF Core will only generate a delete query like this:

DELETE FROM [c]
FROM [Customers] AS [c]
WHERE [c].[HadOrderedThisYear] = 0

Clean, efficient.

ExecuteUpdate

For example, suppose you have made a mistake and inverted the customers' first names and last names. You now want to fix it by swapping them.

With the old EF Core way we would have written some code like this:

foreach (var customer in context.Customers)
{
	string firstName = customer.FirstName;
    customer.FirstName = customer.LastName;
	customer.LastName = firstName;
}

context.SaveChanges();

Let's rewrite this code using the ExecuteUpdate method:

  context.Customers
    .ExecuteUpdate(x => 
		x.SetProperty(c => c.FirstName, b => b.LastName)
		x.SetProperty(c => c.LastName, b => b.FirstName));

The SQL query will look like this:

  UPDATE [x]
  SET [x].[FirstName] = [x].[LastName],
  [x].[LastName] = [x].[FirstName]
  FROM [Customers] AS [x];

Like the AsNoTracking method we saw in the previous post Common EF Core mistake: not using the difference between Tracking and No-Tracking Queries, the two methods ExecuteDelete and ExecuteUpdate allow you to perform more operations without using the traditional EF Core change tracking, and to make some easy performance improvements.