Bulk Insert, Update and Delete in Entity Framework

Back to: Entity Framework Tutorials For Begineers and Professionals

Bulk Insert, Update and Delete in Entity Framework

In this article, I am going to discuss Bulk Insert, Update, and Delete in Entity Framework with Examples. Please read our previous article where we discussed Asynchronous Programming with Entity Framework with Examples. At the end of this article, you will understand how to insert, update, and delete multiple entities in Entity Framework. 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.

Bulk Insert in Entity Framework:

Entity Framework 6 introduced DbSet.AddRange() method to add a collection of entities in one go. What basically the AddRange() method does is, it attaches a collection of entities to the context object with Added state and when we call the SaveChanges method, it will execute the INSERT SQL Command in the database for all the entities.

Entity Framework Bulk Insert Example:

In the following example, first, we create a student collection with three students, and we want to insert these three students into the database. So, we pass this student collection to the BulkInsert method. And BulkInsert method uses the DbSet.AddRange method to attach all the student entities with the Added State in the context object. And when we call the SaveChanges method, the Entity Framework will generate and execute the INSERT SQL Statement into the database for each student entity.

using System;
using System.Collections.Generic;
using System.Linq;

namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            IList<Student> newStudents = new List<Student>() {
                new Student() { FirstName = "John", LastName = "Taylor", StandardId = 1 },
                new Student() { FirstName = "Sara", LastName = "Taylor", StandardId = 1 },
                new Student() { FirstName = "Pam", LastName= "Taylor", StandardId = 1 },
            };
            BulkInsert(newStudents);
            GetStudents("Taylor");
            Console.Read();
        }

        public static void BulkInsert(IList<Student> newStudents)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                context.Database.Log = Console.Write;
                context.Students.AddRange(newStudents);
                context.SaveChanges();
            }
        }

        public static void GetStudents(string LastName)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                var studentsList = context.Students.Where(std => std.LastName.Equals(LastName, StringComparison.InvariantCultureIgnoreCase));
                foreach (var std in studentsList)
                {
                    Console.WriteLine($"FirstName : {std.FirstName}, LastName : {std.LastName}, StandardId : {std.StandardId}");
                }
            }
        }
    }
}

Now, run the above code and you will get the following output. 

Even though we are using the AddRange method, as you can see in the above output, to insert three student entities it is generating and executing three INSERT SQL Statements. That means the number of round trips is equal to the number of entities to be inserted into the database. In our next article, we will discuss how we can reduce the number of round trips. That is using a single round trip, how we can execute all the INSERT statements to improve the application performance.

Bulk Update in Entity Framework:

In the bulk UPDATE, first, we need to fetch all the records from the database which 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. And when we call the SaveChanges() method, then the Entity Framework will generate the UPDATE SQL Statement to save the changes into the database. The following example exactly does the same. In the BulkUpdate method, first, it fetches all the records whose LastName is Taylor and then updates the FirstName and StandardId properties values. Once we update the FirstName and StandardId properties then we call the SaveChanges method to save updated data into the database. In this case, the Entity Framework generate and execute separate SQL UPDATE Statement for each entity in the database.

using System;
using System.Linq;

namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("BulkUpdate Method Started");
            BulkUpdate("Taylor");
            Console.WriteLine("BulkUpdate Method Completed");
            GetStudents("Taylor");
            Console.Read();
        }

        public static void BulkUpdate(string LastName)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                context.Database.Log = Console.Write;
                var studentsList = context.Students.Where(std => std.LastName.Equals(LastName, StringComparison.InvariantCultureIgnoreCase));
                foreach (var std in studentsList)
                {
                    std.FirstName = "FirstName Changed";
                    std.StandardId = 2;
                }
                context.SaveChanges();
            }
        }

        public static void GetStudents(string LastName)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                var studentsList = context.Students.Where(std => std.LastName.Equals(LastName, StringComparison.InvariantCultureIgnoreCase));
                foreach(var std in studentsList)
                {
                    Console.WriteLine($"FirstName : {std.FirstName}, LastName : {std.LastName}, StandardId : {std.StandardId}");
                }
            }
        }
    }
}

Now, run the above code and you will get the following output. 

As you can see in the above output, to update three student entities, it is generating and executing three UPDATE SQL Statements. That means the number of round trips is equal to the number of entities to be updated in the database. In our upcoming article, we will discuss how we can reduce the number of round trips when performing the UPDATE operations. That is using a single round trip, how we can execute all the UPDATE SQL statements in the database to improve the application performance.

Bulk Delete in Entity Framework:

Entity Framework 6 introduced DbSet.RemoveRange() method to perform bulk delete in Entity Framework. What basically the RemoveRange() method does is attaches 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 Command in the database for all the entities.

For a better understanding, please have a look at the following example. In the below example, we are fetching all the students from the Student database table whose last name is Taylor and storing the retrieved student list studentsList collection variable. Then passing that list of students to the RemoveRange method will mark the Entity State of all those students as Deleted. And when we call the SaveChanges method, it will generate and execute separate DELETE SQL Statements into the database for each entity.

using System;
using System.Linq;

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

        public static void BulkDelete(string LastName)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                context.Database.Log = Console.Write;
                var studentsList = context.Students.Where(std => std.LastName.Equals(LastName, StringComparison.InvariantCultureIgnoreCase));
                context.Students.RemoveRange(studentsList);
                context.SaveChanges();
            }
        }

        public static void GetStudents(string LastName)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                var studentsList = context.Students.Where(std => std.LastName.Equals(LastName, StringComparison.InvariantCultureIgnoreCase)).ToList();
                if (studentsList.Count() <= 0)
                {
                    Console.WriteLine($"No Records Found with the LastName : {LastName}");
                }
                else
                {
                    foreach (var std in studentsList)
                    {
                        Console.WriteLine($"FirstName : {std.FirstName}, LastName : {std.LastName}, StandardId : {std.StandardId}");
                    }
                }
            }
        }
    }
}

Now, run the above code and you will get the following output. 

Even though we are using the RemoveRange method, as you can see in the above output, to delete three student entities it is generating and executing three DELETE SQL Statements. That means the number of round trips is equal to the number of entities to be deleted from the database. In our upcoming article, we will discuss how we can reduce the number of round trips. That is using a single round trip, how we can execute all the DELETE SQL statements to improve the application performance.

So, if you notice, the three examples that we discussed look pretty and simple. But the problem is, they will generate INSERT, UPDATE, and DELETE SQL Statement for each entity based on the Entity State and this is a known problem in Entity framework for bulk (INSERT, UPDATE, and DELETE) operations and at this time Microsoft does not have any recommended solution instead of using third party libraries and that we will discuss in our next article.

Note: EF Core improves the performance of AddRange and RemoveRange methods by executing the INSERT and DELETE commands for all entities in a single database round trip.

In the next article, I am going to discuss the BulkInsert Extension Method in Entity Framework. Here, in this article, I try to explain Bulk Insert, Update and Delete using Entity Framework and I hope you enjoyed this Bulk Insert, Update and Delete in Entity Framework article. Please give your valuable feedback and suggestions about this article.