我用的.net 6
- 安装依赖包
> Microsoft.EntityFrameworkCore 6.0.33 6.0.33
> Microsoft.EntityFrameworkCore.Tools 6.0.33 6.0.33
> Npgsql.EntityFrameworkCore.PostgreSQL 6.0.29 6.0.29
> Z.EntityFramework.Extensions.EFCore 6.103.4 6.103.4
- 添加配置
{"Logging": {"LogLevel": {"Default": "Information","Microsoft.AspNetCore": "Warning"}},"AllowedHosts": "*","DataBaseConfig": {"Host": "192.168.214.133","Port": 32222,"UserName": "postgresadmin","Password": "admin123","DataBase": "postgresdb"}
}- 添加实体类，实体配置，以及配置类
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;namespace EFCoreBulkInsert
{public class Config{public int Port { get; set; }public string Host { get; set; }public string UserName { get; set; }public string Password { get; set; }public string DataBase { get; set; }}public class Test{public int ID { get; set; }public string Name { get; set; }}public class TestConfig : IEntityTypeConfiguration<Test>{public void Configure(EntityTypeBuilder<Test> builder){builder.ToTable("test");builder.HasKey(t => t.ID);builder.Property(t => t.ID).HasColumnName("id");builder.Property(t => t.Name).HasColumnName("name");}}
}- 添加dbcontext
using Microsoft.EntityFrameworkCore;namespace EFCoreBulkInsert
{public class CustomDBContext : DbContext{public DbSet<Test> bulkTestConfigs { get; set; }public CustomDBContext(DbContextOptions<CustomDBContext> options) : base(options){}protected override void OnModelCreating(ModelBuilder modelBuilder){base.OnModelCreating(modelBuilder);modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);}}
}- DI
builder.Services.Configure<Config>(builder.Configuration.GetSection("DataBaseConfig"));builder.Services.AddScoped<Config>();builder.Services.AddDbContext<CustomDBContext>((sp,options) =>
{var config = sp.GetRequiredService<IOptionsSnapshot<Config>>();options.UseNpgsql($"Host={config.Value.Host};Port={config.Value.Port};Database={config.Value.DataBase};Username={config.Value.UserName};Password={config.Value.Password}").LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name }, LogLevel.Information).EnableSensitiveDataLogging();
});- controller
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Diagnostics;namespace EFCoreBulkInsert.Controllers
{[Route("api/[controller]/[action]")][ApiController]public class BulkController : ControllerBase{public readonly CustomDBContext _customDBContext;public BulkController(CustomDBContext customDBContext){_customDBContext = customDBContext;}[HttpPost]public async Task<IActionResult> BulkInsert([FromBody] int count){Stopwatch stopwatch = Stopwatch.StartNew();stopwatch.Start();List<Test> tests = new List<Test>();for (int i = 0; i < count; i++){tests.Add(new Test { Name = i.ToString() });}_customDBContext.BulkInsert(tests, options => {options.AutoMapOutputDirection = false;options.InsertIfNotExists = true;options.BatchSize = 100;});stopwatch.Stop();return Ok(new { time= stopwatch.Elapsed });}[HttpPost]public async Task<IActionResult> BulkUpdate([FromBody] List<int> ids){Stopwatch stopwatch = Stopwatch.StartNew();stopwatch.Start();List<Test> tests = new List<Test>();foreach (var item in ids){tests.Add(new Test() { ID = item, Name = "haha" + item.ToString() });}//以下两种写法都行await _customDBContext.BulkUpdateAsync(tests, options =>{options.ColumnInputExpression = x => new { x.Name };});await _customDBContext.BulkUpdateAsync(tests, options =>{options.ColumnInputNames = new List<string> { "Name" };});stopwatch.Stop();return Ok(new { time = stopwatch.Elapsed });}[HttpPost]public async Task<IActionResult> BulkDelete([FromBody] List<int> ids){Stopwatch stopwatch = Stopwatch.StartNew();stopwatch.Start();await _customDBContext.BulkDeleteAsync(_customDBContext.bulkTestConfigs.Where(x => ids.Contains(x.ID)));stopwatch.Stop();return Ok(new { time = stopwatch.Elapsed });}[HttpPost]public async Task<IActionResult> BulkMergeUpdate([FromBody] Dictionary<string,string> keyValuePairs){Stopwatch stopwatch = Stopwatch.StartNew();stopwatch.Start();List<Test> tests = new List<Test>();foreach (var item in keyValuePairs){tests.Add(new Test { ID = Convert.ToInt16(item.Key), Name = item.Value });}await _customDBContext.BulkMergeAsync(tests, options =>{options.ColumnPrimaryKeyNames = new List<string> { "ID" }; //通过指定ID参数，达到更新的效果//options.IgnoreOnMergeInsertNames = new List<string>() { "UpdatedDate", "UpdatedBy" }; //插入忽略的属性//options.IgnoreOnMergeUpdateExpression = x => new { x.CreatedDate, x.CreatedBy }; // 更新忽略的属性});stopwatch.Stop();return Ok(new { time = stopwatch.Elapsed });}}
}Bulk Insert
以下是一些常用的参数设置
- AutoMapOutputDirection: This option allows to optimize performance by not returning outputting values such as identity values.
- InsertIfNotExists: This option ensures only new entities that don’t already exist in the database are inserted.
- InsertKeepIdentity: This option allows insertion of specific values into an identity column from your entities.
- IncludeGraph: This option enables insertion of entities along with all related entities found in the entity graph, maintaining the relationships.
Bulk Update
- ColumnPrimaryKeyExpression: This option allows you to use a custom key to check for pre-existing entities.
- ColumnInputExpression: This option enables you to specify a subset of columns to update by using an expression.
- ColumnInputNames: This option allows you to specify a subset of columns to update by providing their names.
- IncludeGraph: This option allow updating entities along with all related entities found in the entity graph, maintaining the data relationships.
Bulk Delete
- ColumnPrimaryKeyExpression: This option allows the usage of a custom key to verify the existence of entities.
- DeleteMatchedAndConditionExpression: This option enables you to perform or skip the deletion action based on whether all values from the source and destination are equal for the specified properties.
- DeleteMatchedAndOneNotConditionExpression: This option allows you to perform or skip the deletion action if at least one value from the source differs from the destination for the specified properties.
- DeleteMatchedAndFormula: This option lets you perform or skip the deletion action based on a predefined SQL condition.
官网
源码