Bulk Operations Using Entity Framework Core

While being very efficient, Entity Framework (EF) & Entity Framework Core (EF Core) do not allow you to natively perform bulk operations, Hopefully, there is an open-source library that solves the problem It is known as EF Core Bulk extensions. This article shows how to use EF Core Bulk Extensions on a .Net project that relies on EF Core.

Problem with EF, EFCore while dealing with huge data

We should not directly insert 100k data into the database by using Entity Framework. It may take a few minutes to perform the entire task. EntityFramework has been criticized when it comes to performance. The efficient way of dealing with such a large amount of data under such conditions, it is common to go back using ADO.Net to accomplish the task. However, if you have used the Entity Framework in your project, the combination of ADO.Net and SQLBulkCopy will break the benefits of the EntityFramework as an ORM (Object Relation Mapping)

Compare Performance between Bulk Insert vs Add Range

It is said that we can insert large data over 20 times faster than a regular insert. See the comparison below

Source Code - Git Hub Repo

Project Setup

Create a Web API template with the latest .Net Core installed in your machine

Require packages - To perform the CRUD Operations using Code First approach.

Package to Perform EF Core Bulk Operations

Create a model and DbContext where we can perform the table creation in the SQL Database with the configuration setup.

Employee.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
namespace BulkOperations_EFCore.Models
{
public class Employee
{
[Key]
public int Id{ get; set; }
public string Name { get; set; }
public string Designation { get; set; }
public string City { get; set; }
}
}

AppDbContext.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace BulkOperations_EFCore.Models
{
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
{
}
public DbSet<Employee> Employees { get; set; }
}
}

Set up the connection string in the appsettings.json file

appsettings.json

{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"myconn": "server=*Your Server Name*; database=bulkops;Trusted_Connection=True;"
}
}

Startup.cs

using BulkOperations_EFCore.BusinessLogic;
using BulkOperations_EFCore.Models;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace BulkOperations_EFCore
{
public class Startup
{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.AddControllers();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "BulkOperations_EFCore", Version = "v1" });
});
#region Connection String
services.AddDbContext<AppDbContext>(item => item.UseSqlServer(Configuration.GetConnectionString("myconn")));
#endregion
services.AddScoped<EmployeeService>();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseSwagger();
app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "BulkOperations_EFCore v1"));
}
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
}

Create a Class named as EmployeeService in which we can add all the Methods to perform the CRUD Operations using the EF Core and Bulk Operations and added a loop to perform (100k records) for bulk insert and bulk update and along with Bulk delete.

EmployeeService.cs

using BulkOperations_EFCore.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using EFCore.BulkExtensions;
namespace BulkOperations_EFCore.BusinessLogic
{
public class EmployeeService
{
private readonly AppDbContext _appDbContext;
private DateTime Start;
private TimeSpan TimeSpan;
//The "duration" variable contains Execution time when we doing the operations (Insert,Update,Delete)
public EmployeeService(AppDbContext appDbContext)
{
_appDbContext = appDbContext;
}
#region Add Bulk Data
public async Task<TimeSpan> AddBulkDataAsync()
{
List<Employee> employees = new(); // C# 9 Syntax.
Start = DateTime.Now;
for (int i = 0; i < 100000; i++)
{
employees.Add(new Employee()
{
Name = "Employee_" + i,
Designation = "Designation_" + i,
City = "City_" + i
});
}
await _appDbContext.BulkInsertAsync(employees);
TimeSpan = DateTime.Now - Start;
return TimeSpan;
}
#endregion
#region Update Bulk Data
public async Task<TimeSpan> UpdateBulkDataAsync()
{
List<Employee> employees = new(); // C# 9 Syntax.
Start = DateTime.Now;
for (int i = 0; i < 100000; i++)
{
employees.Add(new Employee()
{
Id = (i + 1),
Name = "Update Employee_" + i,
Designation = "Update Designation_" + i,
City = "Update City_" + i
});
}
await _appDbContext.BulkUpdateAsync(employees);
TimeSpan = DateTime.Now - Start;
return TimeSpan;
}
#endregion
#region Delete Bulk Data
public async Task<TimeSpan> DeleteBulkDataAsync()
{
List<Employee> employees = new(); // C# 9 Syntax.
Start = DateTime.Now;
employees = _appDbContext.Employees.ToList();
await _appDbContext.BulkDeleteAsync(employees);
TimeSpan = DateTime.Now - Start;
return TimeSpan;
}
#endregion
}
}

Let's create an individual endpoint for all the respective service methods inside the controller class.

BulkOperationsController.cs

using BulkOperations_EFCore.BusinessLogic;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace BulkOperations_EFCore.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class BulkOperationsController : ControllerBase
{
private readonly EmployeeService _employeeService;
public BulkOperationsController(EmployeeService employeeService)
{
_employeeService = employeeService;
}
[HttpPost(nameof(AddBulkData))]
public async Task<IActionResult> AddBulkData()
{
var response = await _employeeService.AddBulkDataAsync();
return Ok(response);
}
[HttpPut(nameof(UpdateBulkData))]
public async Task<IActionResult> UpdateBulkData()
{
var response = await _employeeService.UpdateBulkDataAsync();
return Ok(response);
}
[HttpDelete(nameof(DeleteBulkData))]
public async Task<IActionResult> DeleteBulkData()
{
var response = await _employeeService.DeleteBulkDataAsync();
return Ok(response);
}
}
}

Testing the Endpoints

Test the API to check how much time is consumed to complete the operation. It hardly takes 4 Sec to insert all the 100k records.

Update - API (9 Sec - 100k records)

Delete - API (3 Sec - 100k records)

Query to fetch the data and count in SQL Server

After execution of Update API

Thanks for reading and please keep visiting and sharing with your community.

Happy Coding..!

9 Comments

Join the conversation! Your thoughts help the community grow.

David Corbin2y ago
This does not seem to BulkInsert the entire graph....
Mithun C3y ago
Thanks for the tutorial. I am getting 'cannot create table' while deleting on the server is there an alternative to that
- Mithun C3y ago
  Nevermind got that working, just incase someone needs it here is the sample using (var transaction = _db.Database.BeginTransaction()) { await _db.BulkDeleteAsync(inputDataList, new BulkConfig() { UseTempDB = true }); await transaction.CommitAsync(); }
Vnd Pl4y ago
Is EFCore.BulkExtensions open source and can be used free of cost?
- Henry Sander3y ago
  Nope, the library is not free anymore ;( The guy is now asking for $500-$1000 for commercial usage (https://github.com/borisdj/EFCore.BulkExtensions/issues/1079). For a paid library, EF Extensions is a better alternative… we use it in my enterprise and get great support from them.
- Jay Krishna Reddy4y ago
  Yeah its free of cost... and its open source
Hamid Khan4y ago
Nice example Thanks Jay Krishna Reddy for sharing
Talal Aslam5y ago
Thanks Dear your Code is much better than other writers
- Jay Krishna Reddy5y ago
  Thanks for your feedback !