Bulk Operations in Entity Framework Core

Back to: ASP.NET Core Tutorials For Beginners and Professionals

Bulk Operations in Entity Framework Core

In this article, I will discuss Bulk Operations (Bulk Insert, Bulk Update, and Bulk Delete) in Entity Framework Core (EF Core) with Examples. Please read our previous article discussing Property Configuration using Entity Framework Core Fluent API with Examples.

Bulk Insert, Update, and Delete Operations in Entity Framework Core

Bulk Operations in Entity Framework Core refers to the ability to process large numbers of records efficiently, such as inserting, updating, or deleting many rows in a single operation. While Entity Framework Core excels in many areas, it is not optimized for bulk operations out of the box. Each insert, update, or delete command is sent to the database individually, which can lead to performance issues when dealing with large datasets.

In Entity Framework Core (EF Core), we can perform bulk INSERT, UPDATE, and DELETE operations using various techniques. EF Core does not provide built-in support for bulk operations like other ORMs, but we can implement these operations efficiently by utilizing different methods and libraries. 

Examples to Understand Bulk Insert, Update, and Delete Operations in Entity Framework Core

To understand How to Perform Bulk Operations using Entity Framework Core (EF Core), we will use the following Student Entity. So, create a class file named Student.cs and copy and paste the following code.

namespace EFCoreCodeFirstDemo.Entities
{
    public class Student
    {
        public int StudentId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Branch { get; set; }
    }
}

Next, modify the Context class as follows:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //To Display the Generated SQL
            optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);

            //Configuring the Connection String
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB;Trusted_Connection=True;TrustServerCertificate=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }

        public DbSet<Student> Students { get; set; }
    }
}

With the above changes, open the Package Manager Console and Execute the add-migration and update-database commands as follows. You can give any name to your migration. Here, I am giving EFCoreDBMig1. The name that you are giving it should not be given earlier.

With this, our Database with Students database table is created, as shown in the below image:

Bulk Insert in Entity Framework Core:

Bulk Insert is the process of efficiently inserting many records into a database table. In Entity Framework Core, we need to use the DbSet AddRange() or the DbContext AddRange method to add a collection of entities in one go. The AddRange() method attaches a collection of entities to the context object with the Added state. When we call the SaveChanges method, it will execute the INSERT SQL Command in the database for all the entities.

For a better understanding, please modify the Program class as follows. In the following example, within the Main method, first, we create a student collection with four students, and we want to insert these four students into the database. So, we pass this student collection to the BulkInsert method. The BulkInsert method uses the DbSet.AddRange or DbContext.AddRange method to attach all the student entities with the Added State in the context object. When we call the SaveChanges method, the Entity Framework generates and executes the INSERT SQL Statement into the database. Then, to display the Entities, we call the GetStudents method.

using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                List<Student> newStudents = new List<Student>() {
                    new Student() { FirstName = "Pranaya", LastName = "Rout", Branch= "CSE" },
                    new Student() { FirstName = "Hina", LastName = "Sharma", Branch= "CSE"  },
                    new Student() { FirstName = "Anurag", LastName= "Mohanty", Branch= "CSE" },
                    new Student() { FirstName = "Prity", LastName= "Tiwary", Branch= "ETC" }
                };

                BulkInsert(newStudents);

                //Display all the Students whose Branch is CSE
                GetStudents("CSE");

                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }

        public static void BulkInsert(IList<Student> newStudents)
        {
            using (var context = new EFCoreDbContext())
            {
                //Using DbSet AddRange Method
                context.Students.AddRange(newStudents);

                //Using DbContext AddRange Method
                //context.AddRange(newStudents);
                context.SaveChanges();
            }
        }

        public static void GetStudents(string Branch)
        {
            using (var context = new EFCoreDbContext())
            {
                //Fetch all the Students based on Branch
                var studentsList = context.Students.Where(std => std.Branch.Equals(Branch, StringComparison.InvariantCultureIgnoreCase));
                foreach (var std in studentsList)
                {
                    Console.WriteLine($"Id : {std.FirstName}, Name : {std.FirstName} {std.LastName}, Branch : {std.Branch}");
                }
            }
        }
    }
}

Output:

Note: This is one of the biggest changes they have made to the AddRange method in Entity Framework Core. Using a Single Round Trip with the database, using the SQL MERGE and INSERT Statement, they are performing the BULK Insert Operation. While the performance is better in EF Core than the old EF6, the solution becomes very inefficient when inserting thousands of entities.

Bulk Update in Entity Framework Core:

Bulk Updating is the process of efficiently updating many records into a database table. In BULK UPDATE, first, we need to fetch all the records from the database that need to be updated and then update the required properties of the entities. When we update the properties of entities, it will mark the entity state as Modified. When we call the SaveChanges() method, the Entity Framework Core will generate the UPDATE SQL Statement to update the changes in the database.

For a better understanding, please modify the Program class as follows: In the BulkUpdate method, first, we fetch all the students whose Branch is CSE and then update the FirstName and LastName properties. Once we update the FirstName and LastName properties, we call the SaveChanges method to update the data in the database. In this case, the EF Core will generate and execute separate SQL UPDATE Statements in the database.

using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Update all the Students whose Branch is CSE
                BulkUpdate("CSE");

                //Display all the Students whose Branch is CSE
                GetStudents("CSE");

                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }

        public static void BulkUpdate(string Branch)
        {
            using (var context = new EFCoreDbContext())
            {
                //Fetch all the Students Based on Branch
                var studentsList = context.Students.Where(std => std.Branch == Branch).ToList();

                //Update the First Name and Last Name
                foreach (var std in studentsList)
                {
                    std.FirstName += "Changed";
                    std.LastName += "Changed";
                }

                //Update the Data Into the Database
                context.SaveChanges();
            }
        }

        public static void GetStudents(string Branch)
        {
            using (var context = new EFCoreDbContext())
            {
                //Fetch all the Students based on Branch
                var studentsList = context.Students.Where(std => std.Branch == Branch).ToList();
                foreach (var std in studentsList)
                {
                    Console.WriteLine($"Id : {std.FirstName}, Name : {std.FirstName} {std.LastName}, Branch : {std.Branch}");
                }
            }
        }
    }
}

Output:

As you can see, using a single database connection, or you can say using a single database round trip, it executes the three update statements in the database. So, it will perform better than EF6, but it is not optimized. In our next article, I will show you how to perform the BULK UPDATE operations in an optimized manner.

Note: By looking at the three UPDATE SQL Statements, please don’t think it makes three database round trips. This is happening in Entity Framework, not in Entity Framework Core. In EF Core, they have improved the performance at some level by sending all these three statements in a single database round trip. The problem has occurred when you are going to update thousands of records.

Bulk Delete in Entity Framework Core:

Bulk deleting involves removing a large number of records efficiently. In Entity Framework Core, we can use the DbSet RemoveRange() method or the DbContext RemoveRange() method to perform bulk delete operations. What the RemoveRange() method does is attach a collection of entities to the context object with the Deleted state, and when we call the SaveChanges method, it will execute the DELETE SQL Statement in the database for all the entities.

For a better understanding, please modify the Program class as follows. In the below example, we are fetching all the students from the Student database table where the Branch is CSE and storing them in studentsList collection variable. Then, pass that list of students to the RemoveRange method, marking the Entity State of all those students as Deleted. When we call the SaveChanges method, it will generate and execute separate DELETE SQL Statements into the database for each entity.

using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //Delete all the Students whose Branch is CSE
                BulkDelete("CSE");

                //Display all the Students whose Branch is CSE
                //If Delete Operation Successful, then it should not display any data 
                GetStudents("CSE");

                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }

        public static void BulkDelete(string Branch)
        {
            using var context = new EFCoreDbContext();

            //Fetch all the Students Based on Branch
            var studentsList = context.Students.Where(std => std.Branch == Branch).ToList();

            //Mark all the Students Entity State as Deleted
            context.Students.RemoveRange(studentsList);

            //Delete All those Entities from the Database
            context.SaveChanges();
        }

        public static void GetStudents(string Branch)
        {
            using (var context = new EFCoreDbContext())
            {
                //Fetch all the Students based on Branch
                var studentsList = context.Students.Where(std => std.Branch == Branch).ToList();

                if (studentsList.Count > 0 )
                {
                    foreach (var std in studentsList)
                    {
                        Console.WriteLine($"Id : {std.FirstName}, Name : {std.FirstName} {std.LastName}, Branch : {std.Branch}");
                    }
                }
                else
                {
                    Console.WriteLine($"No Records Found with the Branch : {Branch}");
                }
                
            }
        }
    }
}

Output:

Similar to Bulk Update Operation, in this case, it executes the three DELETE SQL statements in the database using a single database round trip. That means it will also perform better than EF6 but is not optimized. In our next article, I will show you how to perform the BULK UPDATE operations in an optimized manner.

Note: By looking at the three DELETE SQL Statements, please don’t think it makes three database round trips. This is happening in Entity Framework, not in Entity Framework Core. In EF Core, they have improved the performance at some level by sending all these three statements in a single database round trip. The problem occurs when you are going to delete thousands of records.

While the performance is better in EF Core than the old EF6, the solution becomes very inefficient when inserting thousands of entities. The fastest way of inserting multiple data is by using the Entity Framework Extensions third-party library. Depending on the provider, performance can be increased by up to 50x faster and more.

In the next article, I will discuss Bulk Operations using Entity Framework Core Extension with Examples for Performance Improvement. In this article, I try to explain Bulk Operations (INSERT, UPDATE, and DELETE) using Entity Framework Core with Examples. I hope you enjoyed this Bulk Operations ( BULK INSERT, BULK UPDATE, and BULK DELETE) using Entity Framework Core article.