BulkUpdate in Entity Framework

SPONSOR AD

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.

BulkUpdate in Entity Framework using Z.EntityFramework.Extensions

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:

SPONSOR AD

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.

Performance Comparison Between SaveChanges and BulkUpdate Extension Method

Note: A lot of factors need to be considered for affecting the benchmark time such as index, column type, latency, throttling, etc.

SPONSOR AD
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:
  1. Easy to use, simply call the BulkUpdate Method on the context object.
  2. Flexible as it also provides different options.
  3. Increase performance due to reducing database round-trips.
  4. Increase application responsiveness.
  5. 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:

  1. Update and include/exclude properties
  2. Update with custom key
  3. Update with related child entities (Include Graph)
  4. 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.

SPONSOR AD

3 thoughts on “BulkUpdate in Entity Framework”

Leave a Reply

Your email address will not be published. Required fields are marked *