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

Common EF Core mistake: inneficient 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 allows you to perform data operations in the database without using the traditionnal EF Core change tracking and the SaveChanges method, which results a huge performance gain.

Let's compare theses 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 on each of them to delete it, a delete SQL query is sent for each customer, which can be very inneficient with large amount of datas.

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

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

With this way there is no query to select the datas 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, efficent.

ExecuteUpdate

For example, suppose you have commited a mistake and inverted the customer first names and last names. You now want to fix it by swaping 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 looks like that:

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

As using 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 method ExecuteDelete and ExecuteUpdate allow you to perform more operations without using the tradictional EF Core change tracking and make some easy performance improvments.