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 the following packages
Microsoft.EntityFrameworkCore
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.ToolsThen we will install "EFCore.BulkExtensions", using Nuget package manager
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; }
}
}
In the above C# Code, [Key] Data Annotation is used to denote that this column is PrimaryKey of database table.
Now, we need to navigate to "Startup.cs", and use above DBContext and use the 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,we are simply starting a timer and creating a list of items using Foreach loop, once we have around 100k items with "Names", we are using BulkInsert to save all rows of data in database.
so for the above, it took 996 ms (100000 records), to create a fake Names list and save it in the 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
You may also like to read:
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
Buy us a coffee
Become a Patron