If you are using ASP.NET Core or ASP.NET MVC using Entity framework, then you might to insert lots of rows in database using Entity framework, but it can be slow sometimes, so in that case, you can use Entity framework Bulk Inserts or SQLBulkCopy for faster insertion of rows in database, so in this article, I have explained how to insert or save lots of rows using Entity framework Core BulkInsert.

Step 1: Let's create a new ASP.NET Core MVC project in your Visual Studio, I am using Visual Studio 2019 for this, so open VS and click on Create new project, Select "ASP.NET Core Web App (Model View Controller)" project template -> Click 'Next' -> Name your project (EFCoreBulkInsert) -> Click "Next" -> and then leave settings as it is, click "Create".

Step 2: Install Ef Core BulkInsert Nuget package in your project, so naivagte to Tools -> Manage Nuget package for solution -> Select "Browse" tab and then search for "Entity framework", so we can install Entity framework first in our project

You need to install following packages

Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools

Then we will install "EFCore.BulkExtensions", using Nuget package manager

ef-core-bulk-insert-min.png

Step3: Now, we need to connect to database using Ef Core,so first, add connection string in appsettings.json file

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DevConnection": "Data Source=VKS-PC-NEW\\SQLEXPRESS;database=Students;Trusted_Connection=True;"
  }
}

now, we need to create new DBContext in Models folders, so right-click on Models folder and then "Add" and then select "Class"

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace EFCoreBulkInsert.Models
{
    public partial class StudentContext : DbContext
    {
        public StudentContext()
        {
        }

        public StudentContext(DbContextOptions<StudentContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Names> Names { get; set; }
    }
}

and as we will be saving details in Names table, create Names.cs as below

using System;
using System.ComponentModel.DataAnnotations;

namespace EFCoreBulkInsert.Models
{
    public partial class Names
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public Guid UniqueID { get; set; }
    }
}

Now, we need to naviagte to "Startup.cs", so use above DBContext and use code below in 'ConfigureServices' method

 public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<StudentContext>(options =>
            {
                options.UseSqlServer(Configuration.GetConnectionString("DevConnection"));
            });

            services.AddControllersWithViews();
        }

You will have to add references for

using EFCoreBulkInsert.Models;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;

Step 5: Create a fake list of data using for loop and insert it in database using Entity framework core and BulkInsert, so we will add the below code in "HomeController.cs"

  private readonly StudentContext _appDbContext;
        private DateTime Start;
        private TimeSpan TimeSpan;
        //The "duration" variable contains Execution time when we doing the operations (Insert)  
        public HomeController(StudentContext appDbContext)
        {
            _appDbContext = appDbContext;
        }

        public IActionResult Index()
        {
            List<Names> names = new(); // new syntax from C#9
            //start time now
            Start = DateTime.Now;
            //create fake data
            for (int i = 0; i < 100000; i++)
            {
                names.Add(new Names()
                {
                    Name = "Name_" + i,
                    UniqueID = Guid.NewGuid()
                });
            }

            //open database connection
            using (var transaction = _appDbContext.Database.BeginTransaction())
            {
                //insert list data using BulkInsert
                _appDbContext.BulkInsert(names);
                //commit, save changes
                transaction.Commit();
            }
            TimeSpan = DateTime.Now - Start; // check total time taken

            return View();
        }

Complete Controller code will looks like below

using EFCore.BulkExtensions;
using EFCoreBulkInsert.Models;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Diagnostics;


namespace EFCoreBulkInsert.Controllers
{
    public class HomeController : Controller
    {
        private readonly StudentContext _appDbContext;
        private DateTime Start;
        private TimeSpan TimeSpan;
        //The "duration" variable contains Execution time when we doing the operations (Insert)  
        public HomeController(StudentContext appDbContext)
        {
            _appDbContext = appDbContext;
        }

        public IActionResult Index()
        {
            List<Names> names = new(); // new syntax from C#9
            //start time now
            Start = DateTime.Now;
            //create fake data
            for (int i = 0; i < 100000; i++)
            {
                names.Add(new Names()
                {
                    Name = "Name_" + i,
                    UniqueID = Guid.NewGuid()
                });
            }

            //open database connection
            using (var transaction = _appDbContext.Database.BeginTransaction())
            {
                //insert list data using BulkInsert
                _appDbContext.BulkInsert(names);
                //commit, save changes
                transaction.Commit();
            }
            TimeSpan = DateTime.Now - Start; // check total time taken

            return View();
        }
    }
}

I have commented out the important part of codes, so for the above, it took 996 ms (100000 records), to create fake Names list and save it in database using BulkInsert which is quite fast and can be helpful.

Here is the Sample Output using Gif image, which I have captured while executing above code

bulk-insert-ef-core-min.gif

You may also like to read:

Treeview in ASP.NET Core MVC

Model Validation in ASP.NET Core MVC

Creating GridView in ASP.NET Core MVC with Paging

Import Excel data in SQL Server database in ASP.NET Core MVC

Form Submit in ASP.NET Core MVC

jQuery AJAX in ASP.NET Core MVC

Get Vs post in Rest API