Back to: Entity Framework Tutorials For Begineers and Professionals
Bulk Update in Entity Framework using BulkUpdate Extension Method
In this article, I am going to discuss How to perform Bulk Update in Entity Framework using the BulkUpdate Extension Method with Examples. Please read our previous article where we discussed how to perform Bulk Insert in Entity Framework using the BulkInsert Extension Method with Examples. We are going to work with the same example that we created in our Introduction to Entity Framework Database First Approach article. Please read our introduction to Entity Framework Database First article before proceeding to this article.
Z.EntityFramework.Extensions:
Here, we are going to use Z.EntityFramework.Extensions to perform Bulk Update Operation. So, first, open the NuGet Package Manager window and search for Z.EntityFramework.Extensions package. Select Z.EntityFramework.Extensions and then select the Project and choose the latest version, And, finally, click on the Install button as shown in the below image which will install Z.EntityFramework.Extensions DLL into your project.
Bulk Update in Entity Framework
The BulkUpdate and BulkUpdateAync methods extend our DbContext object which allows us to update a large number of entities in the database. The syntax to use the BulkUpdate Extension Method in EntityFramework is as follows:
context.BulkUpdate(studentsList);
context.BulUpdateAsync(studentsList, cancellationToken);
Example to Understand BulkUpdate Extension Method using Entity Framework
In the below example, first, we fetch all the students whose StandardId is 1 and then update the First Name and Last Name and finally update the updated data to the database using the BulkUpdate Extension Method.
using System;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
Console.WriteLine("BulkUpdate Method Started");
BulkUpdate(1);
Console.WriteLine("BulkUpdate Method Completed");
Console.Read();
}
public static void BulkUpdate(int StandardId)
{
using (var context = new EF_Demo_DBEntities())
{
//Fetch all the students whose StandardId is 1
var studentsList = context.Students.Where(std => std.StandardId == StandardId);
//Update the Firstname and LastName of all Stduents
foreach (var std in studentsList)
{
std.FirstName += " Changed";
std.LastName += " Changed";
}
//To See the Generated SQL By the BulkUpdate Extension Method
context.Database.Log = Console.Write;
// Call the BulkUpdate Extension Method to perform the Bulk Update Operation
context.BulkUpdate(studentsList);
}
}
}
}
When you run the above code, you will get the following output. You can see, it is using a single round trip and single update statement to perform the Bulk Update Operation which will improve the overall application performance.
BulkUpdate Method Started
Opened connection at 12-12-2022 21:00:22 +05:30
SELECT
[Extent1].[StudentId] AS [StudentId],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[StandardId] AS [StandardId]
FROM [dbo].[Student] AS [Extent1]
WHERE [Extent1].[StandardId] = @p__linq__0
-- p__linq__0: '1' (Type = Int32, IsNullable = false)
-- Executing at 12-12-2022 21:00:22 +05:30
-- Completed in 2 ms with result: SqlDataReader
-- Executing Command:
/* SELECT server information */
SELECT @@VERSION
/* SELECT table information */
SELECT DestinationTable.Name AS DestinationName ,
( SELECT 1
WHERE EXISTS ( SELECT 1
FROM sys.triggers AS X
WHERE X.parent_id = A.object_id
AND X.is_disabled = 0
AND OBJECTPROPERTY(X.object_id,
'ExecIsInsertTrigger') = 1 )
) AS HasInsertTrigger ,
( SELECT 1
WHERE EXISTS ( SELECT 1
FROM sys.triggers AS X
WHERE X.parent_id = A.object_id
AND X.is_disabled = 0
AND OBJECTPROPERTY(X.object_id,
'ExecIsUpdateTrigger') = 1 )
) AS HasUpdateTrigger ,
( SELECT 1
WHERE EXISTS ( SELECT 1
FROM sys.triggers AS X
WHERE X.parent_id = A.object_id
AND X.is_disabled = 0
AND OBJECTPROPERTY(X.object_id,
'ExecIsDeleteTrigger') = 1 )
) AS HasDeleteTrigger
FROM (SELECT @Table_0 AS Name) AS DestinationTable
LEFT JOIN sys.synonyms AS B ON B.object_id = OBJECT_ID(DestinationTable.Name)
AND COALESCE(PARSENAME(base_object_name,4), @@SERVERNAME) = @@SERVERNAME
AND COALESCE(PARSENAME(base_object_name,3), DB_NAME(DB_ID())) = DB_NAME(DB_ID())
INNER JOIN sys.tables AS A ON A.object_id = OBJECT_ID(DestinationTable.Name)
OR A.object_id = OBJECT_ID(B.base_object_name)
ORDER BY DestinationName
/* SELECT column information */
SELECT DestinationTable.Name AS DestinationName ,
C.name AS ColumnName ,
C.column_id AS ColumnOrder ,
C.precision AS Precision ,
C.scale AS Scale ,
C.max_length AS MaxLength ,
C.collation_name AS Collation ,
C.Is_Identity AS IsIdentity ,
( CASE WHEN EXISTS ( SELECT 1
FROM sys.index_columns AS X
WHERE X.index_id = B.index_id
AND X.object_id = B.object_id
AND X.column_id = C.column_id ) THEN 1
ELSE 0
END ) AS IsPrimaryKey ,
C.system_type_id AS System_Type_Id ,
D.Name AS TypeName,
(CASE WHEN E.base_object_name IS NOT NULL THEN 1 ELSE 0 END) AS IsSynonym,
D.is_user_defined,
F.name,
CASE WHEN C.default_object_id = 0 THEN 'ZZZ_NO_DEFAULT' ELSE ISNULL(OBJECT_DEFINITION(C.default_object_id), 'ZZZ_ERROR_DEFAULT_ZZZ') END AS DefaultValueSql,
C.is_nullable
FROM (SELECT @Table_0 AS Name) AS DestinationTable
LEFT JOIN sys.synonyms AS E ON E.object_id = OBJECT_ID(DestinationTable.Name)
AND COALESCE(PARSENAME(base_object_name,4), @@SERVERNAME) = @@SERVERNAME
AND COALESCE(PARSENAME(base_object_name,3), DB_NAME(DB_ID())) = DB_NAME(DB_ID())
INNER JOIN sys.tables AS A ON A.object_id = OBJECT_ID(DestinationTable.Name)
OR A.object_id = OBJECT_ID(E.base_object_name)
LEFT JOIN sys.indexes AS B ON B.object_id = A.object_id
AND B.is_primary_key = 1
INNER JOIN sys.columns AS C ON C.object_id = A.object_id
INNER JOIN sys.types AS D ON D.system_type_id = C.system_type_id
AND D.user_type_id = C.user_type_id
INNER JOIN sys.schemas AS F ON D.schema_id = F.schema_id
ORDER BY DestinationName ,
ColumnOrder
-- @Table_0: [dbo].[Student] (Type = String, Size = 15)
-- CommandTimeout:30
-- Executing at 12-12-2022 21:00:22
-- Completed at 12-12-2022 21:00:23
-- Result: SqlDataReader
-- Executing Command:
CREATE TABLE #ZZZProjects_e88d0382_b7d6_4456_ba6a_7b3d26b558f8 ( [StudentId] [sys].[int] NULL, [FirstName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [LastName] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [StandardId] [sys].[int] NULL, ZZZ_Index [INT] NOT NULL )
CREATE CLUSTERED INDEX [INDEX_#ZZZProjects_e88d0382_b7d6_4456_ba6a_7b3d26b558f8] ON #ZZZProjects_e88d0382_b7d6_4456_ba6a_7b3d26b558f8 (ZZZ_Index ASC);
-- CommandTimeout:120
-- Executing at 12-12-2022 21:00:23
-- Completed at 12-12-2022 21:00:23
-- Result: -1
SqlBulkCopy: #ZZZProjects_e88d0382_b7d6_4456_ba6a_7b3d26b558f8
-- BulkCopyTimeout:120
Executing at 12-12-2022 21:00:23
Completed at 12-12-2022 21:00:23
-- Executing Command:
UPDATE DestinationTable
SET [FirstName] = StagingTable.[FirstName], [LastName] = StagingTable.[LastName], [StandardId] = StagingTable.[StandardId]
FROM [dbo].[Student] AS DestinationTable
INNER JOIN (SELECT TOP 100 PERCENT * FROM #ZZZProjects_e88d0382_b7d6_4456_ba6a_7b3d26b558f8
WHERE ZZZ_Index >= @IndexStart AND ZZZ_Index <= @IndexEnd ORDER BY ZZZ_Index) AS StagingTable ON DestinationTable.[StudentId] = StagingTable.[StudentId]
;
-- @IndexStart: 0 (Type = Int32, Size = 0)
-- @IndexEnd: 2023 (Type = Int32, Size = 0)
-- CommandTimeout:120
-- Executing at 12-12-2022 21:00:23
-- Completed at 12-12-2022 21:00:23
-- Result: 2024
-- Executing Command:
DROP TABLE #ZZZProjects_e88d0382_b7d6_4456_ba6a_7b3d26b558f8;
-- CommandTimeout:120
-- Executing at 12-12-2022 21:00:23
-- Completed at 12-12-2022 21:00:23
-- Result: -1
Closed connection at 12-12-2022 21:00:23 +05:30
BulkUpdate Method Completed
Performance Comparison Between SaveChanges and BulkUpdate Extension Method:
Let’s see the performance benchmark between the SaveChanges Method and BulkUpdate Extension Method in Entity Framework with an Example. In the below example, first, we are inserting 1000 students into the student table and then we are going to update the first name and last names of all the students using both approaches. Here, we will also print the time taken to complete the task by both approaches.
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
//First Add 1000 Students
AddStudents(1000);
Stopwatch SaveChangesStopwatch = new Stopwatch();
Stopwatch BulkUpdateStopwatch = new Stopwatch();
//Bulk Update using SaveChanges
using (var context = new EF_Demo_DBEntities())
{
//Fetch all the Students
var studentList = context.Students.ToList();
foreach (var std in studentList)
{
std.FirstName += "_UpdateBySaveChanges";
std.LastName += "_UpdateBySaveChanges";
}
SaveChangesStopwatch.Start();
//Call the SaveChanges Method to Update the Data in the Database
context.SaveChanges();
SaveChangesStopwatch.Stop();
Console.WriteLine($"SaveChanges, Entities : {studentList.Count}, Performance : {SaveChangesStopwatch.ElapsedMilliseconds} MS");
}
//Bulk Update using BulkUpdate Extension Method
using (var context = new EF_Demo_DBEntities())
{
//Fetch all the Students
var studentList = context.Students.ToList();
foreach (var std in studentList)
{
std.FirstName += "_UpdatedByBulkUpdate";
std.LastName += "_UpdatedByBulkUpdate";
}
BulkUpdateStopwatch.Start();
//Call the BulkUpdate Method to Update the Data in the Database
context.BulkUpdate(studentList);
BulkUpdateStopwatch.Stop();
Console.WriteLine($"BulkUpdate, Entities : {studentList.Count}, Performance : {BulkUpdateStopwatch.ElapsedMilliseconds} MS");
}
Console.Read();
}
public static void AddStudents(int count)
{
//Add 1000 Students for Performance Testing
var stduentsList = GenerateStudents(count);
// BulkInsert using BulkInsert Extension Method
using (var context = new EF_Demo_DBEntities())
{
context.BulkInsert(stduentsList, options => options.AutoMapOutputDirection = false);
}
}
public static List<Student> GenerateStudents(int count)
{
List<Student> Studentlist = new List<Student>();
for (int i = 0; i < count; i++)
{
Studentlist.Add(new Student() { FirstName = "FirstName_" + i, LastName = "LastName_" + i, StandardId = 1 });
}
return Studentlist;
}
}
}
When you run the above code, you will get the following output. As you can see, the SaveChanges method took 1333 MS time to update 3028 records into the database while the BulkUpdate Extension Method took only 310 MS to update the same 3028 records into the database. So, clearly, there is a huge performance difference between both approaches. So, in real-time applications, while performing BULK UPDATE, you should avoid using the SaveChanges method if performance is the major factor.
Note: A lot of factors need to be considered for affecting the benchmark time such as index, column type, latency, throttling, etc.
Why BulkUpdate is faster than SaveChanges in Entity Framework?
In real-time applications, it is a common task to update 1000 or more entities. The SaveChanges method makes it quite impossible to handle this kind of situation due to the number of database round-trips required. The SaveChanges perform one database round-trip for every entity to update. So, if you need to update 10,000 entities, 10,000 database round-trips will be performed which is make it slow. If you are performing the update operation using any API, then it might be possible for the client who consumes your API to get a time-out error as the update operation going to take a huge amount of time.
The BulkUpdate Extension Method requires the minimum database round-trips possible. For example, under the hood for SQL Server, a SqlBulkCopy is performed first in a temporary table, then an UPDATE from the temporary table to the destination table is performed which is the fastest way available.
Advantages of BulkUpdate Extension Method in Entity Framework:
- Easy to use, simply call the BulkUpdate Method on the context object.
- Flexible as it also provides different options.
- Increase performance due to reducing database round-trips.
- Increase application responsiveness.
- Reduction in database round-trips also reduces the load on the database server.
Scenarios to use BulkUpdate in Real-Time Application
The BulkUpdate Extension Method in Entity Framework is not only fast but also so flexible that allows us to handle various scenarios in Entity Framework. Some of them are as follows:
- Update and include/exclude properties
- Update with custom key
- Update with related child entities (Include Graph)
- Update with future action
In the next article, I am going to discuss the BulkDelete Extension Method in Entity Framework with Examples. Here, in this article, I try to explain How to Perform Bulk Delete using the BulkUpdate Extension method in Entity Framework with Examples. I hope you enjoy this BulkUpdate Extension method in the Entity Framework article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.
EF Core 7 now supports Bulk Update / Delete out of the box, so there’s no need for further external libraries.
Yes, You are right. But the example that I am showing here is not on .NET Core, it is on .NET Framework which does not support bulk operations.
This is still useful if the values to be updated are different.