修改

2881099 edited this page 2023-12-14 19:52:08 +08:00

Table of Contents

• 动态条件
• 动态表名
• 1、更新指定列
• 2、更新条件
• 3、更新实体
• 4、自定义SQL
• 5、根据 Dto 更新
• 6、Set/SetSource/SetDto 区别
• 7、字典更新
• 8、乐观锁
• 9、悲观锁
• 10、ISelect.ToUpdate 高级更新
• 11、联表更新 UpdateJoin
• 12、BulkCopy 批量更新
• 参考资料
• API

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

中文 | English

FreeSql 提供丰富的数据库更新功能，支持单条或批量更新，在特定的数据库执行还可以返回更新后的记录。

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, connectionString)
    .UseAutoSyncStructure(true) //自动同步实体结构到数据库
    .Build(); //请务必定义成 Singleton 单例模式

class Topic {
    [Column(IsIdentity = true, IsPrimary = true)]
    public int Id { get; set; }
    public int Clicks { get; set; }
    public string Title { get; set; }
    public DateTime CreateTime { get; set; }
}

动态条件

fsql.Update<Topic>(object dywhere)

dywhere 支持：

• 主键值
• new[] { 主键值1, 主键值2 }
• Topic 对象
• new[] { Topic对象1, Topic对象2 }
• new { id = 1 }

动态表名

fsql.Update<Topic>(1).AsTable("Topic_201903").ExecuteAffrows(); //对 Topic_201903 表更新

1、更新指定列

fsql.Update<Topic>(1)
  .Set(a => a.CreateTime, DateTime.Now)
  .ExecuteAffrows();
//UPDATE `Topic` SET `CreateTime` = '2018-12-08 00:04:59' 
//WHERE (`Id` = 1)

支持 Set() 多次，相当于拼接

fsql.Update<Topic>(1)
  .Set(a => a.Clicks + 1)
  .Set(a => a.Time == DateTime.Now)
  .ExecuteAffrows();
//UPDATE `Topic` SET `Clicks` = ifnull(`Clicks`,0) + 1, `Time` = now() 
//WHERE (`Id` = 1)

fsql.Update<Topic>(1)
  .Set(a => new Topic
  {
    Clicks = a.Clicks + 1,
    Time = DateTime.Now
  })
  .ExecuteAffrows();
//UPDATE `Topic` SET `Clicks` = ifnull(`Clicks`,0) + 1, `Time` = now() 
//WHERE (`Id` = 1)

2、更新条件

除了上面介绍的 dywhere 构造参数外，还支持 Where lambda/sql 方法

出于安全考虑，没有条件不执行更新动作，避免误更新全表数据。更新全表数据：fsql.Update<T>().Where("1=1").Set(a => a.Xxx == xxx).ExecuteAffrows()

fsql.Update<Topic>()
  .Set(a => a.Title, "新标题")
  .Set(a => a.Time, DateTime.Now)
  .Where(a => a.Id == 1)
  .ExecuteAffrows();
//UPDATE `Topic` SET `Title` = @p_0, `Time` = @p_1 
//WHERE (Id = 1)

3、更新实体

方法1：(推荐)

只更新变化的属性，依赖 FreeSql.Repository

var repo = fsql.GetRepository<Topic>();
var item = repo.Where(a => a.Id == 1).First();  //此时快照 item
item.Title = "newtitle";
repo.Update(item); //对比快照时的变化
//UPDATE `Topic` SET `Title` = @p_0
//WHERE (`Id` = 1)

是不是觉得先查询再更新，啰嗦？

var repo = fsql.GetRepository<Topic>();
var item = new Topic { Id = 1 };
repo.Attach(item); //此时快照 item
item.Title = "newtitle";
repo.Update(item); //对比快照时的变化
//UPDATE `Topic` SET `Title` = @p_0
//WHERE (`Id` = 1)

方法2：(原始)

//v1.5.0 忽略更新 null 值的属性
fsql.Update<Topic>()
  .SetSourceIgnore(item, col => col == null)
  .ExecuteAffrows();

var item = new Topic { Id = 1, Title = "newtitle" };
fsql.Update<Topic>()
  .SetSource(item)
  .ExecuteAffrows();
//UPDATE `Topic` SET `Clicks` = @p_0, `Title` = @p_1, `CreateTime` = @p_2 
//WHERE (`Id` = 1)

fsql.Update<Topic>()
  .SetSource(item)
  .UpdateColumns(a => new { a.Title, a.CreateTime })
  .ExecuteAffrows();
//UPDATE `Topic` SET `Title` = @p_0, `CreateTime` = @p_1 
//WHERE (`Id` = 1)

fsql.Update<Topic>()
  .SetSource(item)
  .IgnoreColumns(a => new { a.Clicks, a.CreateTime })
  .ExecuteAffrows();
//UPDATE `Topic` SET `Title` = @p_0 
//WHERE (`Id` = 1)

var items = new List<Topic>();
for (var a = 0; a < 10; a++) items.Add(new Topic { Id = a + 1, Title = $"newtitle{a}", Clicks = a * 100 });

fsql.Update<Topic>()
  .SetSource(items)
  .ExecuteAffrows();
//UPDATE `Topic` SET `Clicks` = CASE `Id` WHEN 1 THEN @p_0 WHEN 2 THEN @p_1 WHEN 3 THEN @p_2 WHEN 4 THEN @p_3 WHEN 5 THEN @p_4 WHEN 6 THEN @p_5 WHEN 7 THEN @p_6 WHEN 8 THEN @p_7 WHEN 9 THEN @p_8 WHEN 10 THEN @p_9 END, 
//`Title` = CASE `Id` WHEN 1 THEN @p_10 WHEN 2 THEN @p_11 WHEN 3 THEN @p_12 WHEN 4 THEN @p_13 WHEN 5 THEN @p_14 WHEN 6 THEN @p_15 WHEN 7 THEN @p_16 WHEN 8 THEN @p_17 WHEN 9 THEN @p_18 WHEN 10 THEN @p_19 END, 
//`CreateTime` = CASE `Id` WHEN 1 THEN @p_20 WHEN 2 THEN @p_21 WHEN 3 THEN @p_22 WHEN 4 THEN @p_23 WHEN 5 THEN @p_24 WHEN 6 THEN @p_25 WHEN 7 THEN @p_26 WHEN 8 THEN @p_27 WHEN 9 THEN @p_28 WHEN 10 THEN @p_29 END 
//WHERE (`Id` IN (1,2,3,4,5,6,7,8,9,10))

fsql.Update<Topic>()
  .SetSource(items)
  .IgnoreColumns(a => new { a.Clicks, a.CreateTime })
  .ExecuteAffrows();
//UPDATE `Topic` SET `Title` = CASE `Id` WHEN 1 THEN @p_0 WHEN 2 THEN @p_1 WHEN 3 THEN @p_2 WHEN 4 THEN @p_3 WHEN 5 THEN @p_4 WHEN 6 THEN @p_5 WHEN 7 THEN @p_6 WHEN 8 THEN @p_7 WHEN 9 THEN @p_8 WHEN 10 THEN @p_9 END 
//WHERE (`Id` IN (1,2,3,4,5,6,7,8,9,10))

fsql.Update<Topic>()
  .SetSource(items)
  .Set(a => a.CreateTime, DateTime.Now)
  .ExecuteAffrows();
//UPDATE `Topic` SET `CreateTime` = @p_0 
//WHERE (`Id` IN (1,2,3,4,5,6,7,8,9,10))

指定 Set 列更新后，SetSource 将失效

SetSource 默认依赖实体 IsPrimary 特性，临时主键可使用 SetSource(items, a => a.Code)

Oracle CASE when N'' 字符集不匹配，

• 原因：实体主键 Column DbType 与表类型不一致造成，
• 解决：[Column(DbType = "varchar2", StingLength = 255)]

4、自定义SQL

fsql.Update<Topic>()
  .SetRaw("Title = @title", new { title = "新标题" })
  .Where("Id = @id", 1)
  .ExecuteAffrows();
//UPDATE `Topic` SET Title = @title WHERE (Id = @id)

5、根据 Dto 更新

fsql.Update<T>()
  .SetDto(new { title = "xxx", clicks = 2 })
  .Where(a => a.Id == 1)
  .ExecuteAffrows();
//UPDATE `Topic` SET `Title` = @p_0, `Clicks` = @p_1 WHERE (Id = 1)

fsql.Update<T>()
  .SetDto(new Dictionary<string, object> { ["title"] = "xxx", ["clicks"] = 2 })
  .Where(a => a.Id == 1)
  .ExecuteAffrows();

6、Set/SetSource/SetDto 区别

他们三个是平级功能，分别对应：

• Set/SetRaw 在知道实体的时候使用，对应 update t set x = x

• SetSource 更新整个实体，可以配合 UpdateColumns 或 IgnoreColumns 指定或忽略字段

• SetDto 是 Set 的批量操作

7、字典更新

var dic = new Dictionary<string, object>();
dic.Add("id", 1);
dic.Add("name", "xxxx");

fsql.UpdateDict(dic).AsTable("table1").WherePrimary("id").ExecuteAffrows();

8、乐观锁

更新整个实体数据时，在并发情况下极容易造成旧数据将新的记录更新。

乐观锁的原理，是利用实体某字段，如：long version，更新前先查询数据，此时 version 为 1，更新时产生的 SQL 会附加 where version = 1，当修改失败时（即 Affrows == 0）抛出异常（DbUpdateVersionException）。

每个实体只支持一个乐观锁属性，在属性前标记特性：[Column(IsVersion = true)] 即可。

适用 SetSource 更新，每次更新 version 的值都会增加 1

9、悲观锁

var user = fsql.Select<User>()
  .ForUpdate(true)
  .Where(a => a.Id == 1)
  .ToOne();
//SELECT ... FROM User a for update nowait

ForUpdate 在 Oracle/PostgreSQL/MySql 是通用的写法，我们对 SqlServer 做了特别适配，执行的 SQL 语句大致如下：

SELECT ... FROM [User] a With(UpdLock, RowLock, NoWait)

10、ISelect.ToUpdate 高级更新

IUpdate 默认不支持导航对象，多表关联等。ISelect.ToUpdate 可将查询转为 IUpdate，以便使用导航对象更新数据，如下：

fsql.Select<T1>().Where(a => a.Options.xxx == 1)
  .ToUpdate()
  .Set(a => a.Title, "111")
  .ExecuteAffrows();

注意：此方法不是将数据查询到内存再更新，上面的代码产生如下 SQL 执行：

UPDATE `T1` SET Title = '111' WHERE id in (select a.id from T1 a left join Options b on b.t1id = a.id where b.xxx = 1)

复杂更新使用该方案的好处：

• 更新前可预览测试数据，防止错误更新操作；
• 支持复杂的更新操作，例如：ISelect 上使用 Limit(10) 更新附合条件的前 10 条记录；

11、联表更新 UpdateJoin

v3.2.692+（高风险操作，高风险操作，高风险操作，请谨慎谨慎谨慎使用，测试并核对 ToSql 返回的内容）

fsql.Update<T1>()
  .Join<T2>((a, b) => a.id == b.groupid)
  .Set((a, b) => a.bname == b.name) //其他表字段
  .Set((a, b) => a.bcode == b.id + a.code)
  .Set(a => a.flag, 1) //固定值
  .Where((a, b) => a.id > 0 && b.id > 0)
  .ExecuteAffrows();

不同数据库产生的 SQL 不一样，以 MySql 为例：

UPDATE `T1` a
INNER JOIN `T2` b ON (a.`id` = b.`groupid`)
SET a.`bname` = b.`name`, a.`bcode` = concat(b.`id`, a.`code`), a.`flag` = 1
WHERE a.`id` > 0 AND b.`id` > 0

更复杂的联表更新：

var query = fsql.Select<T2, T3>()
  .InnerJoin(...)
  .Where(...)
  .WithTempQuery((a, b) => new { item1 = a, item2 = b });

fsql.Update<T1>()
  .Join(query, (a, b) => a.id == b.item1.groupid)
  .Set((a, b) => a.bcode == b.item2.xcode)
  .ExecuteAffrows();

UPDATE `T1` a
INNER JOIN (
  SELECT ...
  FROM `t2` a
  INNER JOIN ...
  Where ...
) b ON (a.`id` = b.`groupid`)
SET a.`bcode` = b.`xcode`

12、BulkCopy 批量更新

程序包	扩展方法	说明 (v3.2.693)
FreeSql.Provider.SqlServer	ExecuteSqlBulkCopy
FreeSql.Provider.MySqlConnector	ExecuteMySqlBulkCopy
FreeSql.Provider.Oracle	ExecuteOracleBulkCopy
FreeSql.Provider.Dameng	ExecuteDmBulkCopy	达梦
FreeSql.Provider.PostgreSQL	ExecutePgCopy
FreeSql.Provider.KingbaseES	ExecuteKdbCopy	人大金仓

原理：使用 BulkCopy 将数据插入到临时表，再使用 UPDATE FROM JOIN 联表更新。

提示：当更新的字段数量超过 3000 时，收益较大。

fsql.Update<T1>().SetSource(list).ExecuteSqlBulkCopy();

参考资料

• 《数据库事务》
• 《学习FreeSql之一：添加数据》
• 《学习FreeSql之二：删除数据》
• 《学习FreeSql之三：查询数据》
• 《仓储层Repository》
• 《过滤器、全局过滤器》
• 《UnitOfWork》

API

方法	返回值	参数	描述
SetSource	<this>	T1 | IEnumerable<T1>	更新数据，设置更新的实体
IgnoreColumns	<this>	Lambda	忽略的列
Set	<this>	Lambda, value	设置列的新值，Set(a => a.Name, "newvalue")
Set	<this>	Lambda	设置列的的新值为基础上增加，Set(a => a.Clicks + 1)，相当于 clicks=clicks+1
SetDto	<this>	object	根据 DTO 更新的方法
SetRaw	<this>	string, parms	设置值，自定义 SQL 语法，SetRaw("title = @title", new { title = "newtitle" })
Where	<this>	Lambda	表达式条件，仅支持实体基础成员（不包含导航对象）
Where	<this>	string, parms	原生sql语法条件，Where("id = @id", new { id = 1 })
Where	<this>	T1 | IEnumerable<T1>	传入实体或集合，将其主键作为条件
CommandTimeout	<this>	int	命令超时设置(秒)
WithTransaction	<this>	DbTransaction	设置事务对象
WithConnection	<this>	DbConnection	设置连接对象
ToSql	string		返回即将执行的 SQL 语句
ExecuteAffrows	long		执行 SQL 语句，返回影响的行数
ExecuteUpdated	List<T1>		执行 SQL 语句，返回更新后的记录
Join	IUpdateJoin		联表更新

Basic

• 入门 Getting Started
• 安装 How to Install
• 添加 Insert Data
  • 单条 Single
  • 批量 Batch
• 删除 Delete Data
• 修改 Update Data
  • 更新实体 Entity
  • 更新指定列 Specified Columns
• 添加或修改 Insert or Update ✨
• 查询 Query Data
  • 分页 Pagination
  • 单表 Single Table
  • 多表 Multi Tables
  • 分组聚合 Group Aggregation
  • 嵌套查询 Nested Query ✨
  • 联合查询
  • 返回数据 Return Data ✨
  • 延时加载 Lazy Loading
  • 贪婪加载 Greed Loading ✨
  • LINQ 扩展 LinqToSql
  • 树型查询 Parent-Child Relp. ✨
• 仓储层 Repository Layer
  • 工作单元 Unit of Work
  • 联级保存 Cascade Saving
  • 联级删除 Cascade Deletion
  • 工作单元管理器 ✨
  • 聚合根（实验室）
• CodeFirst
  • 实体特性✨
  • FluentApi
  • 自定义特性
  • 类型映射
  • 导航属性 Entity Relationship ✨
  • 迁移结构
• DbFirst
• 表达式函数
  • 字符串
  • 日期/时间
  • 其他
  • 自定义函数
• 事务
• 过滤器
• ADO
• AOP✨
• 读写分离
• 分表分库
• 租户
• 性能
• 动态操作 Dynamic Operations
• 你不知道的功能✨
• API参考

使用指南

• Dapper比较
• EntityFramework比较

学习与交流

• 常见问题
• 提交Issues

信息

• 服务支持
• 更新日志