EFCore postgresql 批量删除、插入、更新

我用的.net 6

1. 安装依赖包

> 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

2. 添加配置

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "DataBaseConfig": {
    "Host": "192.168.214.133",
    "Port": 32222,
    "UserName": "postgresadmin",
    "Password": "admin123",
    "DataBase": "postgresdb"
  }
}

3. 添加实体类，实体配置，以及配置类

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");
        }
    }
}

4. 添加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);
        }
    }
}

5. 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();
});

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

官网

源码