Back to: ASP.NET Core Tutorials For Beginners and Professionals
Bulk Operations using Entity Framework Core Extension
In this article, I will discuss Bulk Operations using Entity Framework Core Extension with Examples. Please read our previous article, discussing Bulk Insert, Update, and Delete Operations in Entity Framework Core with Examples. At the end of this article, you will understand the following pointers:
- Bulk Operations using Entity Framework Core Extension
- Installing Z.EntityFramework.Extensions.EFCore
- Example to Understand Bulk Operations using Entity Framework Core Extension
- Bulk Insert using Entity Framework Core Extension
- Performance Comparison Between SaveChanges and BulkInsert in EF Core
- Bulk Update using Entity Framework Core Extension
- Performance Comparison Between SaveChanges and BulkUpdate Extension Method
- Bulk Delete using Entity Framework Core Extension
- Performance Comparison Between SaveChanges and BulkDelete Extension Method
- Advantages of using Entity Framework Core Extension
- Disadvantages of using Entity Framework Core Extension
Bulk Operations using Entity Framework Core Extension
In Entity Framework Core (EF Core), performing bulk insert, update, and delete efficiently for many records can be challenging because EF Core does not natively support bulk insert, update, and delete operations. However, we can use third-party libraries and techniques to achieve bulk insert efficiently, update, and delete operations. One commonly used library for bulk operations in EF Core is Entity Framework Extensions (EF Extensions). Let us proceed and try to understand how to perform bulk insert, update, and delete operations using EF Extensions:
Note: Unfortunately, EF Core doesn’t have a formal API for performing any bulk operations, so to improve your performance with bulk operations, you will need to use the Entity Framework Extensions library created by ZZZ Projects.
Installing Z.EntityFramework.Extensions.EFCore:
Now, I will show you how to use Z.EntityFramework.Extensions.EFCore package and perform Bulk Insert, Update, and Delete Operations with Entity Framework Core. First, we need to install the Entity Framework Extensions (EF Extensions) NuGet package in your project.
First, open the NuGet Package Manager for Solution and search Z.EntityFramework.Extensions.EFCore package. Select Z.EntityFramework.Extensions.EFCore, select the Project, choose the latest version, and finally click the Install button, as shown in the image below.
You can also install it using the NuGet Package Manager Console as follows:
Install-Package Z.EntityFramework.Extensions.EFCore
Once you install the Z.EntityFramework.Extensions.EFCore package, you can verify the same inside the Packages folder as shown in the below image.
Entity Framework Extensions extend your DbContext with high-performance bulk operations: BulkSaveChanges, BulkInsert, BulkUpdate, BulkDelete, BulkMerge, and more. It Supports SQL Server, MySQL, Oracle, PostgreSQL, SQLite, and more. For more information, please check the below two links.
GitHub Link: https://www.nuget.org/packages/Z.EntityFramework.Extensions.EFCore/
Official Website: https://entityframework-extensions.net/bulk-extensions
Example to Understand Bulk Operations using Entity Framework Core Extension:
To understand How to Perform Bulk Operations using Entity Framework Core (EF Core), we will use the following Student Entity. So, create a class file named Student.cs and copy and paste the following code.
namespace EFCoreCodeFirstDemo.Entities
{
public class Student
{
public int StudentId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Branch { get; set; }
}
}
Next, modify the Context class as follows:
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace EFCoreCodeFirstDemo.Entities
{
public class EFCoreDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
//To Display the Generated SQL
//optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);
//Configuring the Connection String
optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB;Trusted_Connection=True;TrustServerCertificate=True;");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
}
public DbSet<Student> Students { get; set; }
}
}
With the above changes, open the Package Manager Console and Execute the add-migration and update-database commands as follows. You can give any name to your migration. Here, I am giving EFCoreDBMig1. The name that you are giving it should not be given earlier.
With this, our Database with Students database table is created, as shown in the below image:
Bulk Insert using Entity Framework Core Extension:
The Z.EntityFramework.Extensions.EFCore provides two methods, i.e., BulkInsert and BulkInsertAync, which allow us to insert many entities into the database in one go.
For a better understanding, please modify the Program class as follows: In the below example, we are Inserting the Student Lists into the database using the BulkInsert Extension Method. We don’t need to call the SaveChanges method while performing the Bulk Insert Operations. In this case, the context class will perform the INSERT operation using a single round trip using an optimized SQL query.
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//Create a List of Students
List<Student> newStudents = new List<Student>() {
new Student() { FirstName = "Pranaya", LastName = "Rout", Branch= "CSE" },
new Student() { FirstName = "Hina", LastName = "Sharma", Branch= "CSE" },
new Student() { FirstName = "Anurag", LastName= "Mohanty", Branch= "CSE" },
new Student() { FirstName = "Prity", LastName= "Tiwary", Branch= "ETC" }
};
using var context = new EFCoreDbContext();
// Performing BULK INSERT
// Easy to use. No need to call SaveChanges
context.BulkInsert(newStudents);
Console.WriteLine("BulkInsert Method Completed");
// Display all Students who are belongs to CSE Branch
GetStudents("CSE");
Console.ReadKey();
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
public static void GetStudents(string Branch)
{
using (var context = new EFCoreDbContext())
{
//Fetch all the Students based on Branch
var studentsList = context.Students.Where(std => std.Branch == Branch).ToList();
foreach (var std in studentsList)
{
Console.WriteLine($"Id : {std.FirstName}, Name : {std.FirstName} {std.LastName}, Branch : {std.Branch}");
}
}
}
}
}
Performance Comparison Between SaveChanges and BulkInsert in EF Core:
Let’s see the performance benchmark between the Entity Framework Core SaveChanges method and the BulkInsert Extension Method with an example. We will Insert 1000 students using both approaches (i.e., AddRange with SaveChanges Method and BulkInsert Extension Method). We will measure the time taken to complete the task by both approaches.
For a better understanding, please modify the Program class as follows. In the below code, please don’t consider the FirstTimeExecution method for performance testing, as we know it will take longer to execute something for the first time.
using EFCoreCodeFirstDemo.Entities;
using System.Diagnostics;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//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 (EFCoreDbContext context1 = new EFCoreDbContext())
{
// 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 (EFCoreDbContext context2 = new EFCoreDbContext())
{
// BulkInsert Extension Method
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();
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
public static void FirstTimeExecution()
{
//Generate a List of 20 Students
List<Student> stduentsList = GenerateStudents(20);
//Using AddRange and SaveChanges Method
using (var context = new EFCoreDbContext())
{
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);
}
// Using BulkInsert Extension Method
using (var context = new EFCoreDbContext())
{
//Insert the Bulk Data
context.BulkInsert(stduentsList);
// Delete the Newly Inserted Data
context.BulkDelete(stduentsList);
}
}
//This Method is going to generate list of Students
//count: The number of Students to be generated
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, Branch = "CSE" });
}
return listOfStudents;
}
}
}
Run the above code, and you will get the following output. As you can see, the SaveChanges method took 85 MS to insert 1000 entities into the database, while the BulkInsert Extension method took only 38 MS to insert 1000 entities into the database. So, you can imagine how dangerous Entity Framework Core AddRange and SaveChanges methods are when performance is considered. Many factors that affect the benchmark time need to be considered, such as index, column type, latency, throttling, etc.
Bulk Update using Entity Framework Core Extension:
The Z.EntityFramework.Extensions.EFCore provides two methods, i.e., BulkUpdate and BulkUpdateAync, to extend our DbContext object, allowing us to update many entities in the database in one go.
For a better understanding, please modify the Program class as follows: In the below example, first, we fetch all the students whose Branch is CSE and then update the First Name and Last Name, and finally update the updated data to the database using the BulkUpdate Extension Method.
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
Console.WriteLine("BulkUpdate Method Started");
BulkUpdate("CSE");
Console.WriteLine("BulkUpdate Method Completed");
Console.Read();
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
public static void BulkUpdate(string Branch)
{
using var context = new EFCoreDbContext();
//Fetch all the students whose StandardId is 1
var studentsList = context.Students.Where(std => std.Branch == Branch);
//Update the Firstname and LastName of all Stduents
foreach (var std in studentsList)
{
std.FirstName += " Changed";
std.LastName += " Changed";
}
// Call the BulkUpdate Extension Method to perform the Bulk Update Operation
context.BulkUpdate(studentsList);
}
}
}
Performance Comparison Between SaveChanges and BulkUpdate Extension Method:
Before Proceeding further, please truncate the Student table using the following syntax:
truncate table Students
With an Example, let’s see the performance benchmark between the SaveChanges and BulkUpdate Extension Method in Entity Framework Core. In the example below, first, we insert 1000 students into the student table, and then we will update the first and last names of all the students using both approaches. Here, we will also print the time taken to complete the task by both approaches.
using EFCoreCodeFirstDemo.Entities;
using System.Diagnostics;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
//First Add 1000 Students
AddStudents(1000);
Stopwatch SaveChangesStopwatch = new Stopwatch();
Stopwatch BulkUpdateStopwatch = new Stopwatch();
//Bulk Update using SaveChanges
using (var context = new EFCoreDbContext())
{
//Fetch all the Students
var studentList = context.Students.ToList();
foreach (var std in studentList)
{
std.FirstName += "_UpdateBySaveChanges";
std.LastName += "_UpdateBySaveChanges";
}
SaveChangesStopwatch.Start();
//Call the SaveChanges Method to Update the Data in the Database
context.SaveChanges();
SaveChangesStopwatch.Stop();
Console.WriteLine($"SaveChanges, Entities : {studentList.Count}, Performance : {SaveChangesStopwatch.ElapsedMilliseconds} MS");
}
//Bulk Update using BulkUpdate Extension Method
using (var context = new EFCoreDbContext())
{
//Fetch all the Students
var studentList = context.Students.ToList();
foreach (var std in studentList)
{
std.FirstName += "_UpdatedByBulkUpdate";
std.LastName += "_UpdatedByBulkUpdate";
}
BulkUpdateStopwatch.Start();
//Call the BulkUpdate Method to Update the Data in the Database
context.BulkUpdate(studentList);
BulkUpdateStopwatch.Stop();
Console.WriteLine($"BulkUpdate, Entities : {studentList.Count}, Performance : {BulkUpdateStopwatch.ElapsedMilliseconds} MS");
}
Console.Read();
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
public static void AddStudents(int count)
{
//Add 1000 Students for Performance Testing
var stduentsList = GenerateStudents(count);
// Bulk Insert using BulkInsert Extension Method
using var context = new EFCoreDbContext();
context.BulkInsert(stduentsList, options => options.AutoMapOutputDirection = false);
}
//This Method is going to generate list of Students
//count: The number of Students to be generated
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, Branch = "CSE" });
}
return listOfStudents;
}
}
}
When you run the above code, you will get the following output. As you can see, the SaveChanges method took 241 MS time to update 1000 records into the database, while the BulkUpdate Extension Method took 137 MS to update the same 1000 records. So, clearly, there is a huge performance difference between both approaches. So, in real-time applications, you should avoid using the SaveChanges method if performance is the major factor while performing BULK UPDATE.
Bulk Delete using Entity Framework Core Extension
The BulkDelete and BulkDeleteAync methods extend our DbContext object, allowing us to delete many entities into the database with a single round trip, improving the application’s performance. For a better understanding, please modify the Program class as follows: In the below example, first, we fetch all the students where the Branch is CSE, and then we delete the retrieved student using the BulkDelete Extension Method.
using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
Console.WriteLine("BulkDelete Method Started");
BulkDelete();
Console.WriteLine("BulkDelete Method Completed");
Console.Read();
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
public static void BulkDelete()
{
using (var context = new EFCoreDbContext())
{
//Fetch all the students from the database where Branch == "CSE"
var StudentsListToBeDeleted = context.Students.Where(std => std.Branch == "CSE");
// Call the BulkDelete Extension Method to perform the Bulk Delete Operation
context.BulkDelete(StudentsListToBeDeleted);
}
}
}
}
Performance Comparison Between SaveChanges and BulkDelete Extension Method:
Let us see the performance benchmark between the SaveChanges and BulkDelete Extension Method in Entity Framework Core with an Example. In the below example, first, we are inserting 1000 students into the student table. Then, we will delete the same 1000 records using the SaveChanges method. Then again, we insert 1000 students into the student table and then delete those 1000 records using the BulkDelete Extension Method. Here, we also print the time taken to complete the task by both approaches.
using EFCoreCodeFirstDemo.Entities;
using System.Diagnostics;
namespace EFCoreCodeFirstDemo
{
internal class Program
{
static void Main(string[] args)
{
try
{
Stopwatch SaveChangesStopwatch = new Stopwatch();
Stopwatch BulkDeleteStopwatch = new Stopwatch();
//Bulk DELETE using SaveChanges
using (var context = new EFCoreDbContext())
{
//First Add 1000 Students
AddStudents(1000);
//Then Fetch the Students to be Deleted
var StudentListToBeDeleted = context.Students.Where(std => std.StudentId > 4).ToList();
int StudentCount = StudentListToBeDeleted.Count;
//Then Call the Remove Range method which will mark the Entity State as Deleted
context.Students.RemoveRange(StudentListToBeDeleted);
//Start the StopWatch
SaveChangesStopwatch.Start();
//Call the SaveChanges Method to Delete the Data in the Database
context.SaveChanges();
//Stop the StopWatch
SaveChangesStopwatch.Stop();
Console.WriteLine($"SaveChanges, Entities : {StudentCount}, Performance : {SaveChangesStopwatch.ElapsedMilliseconds} MS");
}
//Bulk DELETE using BulkDelete Extension Method
using (var context = new EFCoreDbContext())
{
//First Add 1000 Students
AddStudents(1000);
//Then Fetch the Students to be Deleted
var StudentListToBeDeleted = context.Students.Where(std => std.StudentId > 4).ToList();
int StudentCount = StudentListToBeDeleted.Count;
//Start the StopWatch
BulkDeleteStopwatch.Start();
//Call the BulkDelete Method to Delete the Data in the Database
context.BulkDelete(StudentListToBeDeleted);
//Stop the StopWatch
BulkDeleteStopwatch.Stop();
Console.WriteLine($"BulkUpdate, Entities : {StudentCount}, Performance : {BulkDeleteStopwatch.ElapsedMilliseconds} MS");
}
Console.Read();
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}"); ;
}
}
public static void AddStudents(int count)
{
//Add Students for Performance Testing
var stduentsList = GenerateStudents(count);
// Bulk Insert using BulkInsert Extension Method
using (var context = new EFCoreDbContext())
{
context.BulkInsert(stduentsList, options => options.AutoMapOutputDirection = false);
}
}
public static List<Student> GenerateStudents(int count)
{
List<Student> Studentlist = new List<Student>();
for (int i = 0; i < count; i++)
{
Studentlist.Add(new Student() { FirstName = "FirstName_" + i, LastName = "LastName_" + i, Branch = "CSE" });
}
return Studentlist;
}
}
}
When you run the above code, you will get the following output. As you can see, the SaveChanges method took 183 MS to delete 1000 records from the database, while the BulkDelete Extension Method took 127 MS to delete the same 1000 records. So, clearly, there is a huge performance difference between both approaches. So, in real-time applications, while performing BULK DELETE, you should avoid using the SaveChanges method if performance is the major factor and go with the BulkDelete Extension method.
Advantages of using Entity Framework Core Extension
Entity Framework Core Extensions (EF Extensions) is a library that provides various features and extensions for Entity Framework Core (EF Core). Using EF Extensions can offer several advantages and benefits when working with EF Core in your application:
- Improved Performance: EF Extensions is designed to optimize database operations, such as bulk insert, bulk update, and bulk delete. These operations are significantly faster than their standard EF Core counterparts, especially when dealing large datasets. The library uses efficient batch processing and generates optimized SQL queries to reduce database round-trips and improve performance.
- Simplified Bulk Operations: Performing bulk insert, update, and delete operations in EF Core can be challenging and error-prone when using standard approaches. EF Extensions simplify these operations by providing easy-to-use methods for bulk operations, reducing the complexity of writing and maintaining custom code.
- Reduced Database Round-Trips: EF Extensions minimize the number of database round-trips by batching multiple records into a single operation. This can lead to substantial performance improvements, particularly in scenarios involving large data imports or updates.
- Transaction Support: EF Extensions provides built-in transaction support for bulk operations. You can execute bulk operations within a transaction, ensuring data consistency and the ability to roll back changes if an error occurs.
- Optimized SQL Queries: The library generates optimized SQL queries for bulk operations, taking advantage of database-specific features and optimizations. This results in more efficient database interactions and better performance.
- Cross-Platform Compatibility: EF Extensions is compatible with various database providers EF Core supports, including SQL Server, MySQL, PostgreSQL, Oracle, SQLite, and more. This cross-platform support allows you to use the library with different database systems.
- Developer Productivity: Using EF Extensions can enhance developer productivity by simplifying the implementation of bulk operations and reducing the need for writing complex custom code. This can lead to faster development and easier maintenance.
- Flexible Licensing: EF Extensions offers different licensing options, including a free Community Edition for basic functionality and paid editions (Professional and Enterprise) with additional features and support. Developers can choose the edition that best fits their needs and budget.
- Active Development and Support: EF Extensions is actively developed and supported by ZZZ Projects. This ensures that the library remains up-to-date with EF Core and database provider changes and provides timely bug fixes and improvements.
- Community and Documentation: EF Extensions has an active user community and comprehensive documentation, including tutorials and examples. This makes it easier for developers to get started and troubleshoot issues.
While EF Extensions offer numerous advantages, they may not be necessary for all projects. Evaluate your specific application requirements and performance needs to determine whether EF Extensions suit your EF Core-based application.
Disadvantages of using Entity Framework Core Extension
While Entity Framework Core Extensions (EF Extensions) provide various advantages and benefits, there are also some potential disadvantages and considerations to be aware of when using this library:
- Additional Dependency: Introducing EF Extensions into your project adds an additional dependency, which may increase the size of your application and require you to manage updates and compatibility with new versions of EF Extensions.
- Licensing Costs: While EF Extensions offers a free Community Edition with basic functionality, more advanced features and support may require a paid license (Professional or Enterprise Edition). Licensing costs can be a consideration for some projects.
- Learning Curve: Learning how to use EF Extensions effectively, including its specific API and methods, may require additional effort and time for developers new to the library.
- Compatibility and Maintenance: EF Extensions may need to be updated to remain compatible with new versions of Entity Framework Core (EF Core) and database providers. Ensuring that your project remains compatible and that any potential breaking changes are addressed can require maintenance effort.
- Limited to Specific Use Cases: EF Extensions is primarily designed for optimizing bulk operations, such as bulk insert, bulk update, and bulk delete. If your application does not require frequent or large-scale bulk operations, the library’s benefits may not justify its use.
- Reduced Control Over SQL Queries: While EF Extensions generate optimized SQL queries for bulk operations, this can also mean reduced control over the specific SQL statements being executed. In some cases, advanced users who need precise control over query optimization may prefer to write custom SQL scripts.
- Transaction Management: While EF Extensions supports transactions for bulk operations, managing transactions and error handling can become more complex when performing bulk operations, especially if multiple operations are involved within a single transaction.
- Compatibility With Database Providers: While EF Extensions aims to support multiple database providers, not all database providers may be fully compatible or supported. Testing and ensuring compatibility with your specific database provider may be necessary.
- Resource Utilization: Performing bulk operations can consume significant system resources, especially in scenarios involving very large datasets. Proper resource management and monitoring may be required to prevent performance issues.
Consideration of Database Constraints: When performing bulk operations, it’s important to consider database constraints (e.g., unique constraints, foreign key constraints) and ensure that data integrity is maintained during the operations.
Points to Remember:
Entity Framework Extensions (EF Extensions), which ZZZ Projects develop, offers both free and paid versions. The library provides various features for optimizing and simplifying bulk operations in Entity Framework Core.
- Free Version (Community Edition): EF Extensions offers a free Community Edition that provides basic functionality for bulk operations. This free version includes features like bulk insert, bulk update, bulk delete, and more. It can be used in your projects without requiring a license.
- Paid Versions (Professional and Enterprise Editions): EF Extensions also offers paid versions, such as the Professional and Enterprise Editions. These paid versions provide additional features, performance enhancements, and support. Licensing fees may apply for the Professional and Enterprise Editions, and the pricing may vary based on the specific edition and usage requirements.
In the next article, I will discuss Asynchronous Programming with Entity Framework Core with Examples. In this article, I try to explain Bulk Operations using Entity Framework Core Extension with Examples. I hope you enjoyed this Bulk Operations using Entity Framework Core Extension 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.
