BulkDelete in Entity Framework

Back to: Entity Framework Tutorials For Begineers and Professionals

Bulk Delete in Entity Framework using the BulkDelete Extension Method

Now, I am going to discuss how to perform Bulk Delete in Entity Framework using the BulkDelete Extension Method with Examples. Please read our previous article where we discussed how to perform Bulk Update 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 Delete in Entity Framework

The BulkDelete and BulkDeleteAync methods extend our DbContext object which allows us to delete a large number of entities into the database with a single round trip which will improve the performance of the application. The syntax to use the BulkDelete extension method is as follows:

context.BulkDelete(StudentsListToBeDeleted);
context.BulUpdateAsync(StudentsListToBeDeleted, cancellationToken);

Example to Understand BulkDelete Extension Method using Entity Framework

While performing the delete operation, we need to check the foreign key columns in order to avoid the runtime exception while performing the DELETE Operation. So, before proceeding, please execute the following SQL Statements. In real-time applications, before deleting the Main Entity, you have to first delete the Child Entity.

DELETE FROM StudentAddress WHERE StudentId > 4;
DELETE FROM StudentCourse WHERE StudentId > 4;

In the below example, first, we fetch all the students where StduentId > 4 and then we delete the retrieved student using the BulkDelete Extension Method.

using System;
using System.Linq;
namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("BulkDelete Method Started");
            BulkDelete();
            Console.WriteLine("BulkDelete Method Completed");
            Console.Read();
        }

        public static void BulkDelete()
        {
            using (var context = new EF_Demo_DBEntities())
            {
                //Fetch all the students from the database where StudentId > 4
                var StudentsListToBeDeleted = context.Students.Where(std => std.StudentId > 4);

                //To See the Generated SQL By the BulkDelete Extension Method
                context.Database.Log = Console.Write;

                // Call the BulkDelete Extension Method to perform the Bulk Delete Operation
                context.BulkDelete(StudentsListToBeDeleted);
            }
        }
    }
}

When you run the above code, you will get the following output. As you can see, it is using Temporary tables, SqlBulkCopy, and the DELETE command to perform the bulk DELETE Operation which will improve the application performance.

BulkDelete Method Started
Opened connection at 12-12-2022 21:47:53 +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].[StudentId] > 4
-- Executing at 12-12-2022 21:47:53 +05:30
-- Completed in 3 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:47:53
-- Completed at 12-12-2022 21:47:54
-- Result: SqlDataReader


-- Executing Command:
CREATE TABLE #ZZZProjects_da711a95_2689_45dc_ab88_0ba938a33f00 ( [StudentId] [sys].[int] NULL, ZZZ_Index [INT] NOT NULL )

CREATE CLUSTERED INDEX [INDEX_#ZZZProjects_da711a95_2689_45dc_ab88_0ba938a33f00] ON #ZZZProjects_da711a95_2689_45dc_ab88_0ba938a33f00 (ZZZ_Index ASC);
-- CommandTimeout:120
-- Executing at 12-12-2022 21:47:54
-- Completed at 12-12-2022 21:47:54
-- Result: -1


SqlBulkCopy: #ZZZProjects_da711a95_2689_45dc_ab88_0ba938a33f00
-- BulkCopyTimeout:120
Executing at 12-12-2022 21:47:54
Completed at 12-12-2022 21:47:54


-- Executing Command:
DELETE DestinationTable
FROM [dbo].[Student] AS DestinationTable
INNER JOIN (SELECT TOP 100 PERCENT * FROM #ZZZProjects_da711a95_2689_45dc_ab88_0ba938a33f00
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: 3023 (Type = Int32, Size = 0)
-- CommandTimeout:120
-- Executing at 12-12-2022 21:47:54
-- Completed at 12-12-2022 21:47:54
-- Result: 3024


-- Executing Command:
DROP TABLE #ZZZProjects_da711a95_2689_45dc_ab88_0ba938a33f00;
-- CommandTimeout:120
-- Executing at 12-12-2022 21:47:54
-- Completed at 12-12-2022 21:47:54
-- Result: -1


Closed connection at 12-12-2022 21:47:54 +05:30
BulkDelete Method Completed

Why BulkDelete is Faster than SaveChanges Method in Entity Framework?

It is a common scenario, in real-time applications to perform a BULK DELETE operation.

The SaveChanges method makes more time to perform the BULK DELETE due to the number of database round-trips. The SaveChanges performs one database round-trip for every entity to delete. So, if you need to delete 10,000 entities, 10,000 database round-trips will be performed which is slow down your application performance.

The BulkDelete Extension Method requires a smaller number of database round-trips to perform the Bulk Delete Operation. For example, in SQL Server, a SqlBulkCopy is performed first in a temporary table, then a DELETE from the temporary table to the destination table is performed which is the fastest way available.

Performance Comparison Between SaveChanges and BulkDelete Extension Method:

Let us see the performance benchmark between the SaveChanges Method and BulkDelete 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 delete the same 1000 records using SaveChanges method and then again, we are inserting 1000 students into the student table and then deleting those 1000 records using BulkDelete Extension Method. Here, we are also printing 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)
        {
            Stopwatch SaveChangesStopwatch = new Stopwatch();
            Stopwatch BulkDeleteStopwatch = new Stopwatch();

            //Bulk DELETE using SaveChanges
            using (var context = new EF_Demo_DBEntities())
            {
                //First Add 1000 Students
                AddStudents(1000);

                //First Fetch the Students to be Deleted
                var StudentListToBeDeleted = context.Students.Where(std => std.StudentId > 4).ToList();
                int StudentCount = StudentListToBeDeleted.Count;

                //Then Call the Remove Range method which will mark the Entity State as Deleted
                context.Students.RemoveRange(StudentListToBeDeleted);
                
                //Start the StopWatch
                SaveChangesStopwatch.Start();

                //Call the SaveChanges Method to Delete the Data in the Database
                context.SaveChanges();
                SaveChangesStopwatch.Stop();

                Console.WriteLine($"SaveChanges, Entities : {StudentCount}, Performance : {SaveChangesStopwatch.ElapsedMilliseconds} MS");
            }

            //Bulk DELETE using BulkDelete Extension Method
            using (var context = new EF_Demo_DBEntities())
            {
                //First Add 1000 Students
                AddStudents(1000);

                //First Fetch the Students to be Deleted
                var StudentListToBeDeleted = context.Students.Where(std => std.StudentId > 4).ToList();
                int StudentCount = StudentListToBeDeleted.Count;

                BulkDeleteStopwatch.Start();

                //Call the BulkDelete Method to Delete the Data in the Database
                context.BulkDelete(StudentListToBeDeleted);

                BulkDeleteStopwatch.Stop();
                Console.WriteLine($"BulkUpdate, Entities : {StudentCount}, Performance : {BulkDeleteStopwatch.ElapsedMilliseconds} MS");
            }

            Console.Read();
        }

        public static void AddStudents(int count)
        {
            //Add Students for Performance Testing
            var stduentsList = GenerateStudents(count);

            // Bulk Insert 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 228 MS time to delete 1000 records from the database while the BulkDelete Extension Method took only 102 MS to delete the same 1000 records from the database. So, clearly, there is a huge performance difference between both approaches. So, in real-time applications, while performing BULK DELETE, you should avoid using the SaveChanges method if performance is the major factor and you should go with the BulkDelete Extension method.

Advantages of BulkDelete Extension Method in Entity Framework:

1. Easy to use, simply call the BulkDelete Method on the context object by passing the list of entities that you want to delete from the database.
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.

In the next article, I am going to discuss How to use Stored Procedures in Entity Framework with Examples. Here, in this article, I try to explain the BulkDeelete Extension method in Entity Framework with Examples. I hope you enjoy this BulkDelete Extension method in the Entity Framework article.