BulkInsert in Entity Framework

BulkInsert Extension Method in Entity Framework

In this article, I am going to discuss BulkInsert Extension Method in Entity Framework using Z.EntityFramework.Extensions with Examples. Please read our previous article where we discussed Bulk Insert, Update, and Delete in Entity Framework. At the end of this article, you will understand how to bulk insert using the BulkInsert extension method in Entity Framework with Examples.

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.

Installing Z.EntityFramework.Extensions:

In this article, I am going to show you how to use Z.EntityFramework.Extensions package and perform Bulk Insert, Update, and Delete Operations. First, open the NuGet Package Manager window and search Z.EntityFramework.Extensions. 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.

Installing Z.EntityFramework.Extensions

Once you click on the Install button it will take some time and install Z.EntityFramework.Extensions DLL into your project.

Note: Entity Framework Extensions extends your DbContext with high-performance bulk operations such as BulkSaveChanges, BulkInsert, BulkUpdate, BulkDelete, BulkMerge, and more. It supports SQL Server, MySQL, Oracle, PostgreSQL, SQLite, and more!

Advantages of using Entity Framework Extensions:
  1. Easy to use
  2. Flexible
  3. Increase performance
  4. Increase application responsiveness
  5. Reduce database load
  6. Reduce database round-trips
BulkInsert Extension Method:

The BulkInsert and BulkInsertAync methods extend our DbContext which allows us to insert a large number of entities into the database with one go. The syntax to use the Bulk Insert extension method as follows:

context.BulkInsert(listStudents);
context.BulkInsertAsync(listStudents, cancellationToken);

Example: BulkInsert example using Entity Framework Extensions
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 },
            };
            using (var context = new EF_Demo_DBEntities())
            {
                // Easy to use
                context.BulkInsert(newStudents);
            }
            Console.WriteLine("BulkInsert Method Completed");
            GetStudents("Taylor");
            Console.Read();
        }

        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:

BulkInsert Extension Method in Entity Framework

Performance Comparison Between SaveChanges and BulkInsert in Entity Framework:

Let see the performance benchmark between the saveChanges and the BulkInsert method. We are going to insert the same 1000 students using both the approach and will see the time taken to complete the execution. For better understanding please have a look at the following example. In the below example, don’t consider the FirstTimeExecution method for performance testing as we know when we execute something for the first time it will take some more time.

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

namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            //Don't consider below for performance testing
            FirstTimeExecution();

            // Generate 1000 Students
            var studentList = GenerateStudents(1000);
            var clockSaveChanges = new Stopwatch();
            var clockBulkInsert = new Stopwatch();

            using (var context = new EF_Demo_DBEntities())
            {
                // SaveChanges
                context.Students.AddRange(studentList);
                clockSaveChanges.Start();
                context.SaveChanges();
                clockSaveChanges.Stop();
                Console.WriteLine($"SaveChanges (EF), Entities : {studentList.Count}, Performance : {clockSaveChanges.ElapsedMilliseconds} ms");
            }

            using (var context = new EF_Demo_DBEntities())
            {
                // BulkInsert
                clockBulkInsert.Start();
                context.BulkInsert(studentList, options => options.AutoMapOutputDirection = false); // performance can be improved with options
                clockBulkInsert.Stop();
                Console.WriteLine($"BulkInsert (EF Extensions), Entities : {studentList.Count}, Performance : {clockBulkInsert.ElapsedMilliseconds} ms");
            }

            Console.Read();
        }

        public static void FirstTimeExecution()
        {
            var stduentsList = GenerateStudents(20);

            // SaveChanges
            using (var context = new EF_Demo_DBEntities())
            {
                context.Students.AddRange(stduentsList);
                context.SaveChanges();
                // Clean
                context.BulkDelete(stduentsList);
            }

            // BulkInsert
            using (var context = new EF_Demo_DBEntities())
            {
                context.BulkInsert(stduentsList, options => options.AutoMapOutputDirection = false);
                // Clean
                context.BulkDelete(stduentsList);
            }
        }

        public static List<Student> GenerateStudents(int count)
        {
            var list = new List<Student>();

            for (int i = 0; i < count; i++)
            {
                list.Add(new Student() { FirstName = "FirstName_" + i, LastName = "LastName_" + i, StandardId = 1 });
            }

            return list;
        }
    }
}

Output: You can see in the below output, the SaveChanges method took 647 ms time to insert 1000 rows into the database while the BulkInsert extension method took only 15 ms to insert the same 1000n records into the database.

Performance Comparison Between SaveChanges and BulkInsert in Entity Framework

Note: A lot of factors need to be considered which affect the benchmark time such as index, column type, latency, throttling, etc.

Why BulkInsert Extension Method is faster than SaveChanges?

Inserting 1000 entities for an initial load or a file importation is a typical scenario. The SaveChanges method makes it quite slow/impossible to handle this kind of situation due to the number of databases round-trips required. The SaveChanges perform one database round-trip for every entity to insert. So, if you need to insert 10,000 entities, 10,000 database round-trips will be performed which is make it slow.

The BulkInsert in counterpart requires the minimum number of database round-trips possible. For example, under the hood for SQL Server, a SqlBulkCopy is performed to insert 10,000 entities which is the fastest way available.

BulkInsert Extension Method with Options in Entity Framework:

The options parameter in the BulkInsert extension method allows us to use a lambda expression to customize the way entities are going to be inserted. In the following example, we use the Option parameter to set the Batch Size.

using System;
using System.Collections.Generic;

namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            var studentList = GenerateStudents(10000);
            
            using (var context = new EF_Demo_DBEntities())
            {
                context.BulkInsert(studentList, options => {
                    options.BatchSize = 50;
                });
            }

            Console.Read();
        }

        public static List<Student> GenerateStudents(int count)
        {
            var list = new List<Student>();

            for (int i = 0; i < count; i++)
            {
                list.Add(new Student() { FirstName = "FirstName_" + i, LastName = "LastName_" + i, StandardId = 1 });
            }

            return list;
        }
    }
}
Insert and keep identity value

In real-time applications, your entity has an identity property i.e. auto-incremented property. But for some reason, you want to insert a specific value instead of the auto-incremented value. You can do the same using the InsertKeepIdentity option which allows you to keep the identity value of your entity.

using System;
using System.Collections.Generic;

namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            var studentList = GenerateStudents(5);
            
            using (var context = new EF_Demo_DBEntities())
            {
                context.BulkInsert(studentList, options => options.InsertKeepIdentity = true);
            }

            Console.Read();
        }

        public static List<Student> GenerateStudents(int count)
        {
            var list = new List<Student>();

            for (int i = 0; i < count; i++)
            {
                list.Add(new Student() { StudentId = 100 + i, FirstName = "FirstName_" + i, LastName = "LastName_" + i, StandardId = 1 });
            }

            return list;
        }
    }
}

Insert only if the entity does not already exist

In real-time scenarios, you want to insert entities in the database only if those entities do not exist in the database.

  1. InsertIfNotExists: This option allows us to insert entities that don’t already exist.
  2. PrimaryKeyExpression: This option allows us to customize the key to use to check if the entity already exists or not.
Example:
using System;
using System.Collections.Generic;

namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            var studentList = GenerateStudents(3);
            using (var context = new EF_Demo_DBEntities())
            {
                context.BulkInsert(studentList);
            }

            studentList = GenerateStudents(5);
            using (var context = new EF_Demo_DBEntities())
            {
                // Customer "FirstName__0", "FirstName__1", "FirstName__2" already exists
                // Customer "FirstName__3", "FirstName__4" are added
                context.BulkInsert(studentList, options => {
                    options.InsertIfNotExists = true;
                    options.ColumnPrimaryKeyExpression = c => c.FirstName;
                });
            }

            Console.ReadKey();
        }

        public static List<Student> GenerateStudents(int count)
        {
            var list = new List<Student>();
            for (int i = 0; i < count; i++)
            {
                list.Add(new Student() { StudentId = 100 + i, FirstName = "FirstName_" + i, LastName = "LastName_" + i, StandardId = 1 });
            }
            return list;
        }
    }
}
Insert with related child entities (Include Graph)

It is a common scenario in real-time applications to insert entities along with their related child entities automatically.

  1. IncludeGraph: This option allows us to automatically insert all entities part of the graph.
  2. IncludeGraphBuilder: This option allows us to customize how to insert entities for a specific type.
Example:
using System;
using System.Collections.Generic;

namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            var studentList = GenerateStudents(3);
            using (var context = new EF_Demo_DBEntities())
            {
                context.BulkInsert(studentList, options => options.IncludeGraph = true);
            }
            Console.ReadKey();
        }

        public static List<Student> GenerateStudents(int count)
        {
            var list = new List<Student>();
            for (int i = 0; i < count; i++)
            {
                var student = new Student() { FirstName = "FirstName_" + i, LastName = "LastName_" + i, StandardId = 1 };

                StudentAddress studentAddress = new StudentAddress()
                {
                    StudentId = student.StudentId,
                    Address1 = "Address1" + i,
                    Address2 = "Address2" + i
                };

                student.StudentAddress = studentAddress;
                list.Add(student);
            }
            return list;
        }
    }
}

In the next article, I am going to discuss the BulkUpdate Extension method in Entity Framework with Examples. Here, in this article, I try to explain the BulkInsert Extension method in Entity Framework with Examples. I hope you enjoy this BulkInsert Extension method in the Entity Framework with Examples article.

Leave a Reply

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