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
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel.DataAnnotations;
  4. using System.Linq;
  5. using System.Threading.Tasks;
  7. namespace BulkOperations_EFCore.Models
  8. {
  9. public class Employee
  10. {
  11. [Key]
  12. public int Id{ get; set; }
  13. public string Name { get; set; }
  14. public string Designation { get; set; }
  15. public string City { get; set; }
  16. }
  17. }
AppDbContext.cs
  1. using Microsoft.EntityFrameworkCore;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Linq;
  5. using System.Threading.Tasks;
  7. namespace BulkOperations_EFCore.Models
  8. {
  9. public class AppDbContext : DbContext
  10. {
  11. public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
  12. {
  14. }
  15. public DbSet<Employee> Employees { get; set; }
  16. }
  17. }
Set up the connection string in the appsettings.json file
appsettings.json
  1. {
  2. "Logging": {
  3. "LogLevel": {
  4. "Default": "Information",
  5. "Microsoft": "Warning",
  6. "Microsoft.Hosting.Lifetime": "Information"
  7. }
  8. },
  9. "AllowedHosts": "*",
  10. "ConnectionStrings": {
  11. "myconn": "server=*Your Server Name*; database=bulkops;Trusted_Connection=True;"
  12. }
  13. }
Startup.cs
  1. using BulkOperations_EFCore.BusinessLogic;
  2. using BulkOperations_EFCore.Models;
  3. using Microsoft.AspNetCore.Builder;
  4. using Microsoft.AspNetCore.Hosting;
  5. using Microsoft.AspNetCore.HttpsPolicy;
  6. using Microsoft.AspNetCore.Mvc;
  7. using Microsoft.EntityFrameworkCore;
  8. using Microsoft.Extensions.Configuration;
  9. using Microsoft.Extensions.DependencyInjection;
  10. using Microsoft.Extensions.Hosting;
  11. using Microsoft.Extensions.Logging;
  12. using Microsoft.OpenApi.Models;
  13. using System;
  14. using System.Collections.Generic;
  15. using System.Linq;
  16. using System.Threading.Tasks;
  18. namespace BulkOperations_EFCore
  19. {
  20. public class Startup
  21. {
  22. public Startup(IConfiguration configuration)
  23. {
  24. Configuration = configuration;
  25. }
  27. public IConfiguration Configuration { get; }
  29. // This method gets called by the runtime. Use this method to add services to the container.
  30. public void ConfigureServices(IServiceCollection services)
  31. {
  33. services.AddControllers();
  34. services.AddSwaggerGen(c =>
  35. {
  36. c.SwaggerDoc("v1", new OpenApiInfo { Title = "BulkOperations_EFCore", Version = "v1" });
  37. });
  39. #region Connection String
  40. services.AddDbContext<AppDbContext>(item => item.UseSqlServer(Configuration.GetConnectionString("myconn")));
  41. #endregion
  42. services.AddScoped<EmployeeService>();
  44. }
  46. // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
  47. public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
  48. {
  49. if (env.IsDevelopment())
  50. {
  51. app.UseDeveloperExceptionPage();
  52. app.UseSwagger();
  53. app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "BulkOperations_EFCore v1"));
  54. }
  56. app.UseHttpsRedirection();
  58. app.UseRouting();
  60. app.UseAuthorization();
  62. app.UseEndpoints(endpoints =>
  63. {
  64. endpoints.MapControllers();
  65. });
  66. }
  67. }
  68. }
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
  1. using BulkOperations_EFCore.Models;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Linq;
  5. using System.Threading.Tasks;
  6. using EFCore.BulkExtensions;
  8. namespace BulkOperations_EFCore.BusinessLogic
  9. {
  10. public class EmployeeService
  11. {
  12. private readonly AppDbContext _appDbContext;
  13. private DateTime Start;
  14. private TimeSpan TimeSpan;
  15. //The "duration" variable contains Execution time when we doing the operations (Insert,Update,Delete)
  16. public EmployeeService(AppDbContext appDbContext)
  17. {
  18. _appDbContext = appDbContext;
  19. }
  20. #region Add Bulk Data
  21. public async Task<TimeSpan> AddBulkDataAsync()
  22. {
  23. List<Employee> employees = new(); // C# 9 Syntax.
  24. Start = DateTime.Now;
  25. for (int i = 0; i < 100000; i++)
  26. {
  27. employees.Add(new Employee()
  28. {
  29. Name = "Employee_" + i,
  30. Designation = "Designation_" + i,
  31. City = "City_" + i
  32. });
  33. }
  34. await _appDbContext.BulkInsertAsync(employees);
  35. TimeSpan = DateTime.Now - Start;
  36. return TimeSpan;
  37. }
  38. #endregion
  40. #region Update Bulk Data
  41. public async Task<TimeSpan> UpdateBulkDataAsync()
  42. {
  43. List<Employee> employees = new(); // C# 9 Syntax.
  44. Start = DateTime.Now;
  45. for (int i = 0; i < 100000; i++)
  46. {
  47. employees.Add(new Employee()
  48. {
  49. Id = (i + 1),
  50. Name = "Update Employee_" + i,
  51. Designation = "Update Designation_" + i,
  52. City = "Update City_" + i
  53. });
  54. }
  55. await _appDbContext.BulkUpdateAsync(employees);
  56. TimeSpan = DateTime.Now - Start;
  57. return TimeSpan;
  58. }
  59. #endregion
  61. #region Delete Bulk Data
  62. public async Task<TimeSpan> DeleteBulkDataAsync()
  63. {
  64. List<Employee> employees = new(); // C# 9 Syntax.
  65. Start = DateTime.Now;
  66. employees = _appDbContext.Employees.ToList();
  67. await _appDbContext.BulkDeleteAsync(employees);
  68. TimeSpan = DateTime.Now - Start;
  69. return TimeSpan;
  70. }
  71. #endregion
  72. }
  73. }
Let's create an individual endpoint for all the respective service methods inside the controller class.
BulkOperationsController.cs
  1. using BulkOperations_EFCore.BusinessLogic;
  2. using Microsoft.AspNetCore.Http;
  3. using Microsoft.AspNetCore.Mvc;
  4. using System;
  5. using System.Collections.Generic;
  6. using System.Linq;
  7. using System.Threading.Tasks;
  9. namespace BulkOperations_EFCore.Controllers
  10. {
  11. [Route("api/[controller]")]
  12. [ApiController]
  13. public class BulkOperationsController : ControllerBase
  14. {
  15. private readonly EmployeeService _employeeService;
  16. public BulkOperationsController(EmployeeService employeeService)
  17. {
  18. _employeeService = employeeService;
  19. }
  20. [HttpPost(nameof(AddBulkData))]
  21. public async Task<IActionResult> AddBulkData()
  22. {
  23. var response = await _employeeService.AddBulkDataAsync();
  24. return Ok(response);
  25. }
  26. [HttpPut(nameof(UpdateBulkData))]
  27. public async Task<IActionResult> UpdateBulkData()
  28. {
  29. var response = await _employeeService.UpdateBulkDataAsync();
  30. return Ok(response);
  31. }
  32. [HttpDelete(nameof(DeleteBulkData))]
  33. public async Task<IActionResult> DeleteBulkData()
  34. {
  35. var response = await _employeeService.DeleteBulkDataAsync();
  36. return Ok(response);
  37. }
  38. }
  39. }
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..!