In this article, we are going to talk about how to use bulk updates in Entity Framework Core. If you are interested in a deeper dive into how to use Entity Framework Core, we have a whole series about that.
Let’s see how we can do bulk updates using Entity Framework Core bulk operations.
Prepare the Entity Model
For this article, we are going to use the entity model to demonstrate the usage of the new bulk updates in Entity Framework Core. Therefore, we have a Team entity:
public class Team
{
[Key]
public Guid Id { get; set; }
[Required]
public string Name { get; set; }
[Required]
public int YearFounded { get; set; }
[Required]
public string Description { get; set; }
public ICollection<Player> Players { get; set; }
public ICollection<Game> Games { get; set; }
}
And every team has some players in it:
public class Player
{
[Key]
public Guid Id { get; set; }
[Required]
public string Name { get; set; }
[Required]
public int Age { get; set; }
[Required]
public Guid TeamId { get; set; }
[ForeignKey(nameof(TeamId))]
public virtual Team Team { get; set; }
}
And it also has some games:
public class Game
{
[Key]
public Guid Id { get; set; }
[Required]
public string Opponent { get; set; }
[Required]
public DateTime Date { get; set; }
[Required]
public Guid TeamId { get; set; }
[Required]
public virtual Team Team { get; set; }
}
This represents the baseline of our entity model which we are going to use throughout the rest of the article.
The Conventional SaveChanges Method
Now, that we have prepared our data model, let’s assume we have some Team records stored in our database and want to bulk update/delete them using Entity Framework Core.
One conventional way is to load all of the records and make desired updates to them through a loop and call _context.SaveChanges() method. Then, the change tracker identifies the modification of entities and performs several SQL updates to SQL Server to make updates to all the records.
Let’s have a look at the code:
var teams = await _context.Teams
.ToListAsync();
foreach (var team in teams)
{
team.YearFounded += 1;
team.Description += " " + description;
}
return await _context.SaveChangesAsync();
Here, we increase the YearFounded property of all the team records by 1 and concatenate a description to the Description property.
If for example, we have 4 records and MaxBatchSize set to 2, as soon as we call _context.SaveChanges() method, this translates into two SQL executions.
Let’s examine the first one:
Executed DbCommand
[Parameters=[@p2='4a211dd7-e2f6-4023-ac83-bd0bd303fec9', @p0='Description 3 for Code Maze Newly Added'
(Nullable = false) (Size = 4000), @p1='1992', @p5='4dfbf011-d4f3-4dff-a5e2-3bd3d5c28d76',
@p3='Description 0 for Code Maze Newly Added' (Nullable = false) (Size = 4000),
@p4='1994'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Teams]
SET [Description] = @p0,
[YearFounded] = @p1
OUTPUT 1
WHERE [Id] = @p2;
UPDATE [Teams]
SET [Description] = @p3,
[YearFounded] = @p4
OUTPUT 1
WHERE [Id] = @p5;
And the second one:
Executed DbCommand
[Parameters=[@p2='537072e6-e002-4b9c-a9e4-8ad8e83fa4ac', @p0='Description 1 for Code Maze Newly Added'
(Nullable = false) (Size = 4000), @p1='2020', @p5='9396739b-05b4-42fb-84f9-6ed7fa3b64db',
@p3='Description 2 for Code Maze Newly Added' (Nullable = false) (Size = 4000), @p4='1983'],
CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
UPDATE [Teams]
SET [Description] = @p0,
[YearFounded] = @p1
OUTPUT 1
WHERE [Id] = @p2;
UPDATE [Teams]
SET [Description] = @p3,
[YearFounded] = @p4
OUTPUT 1
WHERE [Id] = @p5;
These SQL executions indicate that two sequential batch updates have been performed on our database. The default MaxBatchSize in EF Core is 42. It means that if we have 100 records and we want to update all of them, we need to make [100 /42]+1 =3 roundtrips to the database to complete the task.
Depending on the MaxBatchSize setting and the number of records = n, we need to perform ceil(n/MaxBatchSize) roundtrips to the database to update all the data.
Now, let’s see the code for conventional bulk delete:
var teams = await _context.Teams
.ToListAsync();
_context.RemoveRange(teams);
return await _context.SaveChangesAsync();
The same pattern and formula apply with bulk delete. Again, we have n=4 records and MaxBatchSize = 2. As soon as we call _context.SaveChanges() method, this translates to the two SQL executions:
Executed DbCommand [Parameters=[@p0='00b31bff-84b6-4935-9306-d952face0fcd', @p1='5b10d33d-c2de-4ec1-9e35-7460fc83ebdc'], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; DELETE FROM [Teams] OUTPUT 1 WHERE [Id] = @p0; DELETE FROM [Teams] OUTPUT 1 WHERE [Id] = @p1;
And the second one:
Executed DbCommand [Parameters=[@p0='791a41ce-4ad4-4f34-a708-54df926c1726', @p1='a467a4f0-5406-4cfa-a726-3682cd9f3437'], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; DELETE FROM [Teams] OUTPUT 1 WHERE [Id] = @p0; DELETE FROM [Teams] OUTPUT 1 WHERE [Id] = @p1;
The New Bulk Updates In Entity Framework Core
Microsoft introduced two new bulk update methods since Entity Framework Core 7: ExecuteUpdate and ExecuteDelete. With these two methods, we no longer need to load all the entities into memory to update their properties or to delete them all. We just need to tell the method to update the desired properties or to delete all of the records and then it takes care of the rest of it. Therefore, we no longer have a change tracker involved and the whole bulk update gets executed in only one database roundtrip regardless of the value of MaxBatchSize.
ExecuteUpdate Method
Now, we want to use the ExecuteUpdate method to update four Team records:
Name YearFounded Description ------------------------------------------------------ Team 3 2004 Description 3 for Code Maze Team 2 1998 Description 2 for Code Maze Team 0 1974 Description 0 for Code Maze Team 1 2005 Description 1 for Code Maze
Let’s write the code:
await _context.Teams
.ExecuteUpdateAsync(t =>
t.SetProperty(b => b.YearFounded, b => b.YearFounded + 1)
.SetProperty(b => b.Description, b => b.Description + " " + description));
Here, we use SetProperty to explicitly set the desired value of each property.
Again, we can inspect the translated SQL script in the Visual Studio output window:
Executed DbCommand
[Parameters=[@__description_0='Newly Added' (Size = 4000)], CommandType='Text', CommandTimeout='30']
UPDATE [t]
SET [t].[Description] = ([t].[Description] + N' ') + @__description_0,
[t].[YearFounded] = [t].[YearFounded] + 1
FROM [Teams] AS [t]
We can observe that the whole bulk update executes in only one database roundtrip.
Next, we can fetch all the records to inspect the changes:
return await _context.Teams
.ToListAsync();
Once we execute it, we can find the result:
Name YearFounded Description ------------------------------------------------------ Team 3 2004 Description 3 for Code Maze Team 2 1998 Description 2 for Code Maze Team 0 1974 Description 0 for Code Maze Team 1 2005 Description 1 for Code Maze
As we can see, the data has not changed. What is the problem?
Well, as we said, we no longer have the change tracker involved when we use ExecuteUpdate method. It means that after executing ExecuteUpdate, the change tracker is not aware of the modification to the records; therefore, when we call _context.Teams.ToListAsync(), it uses the cached values and returns them to us. So, what should we do?
We should modify the code and add AsNoTracking() to it so that the new values are fetched from the database:
return await _context.Teams
.AsNoTracking()
.ToListAsync();
Again, let’s inspect the records:
Name YearFounded Description ------------------------------------------------------ Team 3 2005 Description 3 for Code Maze Newly Added Team 2 1999 Description 2 for Code Maze Newly Added Team 0 1975 Description 0 for Code Maze Newly Added Team 1 2006 Description 1 for Code Maze Newly Added
Now, we can see that YearFounded and Description properties of all records have changed accordingly.
ExecuteDelete Method
Now, it’s time to use ExecuteDelete to delete all the records that satisfy a desired condition.
For example, we want to delete all the Team records that have YearFounded greater than a specified minYearFounded value:
await _context.Teams .Where(t => t.YearFounded >= minYearFounded) .ExecuteDeleteAsync();
Next, let’s inspect the output window:
Executed DbCommand [Parameters=[@__minYearFounded_0='1970'], CommandType='Text', CommandTimeout='30'] DELETE FROM [t] FROM [Teams] AS [t] WHERE [t].[YearFounded] >= @__minYearFounded_0
Again, the system executes the bulk delete in only one database roundtrip no matter the value of MaxBatchSize and the change tracker remains unaware of the deletion operation. Therefore, if we want to get the Team records after the delete, we should use AsNoTracking().
Moreover, in cases where there are a principal entity and several dependent entities, such as the Team entity and the Player entity in this example, we should handle delete constraints (CascadeDelete, SetNull, NoAction) the same way we handle normal delete operations.
Handle Inheritance Strategies With Bulk Updates In Entity Framework Core
When we want to use the two new bulk update methods ExecuteUpdate and ExecuteDelete to update data mapped from inherited entities, we should remember one simple rule: We cannot use these bulk update methods to act on multiple tables; therefore, if we have table hierarchies using TPH inheritance strategy, we will have no issues because the bulk update methods will act only on one common table.
Another example is when we have TPC inheritance strategy. To demonstrate this, let’s assume we have an Employee abstract class:
public abstract class Employee
{
[Key]
public Guid Id { get; set; }
[Required]
public string FirstName { get; set; }
[Required]
public string LastName { get; set; }
[Required]
public int YearsExperience { get; set; }
}
There is a Referee entity that inherits Employee class:
public class Referee : Employee
{
[Required]
public string RefereeCode { get; set; }
[Required]
public string LicenceNo { get; set; }
[Required]
public string Category { get; set; }
}
And also an Organizer entity that inherits Employee class as well:
public class Organizer : Employee
{
[Required]
public string OrganizerCode { get; set; }
[Required]
public int NoOfEventsOrganized { get; set; }
}
We use the TPC inheritance mapping strategy; therefore, it means we have Organizer and Referee tables in our database but we do not have a separate table for the Employee abstract class. Now, let’s delete Referee records:
return await _context.Set<Referee>()
.ExecuteDeleteAsync();
We can see that this translates to:
DELETE FROM [r] FROM [Referees] AS [r]
And after we execute it, the Referee records get deleted successfully.
Let’s consider another scenario where we have a Table Per Concrete Type (TPC) inheritance strategy.
In this scenario, we need to delete or update employees with experience exceeding a certain number of years:
await _context.Employees
.Where(p => p.YearsExperience > minYearExperience)
.ExecuteDeleteAsync();
In this situation, since there are multiple concrete tables (Referees and Organizers) inherited from Employee entity, this returns a System.InvalidOperationException.
To demonstrate TPT inheritance strategy, let’s assume that we have one type of game: FootballGame and it inherits Game class that we earlier defined:
public class FootballGame : Game
{
public int FirstHalfTimeScore { get; set; }
public int SecondHalfTimeScore { get; set; }
}
To delete all FootballGame records, we execute the code:
await _context.Games .OfType<FootballGame>() .ExecuteDeleteAsync();
We get a System.InvalidOperationException:
The LINQ expression 'DbSet<Game>()
.OfType<FootballGame>()
.ExecuteDelete()' could not be translated. Additional information: The operation
'ExecuteDelete' is being applied on entity type 'FootballGame', which is using the
TPT mapping strategy. 'ExecuteDelete'/'ExecuteUpdate' operations on hierarchies mapped as TPT
is not supported. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
This exception also indicates that the same thing happens if we want to use ExecuteUpdate method while we have a TPT mapping strategy. The reason is simple: In the TPT inheritance strategy we keep data for the inherited types (e.g FootballGame) in two distinct database tables, Games and FootballGames in this case. Therefore, the LINQ expression cannot execute a bulk update on the two database tables and returns an exception instead.
Conclusion
In this article, we talked about how to use bulk updates in Entity Framework Core and provided some examples to demonstrate how to use them both with conventional SaveChanges method and with the new bulk update methods (ExecuteUpdate and ExecuteDelete) introduced in Entity Framework Core 7.
