Loading


Microsoft Entity Framework

  • Home
  • Microsoft Entity Framework
  • How to perform BulkInsert Extension Method in Entity Framework?. The Complete Microsoft Entity Framework Developer Course 2024.
Microsoft Entity Framework

How to perform BulkInsert Extension Method in Entity Framework?. The Complete Microsoft Entity Framework Developer Course 2023 [Videos].

By admin / 11-Feb-2022 / in Blog, Microsoft Entity Framework / 449 Comments / 37 Likes / Share This Post

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 Video. Please read our introduction to Entity Framework Database First Video before proceeding to this Video.

Installing Z.EntityFramework.Extensions:

In this Video, 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, dont 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)
{
//Dont 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 dont 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;
}
}
}

Read more
See All

Comments (449 Comments)

Submit Your Comment

See All Posts

Related Posts

Microsoft Entity Framework / Audio Video

How we can install sql server and create db?

How we can install sql server and create db?
24-aug-2024 /37 /449

Microsoft Entity Framework / Blog

What is the Entity Framework?

Entity Framework is an Open-Source Object-Relational Mapping (ORM) Framework for .NET applications that enables .NET developers to work with relational data using domain-specific objects without focusing on the underlying database tables and columns where actually the data is stored. That means the Entity Framework eliminates the need for writing the data-access code that developers usually need to write.
11-Feb-2022 /37 /449

Microsoft Entity Framework / Blog

What is Entity Framework Architecture?

In this article, I am going to discuss the Entity Framework Architecture in Detail. Please read our previous article where we discussed the Overview of the Entity Framework. At the end of this article, you will understand the following pointers in detail.
11-Feb-2022 /37 /449