EF Core 批量插入操作原理分析

概要

本文主要讨论EF Core 在批量添加操作的基本原理和优化方式，基本原理主要针对EF Core 6.0 和7.0两个版本。

代码和实现

本文通过一个简单场景来模拟批量添加操作。一个分行（Branch）包含若干台ATM机，Branch和ATM各对应一张数据表。我们一次添加多台ATM机的数据到ATM机数据表。

具体Branch和ATM的定义请参考附录

批量添加代码如下：

 public async Task<int> BatchAddATMs()
 {
     await _context.Set<ATM>().AddRangeAsync(new List<ATM>()
     {
         new ATM(){ Name="ATM1",BranchId = 1 },
         new ATM(){ Name="ATM2", BranchId = 1},
         new ATM(){ Name="ATM3", BranchId = 1},
         new ATM(){ Name="ATM4", BranchId = 1},
         new ATM(){ Name="ATM5", BranchId = 1},
         new ATM(){Name="ATM6" , BranchId = 2},
         new ATM(){Name="ATM7" , BranchId = 2}
     });
     return await _context.SaveChangesAsync(); 
 }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

代码执行后，产生的SQL语句如下：

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (396ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?'
(DbType = Int32), @p2='?' (DbType = Boolean), @p3='?' (Size = 4000), @p4='?' (Db
Type = Boolean), @p5='?' (DbType = Int32), @p6='?' (DbType = Int32), @p7='?' (Db
Type = Boolean), @p8='?' (Size = 4000), @p9='?' (DbType = Boolean), @p10='?' (Db
Type = Int32), @p11='?' (DbType = Int32), @p12='?' (DbType = Boolean), @p13='?'
(Size = 4000), @p14='?' (DbType = Boolean), @p15='?' (DbType = Int32), @p16='?'
(DbType = Int32), @p17='?' (DbType = Boolean), @p18='?' (Size = 4000), @p19='?'
(DbType = Boolean), @p20='?' (DbType = Int32), @p21='?' (DbType = Int32), @p22='
?' (DbType = Boolean), @p23='?' (Size = 4000), @p24='?' (DbType = Boolean), @p25
='?' (DbType = Int32), @p26='?' (DbType = Int32), @p27='?' (DbType = Boolean), @
p28='?' (Size = 4000), @p29='?' (DbType = Boolean), @p30='?' (DbType = Int32), @
p31='?' (DbType = Int32), @p32='?' (DbType = Boolean), @p33='?' (Size = 4000), @
p34='?' (DbType = Boolean)], CommandType='Text', CommandTimeout='30']
      SET IMPLICIT_TRANSACTIONS OFF;
      SET NOCOUNT ON;
      MERGE [tt_atm] USING (
      VALUES (@p0, @p1, @p2, @p3, @p4, 0),
      (@p5, @p6, @p7, @p8, @p9, 1),
      (@p10, @p11, @p12, @p13, @p14, 2),
      (@p15, @p16, @p17, @p18, @p19, 3),
      (@p20, @p21, @p22, @p23, @p24, 4),
      (@p25, @p26, @p27, @p28, @p29, 5),
      (@p30, @p31, @p32, @p33, @p34, 6)) AS i ([BranchId], [DeviceStatus], [IsDe
leted], [Name], [SupportForeignCurrency], _Position) ON 1=0
      WHEN NOT MATCHED THEN
      INSERT ([BranchId], [DeviceStatus], [IsDeleted], [Name], [SupportForeignCu
rrency])
      VALUES (i.[BranchId], i.[DeviceStatus], i.[IsDeleted], i.[Name], i.[Suppor
tForeignCurrency])
      OUTPUT INSERTED.[Id], INSERTED.[Rowversion], i._Position;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

我们可以看到EF Core 并没有为每个ATM生成一条insert语句去执行插入操作，而是使用了merge语句。

merge语句主要用于源数据表和目标数据表的数据同步，它本身就包含了插入，更新和删除操作，具备出色的性能。

在本例中，要使用merge语句来批量操作，EF Core 分两步来实现：

1. 使用Derived Table来模拟源数据表，将要插入的数据通过values子句，创建出一张源数据表；
2. ON 1=0是人工制造一个when not matched成立的条件，即只执行insert操作，忽略merge中的update和delete操作。

7.0版本相比于6.0版本，更加高效。7.0中删除了事务操作，因为merge是受隐式事务保护的单个语句。而且7.0不再使用临时表作为源数据表，而是使用Derived Table来作为源数据表。

请注意，该批量插入的方法存在一个缺点，我们可以看到生成的SQL包含OUTPUT 语句，所以如果目标数据表包含触发器，则会抛出异常。

附录

 [Table("tt_branch")]
    public class Branch : Entity
    {
        [Required]
        public string Name { get; set; } = string.Empty;
        [Required]
        public string Address { get; set; } = string.Empty;
        [Required]
        public bool hasCreditCardService { get; set; } = false;
        [Required]
        public bool hasChequeService { get; set; } = false;

        public ICollection<ATM> Atms { get; } = new List<ATM>();
        public ICollection<McDonaldATM> MCAtms { get; } = new List<McDonaldATM>();
        public ICollection<CDM> Cdms { get; } = new List<CDM>();

        public User Manager { get; set; } = null!;

}
public abstract class BankDevice : Entity
    {
       
        [Required]
        public string Name { get; set; } = string.Empty;
        [Required]
        public DeviceStatus DeviceStatus { get; set; } = DeviceStatus.Running;
        public int BranchId { get; set; }
    }
    [Table("tt_atm")]
    public class ATM : BankDevice
    {
        [Required]
        public bool SupportForeignCurrency { get; set; } = false;

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35