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..!