Back to: Entity Framework Tutorials For Begineers and Professionals
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 Operations in Entity Framework with Examples. At the end of this article, you will understand how to perform bulk insert using the BulkInsert extension method 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.
Installing Z.EntityFramework.Extensions:
Now, I am going to show you how to use Z.EntityFramework.Extensions package and perform Bulk Insert, Update, and Delete Operations with Entity Framework. First, open the NuGet Package Manager Console window and search Z.EntityFramework.Extensions package. 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.
Once you click on the Install button it will take some time and install Z.EntityFramework.Extensions DLL into your project.
Note: This Z.EntityFramework.Extensions extend our DbContext object 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:
- Easy to use.
- Flexible.
- Increase Performance.
- Increase Application Responsiveness.
- Reduce Database Load by Reducing the number of database round-trips.
BulkInsert Extension Method:
The Z.EntityFramework.Extensions provide two methods i.e. BulkInsert and BulkInsertAync 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 is as follows:
context.BulkInsert(listStudents);
context.BulkInsertAsync(listStudents, cancellationToken);
Example to Understand BulkInsert Method using Entity Framework Extensions
In the below example, we are Inserting the Student Lists i.e. newStudents to the database using BulkInsert Extension Method. Here, we don’t need to call the SaveChanges method while performing the Bulk Insert Operations. In this case, using a single round trip, the context class going to perform the INSERT operation.
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())
{
context.Database.Log = Console.Write;
// 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}");
}
}
}
}
}
When you run the above code, you will get the following output. Please observe the SQL Query carefully and you will see that it is using SQL Merge to perform the BULK operations. As it reduces the number of round trips to the database server, so, drastically it improves the application performance.
BulkInsert Method Started
Opened connection at 12-12-2022 19:45:45 +05:30
-- Executing Command:
/* SELECT server information */
SELECT @@VERSION
/* SELECT table information */
SELECT DestinationTable.Name AS DestinationName ,
( SELECT 1
WHERE EXISTS ( SELECT 1
FROM sys.triggers AS X
WHERE X.parent_id = A.object_id
AND X.is_disabled = 0
AND OBJECTPROPERTY(X.object_id,
'ExecIsInsertTrigger') = 1 )
) AS HasInsertTrigger ,
( SELECT 1
WHERE EXISTS ( SELECT 1
FROM sys.triggers AS X
WHERE X.parent_id = A.object_id
AND X.is_disabled = 0
AND OBJECTPROPERTY(X.object_id,
'ExecIsUpdateTrigger') = 1 )
) AS HasUpdateTrigger ,
( SELECT 1
WHERE EXISTS ( SELECT 1
FROM sys.triggers AS X
WHERE X.parent_id = A.object_id
AND X.is_disabled = 0
AND OBJECTPROPERTY(X.object_id,
'ExecIsDeleteTrigger') = 1 )
) AS HasDeleteTrigger
FROM (SELECT @Table_0 AS Name) AS DestinationTable
LEFT JOIN sys.synonyms AS B ON B.object_id = OBJECT_ID(DestinationTable.Name)
AND COALESCE(PARSENAME(base_object_name,4), @@SERVERNAME) = @@SERVERNAME
AND COALESCE(PARSENAME(base_object_name,3), DB_NAME(DB_ID())) = DB_NAME(DB_ID())
INNER JOIN sys.tables AS A ON A.object_id = OBJECT_ID(DestinationTable.Name)
OR A.object_id = OBJECT_ID(B.base_object_name)
ORDER BY DestinationName
/* SELECT column information */
SELECT DestinationTable.Name AS DestinationName ,
C.name AS ColumnName ,
C.column_id AS ColumnOrder ,
C.precision AS Precision ,
C.scale AS Scale ,
C.max_length AS MaxLength ,
C.collation_name AS Collation ,
C.Is_Identity AS IsIdentity ,
( CASE WHEN EXISTS ( SELECT 1
FROM sys.index_columns AS X
WHERE X.index_id = B.index_id
AND X.object_id = B.object_id
AND X.column_id = C.column_id ) THEN 1
ELSE 0
END ) AS IsPrimaryKey ,
C.system_type_id AS System_Type_Id ,
D.Name AS TypeName,
(CASE WHEN E.base_object_name IS NOT NULL THEN 1 ELSE 0 END) AS IsSynonym,
D.is_user_defined,
F.name,
CASE WHEN C.default_object_id = 0 THEN 'ZZZ_NO_DEFAULT' ELSE ISNULL(OBJECT_DEFINITION(C.default_object_id), 'ZZZ_ERROR_DEFAULT_ZZZ') END AS DefaultValueSql,
C.is_nullable
FROM (SELECT @Table_0 AS Name) AS DestinationTable
LEFT JOIN sys.synonyms AS E ON E.object_id = OBJECT_ID(DestinationTable.Name)
AND COALESCE(PARSENAME(base_object_name,4), @@SERVERNAME) = @@SERVERNAME
AND COALESCE(PARSENAME(base_object_name,3), DB_NAME(DB_ID())) = DB_NAME(DB_ID())
INNER JOIN sys.tables AS A ON A.object_id = OBJECT_ID(DestinationTable.Name)
OR A.object_id = OBJECT_ID(E.base_object_name)
LEFT JOIN sys.indexes AS B ON B.object_id = A.object_id
AND B.is_primary_key = 1
INNER JOIN sys.columns AS C ON C.object_id = A.object_id
INNER JOIN sys.types AS D ON D.system_type_id = C.system_type_id
AND D.user_type_id = C.user_type_id
INNER JOIN sys.schemas AS F ON D.schema_id = F.schema_id
ORDER BY DestinationName ,
ColumnOrder
-- @Table_0: [dbo].[Student] (Type = String, Size = 15)
-- CommandTimeout:30
-- Executing at 12-12-2022 19:45:45
-- Completed at 12-12-2022 19:45:45
-- Result: SqlDataReader
-- Executing Command:
MERGE INTO [dbo].[Student] AS DestinationTable
USING
(
SELECT TOP 100 PERCENT * FROM (SELECT @0_0 AS [StudentId], @0_1 AS [FirstName], @0_2 AS [LastName], @0_3 AS [StandardId], @0_4 AS ZZZ_Index
UNION ALL SELECT @1_0 AS [StudentId], @1_1 AS [FirstName], @1_2 AS [LastName], @1_3 AS [StandardId], @1_4 AS ZZZ_Index
UNION ALL SELECT @2_0 AS [StudentId], @2_1 AS [FirstName], @2_2 AS [LastName], @2_3 AS [StandardId], @2_4 AS ZZZ_Index) AS StagingTable ORDER BY ZZZ_Index
) AS StagingTable
ON 1 = 2
WHEN NOT MATCHED THEN
INSERT ( [FirstName], [LastName], [StandardId] )
VALUES ( [FirstName], [LastName], [StandardId] )
OUTPUT
$action,
StagingTable.ZZZ_Index,
INSERTED.[StudentId] AS [StudentId_zzzinserted]
;
-- @0_0: 0 (Type = Int32, Size = 4)
-- @0_1: John (Type = AnsiString, Size = 100)
-- @0_2: Taylor (Type = AnsiString, Size = 100)
-- @0_3: 1 (Type = Int32, Size = 4)
-- @0_4: 0 (Type = Int32, Size = 0)
-- @1_0: 0 (Type = Int32, Size = 4)
-- @1_1: Sara (Type = AnsiString, Size = 100)
-- @1_2: Taylor (Type = AnsiString, Size = 100)
-- @1_3: 1 (Type = Int32, Size = 4)
-- @1_4: 1 (Type = Int32, Size = 0)
-- @2_0: 0 (Type = Int32, Size = 4)
-- @2_1: Pam (Type = AnsiString, Size = 100)
-- @2_2: Taylor (Type = AnsiString, Size = 100)
-- @2_3: 1 (Type = Int32, Size = 4)
-- @2_4: 2 (Type = Int32, Size = 0)
-- CommandTimeout:120
-- Executing at 12-12-2022 19:45:45
-- Completed at 12-12-2022 19:45:46
-- Result: 3 rows
Closed connection at 12-12-2022 19:45:46 +05:30
BulkInsert Method Completed
FirstName : John, LastName : Taylor, StandardId : 1
FirstName : Sara, LastName : Taylor, StandardId : 1
FirstName : Pam, LastName : Taylor, StandardId : 1
Performance Comparison Between SaveChanges and BulkInsert in Entity Framework:
Let’s see the performance benchmark between the Entity Framework SaveChanges method and the BulkInsert Extension Method with an example. As we know, the Entity Framework SaveChanges method will generate and execute separate SQL Query for each entity i.e. multiple database trips while the BulkInsert Extension Method does the same task using a single database trip.
We are going to Bulk Insert 1000 students using both approaches (i.e. AddRange Method and BulkInsert Extension Method) and will measure the time taken to complete the task by both approaches. For a 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;
namespace DBFirstApproach
{
class Program
{
static void Main(string[] args)
{
//Don't consider below for performance Testing
//This warmup
FirstTimeExecution();
// Generate 1000 Students
List<Student> studentList = GenerateStudents(1000);
Stopwatch SaveChangesStopwatch = new Stopwatch();
Stopwatch BulkInsertStopwatch = new Stopwatch();
using (EF_Demo_DBEntities context1 = new EF_Demo_DBEntities())
{
// Add the Student Collection using the AddRange Method
context1.Students.AddRange(studentList);
SaveChangesStopwatch.Start();
context1.SaveChanges();
SaveChangesStopwatch.Stop();
Console.WriteLine($"SaveChanges, Entities : {studentList.Count}, Time Taken : {SaveChangesStopwatch.ElapsedMilliseconds} MS");
}
using (EF_Demo_DBEntities context2 = new EF_Demo_DBEntities())
{
// BulkInsert
BulkInsertStopwatch.Start();
context2.BulkInsert(studentList, options => options.AutoMapOutputDirection = false); // performance can be improved with options
BulkInsertStopwatch.Stop();
Console.WriteLine($"BulkInsert, Entities : {studentList.Count}, Time Taken : {BulkInsertStopwatch.ElapsedMilliseconds} MS");
}
Console.Read();
}
public static void FirstTimeExecution()
{
List<Student> stduentsList = GenerateStudents(20);
// SaveChanges
using (var context = new EF_Demo_DBEntities())
{
context.Students.AddRange(stduentsList);
//Call the SaveChanges Method to INSERT the data into the database
context.SaveChanges();
// Delete the Newly Inserted Data
context.BulkDelete(stduentsList);
}
// BulkInsert
using (var context = new EF_Demo_DBEntities())
{
context.BulkInsert(stduentsList, options => options.AutoMapOutputDirection = false);
// Delete the Newly Inserted Data
context.BulkDelete(stduentsList);
}
}
public static List<Student> GenerateStudents(int count)
{
var listOfStudents = new List<Student>();
for (int i = 0; i < count; i++)
{
listOfStudents.Add(new Student() { FirstName = "FirstName_" + i, LastName = "LastName_" + i, StandardId = 1 });
}
return listOfStudents;
}
}
}
Now, run the above code and you will get the following output. As you can see, the SaveChanges method took 490 MS to insert 1000 entities into the database while the BulkInsert Extension method took only 20 MS to insert the same 1000 entities into the database. So, you can imagine how dangerous is Entity Framework AddRange method when performance is taken into consideration.
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 of 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 as 50. With this Batch size, now, 50 entities are going to be inserted into the database as a batch, and depending upon the number of entities multiple, the BulkInsert method will create multiple batches to insert the entities.
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 using BulkInsert Method in Entity Framework
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. For a better understanding, please have a look at the following example. Here, the Identity column value i.e. the StudentID column value that we set is going to be inserted into the database.
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 the database
In Real-Time scenarios, you want to insert entities in the database only if those entities do not exist in the database.
- InsertIfNotExists: This option allows us to insert entities that don’t already exist in the database.
- PrimaryKeyExpression: This option allows us to customize the key to check if the entity already exists or not.
For a better understanding, please have a look at the following 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 or Child Entities (Include Graph) in Entity Framework using BulkInsert
It is a common scenario in real-time applications to insert entities along with their related child entities automatically. For this, we need to use the following options.
- IncludeGraph: This option allows us to automatically insert all entities as part of the graph.
- IncludeGraphBuilder: This option allows us to customize how to insert entities for a specific type.
For a better understanding, please have a look at the following 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.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.