我用的.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.