概要
EF Core 7.0 提供了一个可以将LINQ查询和批量修改相结合的方法ExecuteUpdate。由于数据修改是以批量更新的方式完成,所以可以减少数据库的往返次数。
本文将主要介绍ExecuteUpdate的使用方法。
代码和实现
基本案例
本文我们使用银行分行,ATM机和分行经理三张数据表,关系如下,类定义请见附录:
- 一个分行拥有若干台ATM机
- 一台ATM机只能隶属于一个分行
- 一个分行只拥有一名分行经理
- 一个人只能做一个分行的经理
批量更新之单表操作
我们在所有IsDeleted 为true,即已经关闭的分行, 将它们的名称前面加上字符串 Decommissioned
EF CORE代码如下:
public async Task<int> UpdateBranchSingleTable()
{
return await _context.Set<Branch>()
.Where(b => b.IsDeleted == true)
.ExecuteUpdateAsync(b => b.SetProperty(p => p.Name, m => "Decommissioned" + m.Name + "!"));
}
生成的SQL代码如下:
UPDATE [t]
SET [t].[Name] = (N'Decommissioned' + [t].[Name]) + N'!'
FROM [tt_branch] AS [t]
WHERE [t].[IsDeleted] = CAST(1 AS bit)
生成的是批量更新的SQL语句。
批量更新之多表操作
Case 1 查询拥有支持外币操作ATM机的分行,并将分行名称前面标注支持外币业务。
public async Task<int> UpdateBranchFormMultipleTable()
{
return await _context.Set<Branch>()
.Where(b => b.IsDeleted == false && b.Atms.Any(a => a.SupportForeignCurrency == true))
.ExecuteUpdateAsync(b => b.SetProperty(p => p.Name, m => "Global Service " + m.Name));
}
Case 2 查询分行经理title是BranchManager的分行,并将这些分行的名称前面增加Level 2。
public async Task<int> UpdateBranchMultipleTables() {
return await _context.Set<Branch>()
.Where(b => b.IsDeleted == false && b.Manager.Title == "BranchManager")
.ExecuteUpdateAsync(b => b.SetProperty(p => p.Name, m => "Level 2" + m.Name));
}
生成的SQL代码如下:
UPDATE [t]
SET [t].[Name] = N'Level 2' + [t].[Name]
FROM [tt_branch] AS [t]
INNER JOIN [tt_user] AS [t0] ON [t].[Id] = [t0].[Id]
WHERE [t].[IsDeleted] = CAST(0 AS bit) AND [t0].[Title] = N'BranchManager'
我们看到,SQL是基于联表操作以后再进行的过滤和更新。这样看,通过Dapple等半自动ORM框架,自己完成的SQL语句和通过EF Core生成的SQL没有任何区别。
附录
实体类定义
public class Entity
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
[Timestamp]
public byte[]? Rowversion { get; set; }
public bool IsDeleted { get; set; }
}
[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 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;
}
[Table("tt_atm")]
public class ATM : BankDevice
{
[Required]
public bool SupportForeignCurrency { get; set; } = false;
}
public enum DeviceStatus
{
Running = 1,
Standby,
Maintance
}
[Table("tt_user")]
public class User:Entity
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string Title { get; set; }
public Branch Branch { get; set; } = null!;
}
实体关系定义
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Branch>()
.HasMany(x => x.Atms)
.WithOne()
.HasForeignKey("BranchId")
.IsRequired();
modelBuilder.Entity<Branch>()
.HasMany(x => x.Cdms)
.WithOne()
.HasForeignKey("BranchId")
.IsRequired();
modelBuilder.Entity<Branch>()
.HasMany(x => x.MCAtms)
.WithOne()
.HasForeignKey("BranchId")
.IsRequired();
modelBuilder.Entity<Branch>()
.HasOne(x => x.Manager)
.WithOne(x => x.Branch)
.HasForeignKey<Branch>();
}