Bulk Insert, Update and Delete in Entity Framework

Bulk Insert, Update and Delete in Entity Framework

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

Note: 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.

Bulk Insert Example:

In the following example, first, we create a student collection with three students, and these students we want to insert into the database with one go. Then we pass this student collection to the BulkInsert method and this method uses the AddRange method to insert all the students with one go. The AddRange method attaches the student entities to the context object with Added state and when we call the SaveChanges method, the student entities are added to the database.

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

namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("BulkInsert Method Started");
            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);
            Console.WriteLine("BulkInsert Method Completed");
            GetStudents("Taylor");
            Console.Read();
        }

        public static void BulkInsert(IList<Student> newStudents)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                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}");
                }
            }
        }
    }
}

Output:

Bulk Insert, Update and Delete in Entity Framework

Bulk Update in Entity Framework:

In the bulk update, first, we need to pull all the records which are needed to be updated and then update the properties one by one and finally call the SaveChanges() method to save all changes. The following example does the same. In the BulkUpdate method, first, it fetches all the records whose LastName is Taylor and then updates the FirstName and StandardId, once it updates the properties then it calls the SaveChanges method to save updated data into 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())
            {
                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}");
                }
            }
        }
    }
}

Output:

Bulk Delete in Entity Framework:

Entity Framework 6 introduced DbSet.RemoveRange() method to remove a collection of entities in one go. What basically the RemoveRange() method does is, it 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.

using System;
using System.Linq;

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

        public static void BulkDelete(string LastName)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                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}");
                    }
                }
            }
        }
    }
}

Output:

blank

The examples we discussed in this article are looks pretty and but the problem is, it will generate SQL insert, update, and delete script for each record and it is a known problem in Entity framework for bulk operation (Insert, Update or Delete) and at this time Microsoft does not have any recommended solution instead of using third party entity framework 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.

Leave a Reply

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