-
Notifications
You must be signed in to change notification settings - Fork 854
Update Data
中文 | 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; }
}
fsql.Update<Topic>(object dywhere)
dywhere
supports:
- Primary key
new[] { PrimaryKey1, PrimaryKey2 }
- Topic Object
new[] { TopicObject1, TopicObject2 }
new { id = 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)
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)
In addition to the
dywhere
parameter described above, it also supports theWhere 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)
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 将失效
fsql.Update<Topic>()
.SetRaw("Title = @title", new { title = "新标题" })
.Where("Id = @id", 1)
.ExecuteAffrows();
//UPDATE `Topic` SET Title = @title WHERE (Id = @id)
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();
他们三个是平级功能,分别对应:
-
Set/SetRaw 在知道实体的时候使用,对应 update t set x = x
-
SetSource 更新整个实体,可以配合 UpdateColumns/IgnoreColumns 指定或忽略字段
-
SetDto 是 Set 的批量操作
更新整个实体数据时,在并发情况下极容易造成旧数据将新的记录更新。
乐观锁的原理,是利用实体某字段,如:long version,更新前先查询数据,此时 version 为 1,更新时产生的 SQL 会附加 where version = 1,当修改失败时(即 Affrows == 0)抛出异常(DbUpdateVersionException)。
每个实体只支持一个乐观锁属性,在属性前标记特性:[Column(IsVersion = true)] 即可。
适用 SetSource 更新,每次更新 version 的值都会增加 1
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)
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 条记录;
- 《数据库事务》
- 《学习FreeSql之一:添加数据》
- 《学习FreeSql之二:删除数据》
- 《学习FreeSql之三:查询数据》
- 《仓储层Repository》
- 《过滤器、全局过滤器》
- 《UnitOfWork》
方法 | 返回值 | 参数 | 描述 |
---|---|---|---|
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语句,返回更新后的记录 |