Skip to content

Update Data

Jump to bottom
AlexLEWIS edited this page Aug 11, 2021 · 15 revisions

中文 | English

FreeSql provides a variety of database update functions. It supports single or batch updates, and can also return updated records when executed in a specific database.

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, connectionString)
    .UseAutoSyncStructure(true) //Automatically synchronize the entity structure to the database.
    .Build(); //Be sure to define as singleton mode

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

Dynamic Conditions

fsql.Update<Topic>(object dywhere)

dywhere supports:

  • Primary key
  • new[] { PrimaryKey1, PrimaryKey2 }
  • Topic Object
  • new[] { TopicObject1, TopicObject2 }
  • new { id = 1 }

1. Update the specified column

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

Support multiple calls to Set(), which is equivalent to splicing Sql statements.

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. Update Conditions

In addition to the dywhere parameter described above, it also supports the Where lambda/sql method

For safety reasons, when there are no conditions, the update action will not be executed to avoid updating the entire table data by mistake. Update the entire table data: fsql.Update<T>().Where("1=1").Set(a => a.Xxx == xxx).ExecuteAffrows()

fsql.Update<Topic>()
  .Set(a => a.Title, "New 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. Update the Entity

Method 1: (recommended)

Only update the changed properties (depend on FreeSql.Repository package)

var repo = fsql.GetRepository<Topic>();
var item = repo.Where(a => a.Id == 1).First();  //Snapshot item at this time
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 将失效

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、乐观锁

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

乐观锁的原理，是利用实体某字段，如：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

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

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

9、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 条记录；

参考资料

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语句，返回更新后的记录

Basic

使用指南

学习与交流

信息

Clone this wiki locally