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; }
}
1
2
3
4
5
6
7
8
9
10
11
12

# 动态条件

fsql.Update<Topic>(object dywhere)
1

dywhere 支持

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

# 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)
1
2
3
4
5

支持 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` = `Clicks` + 1, `Time` = now() 
//WHERE (`Id` = 1)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 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)
1
2
3
4
5
6
7

# 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)
1
2
3
4
5
6

是不是觉得先查询再更新,啰嗦?

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)
1
2
3
4
5
6
7

方法2:(原始)

//v1.5.0 忽略更新 null 值的属性
fsql.Update<Topic>()
  .SetSourceIgnore(item, col => col == null)
  .ExecuteAffrows();
1
2
3
4
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))
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
36
37
38
39
40
41
42
43
44
45

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

# 4、自定义SQL

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

# 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();
1
2
3
4
5
6
7
8
9
10

# 6、列优先级

全部列(SetSource) < 指定列(UpdateColumns) < 忽略列(IgnoreColumns) < 指定列(Set/SetRaw)
1

使用 Set/SetRaw 只有指定的列会保存;

使用 SetSource 实体所有列将被保存,可以通过 UpdateColumns/IgnoreColumns 设置更新/忽略的列;

# 7、乐观锁

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

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

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

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

# 8、悲观锁

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

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

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

# 9、ISelect.ToUpdate 高级更新

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

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

注意:此方法不是将数据查询到内存再更新,上面的代码产生如下 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)
1

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

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

# 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> 传入实体或集合,将其主键作为条件
WhereExists <this> ISelect 子查询是否存在
CommandTimeout <this> int 命令超时设置(秒)
WithTransaction <this> DbTransaction 设置事务对象
WithConnection <this> DbConnection 设置连接对象
ToSql string 返回即将执行的SQL语句
ExecuteAffrows long 执行SQL语句,返回影响的行数
ExecuteUpdated List<T1> 执行SQL语句,返回更新后的记录