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:
- 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.
- 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.
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.
Registration Open For New Online Training
Enhance Your Professional Journey with Our Upcoming Live Session. For complete information on Registration, Course Details, Syllabus, and to get the Zoom Credentials to attend the free live Demo Sessions, please click on the below links.