15

Update Data

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

Table of Contents

• Dynamic Conditions
• Dynamic tablename
• 1. Update the specified column
• 2. Update Conditions
• 3. Update the Entity
• 4. Custom SQL
• 5. Update According to the DTO
• 6. The difference between Set, SetSource and SetDto
• 7、Dictionary Update
• 8. Optimistic Lock
• 9. Pessimistic Lock
• 10. Advanced Update: ISelect.ToUpdate
• 11、UpdateJoin
• 12、BulkCopy Batch Update
• Reference
• 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 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 }

Dynamic tablename

fsql.Update<Topic>(1).AsTable("Topic_201903").ExecuteAffrows();

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); //Compare the changes before and after the snapshot.
//UPDATE `Topic` SET `Title` = @p_0
//WHERE (`Id` = 1)

Do you think it’s verbose to query first and then update?

var repo = fsql.GetRepository<Topic>();
var item = new Topic { Id = 1 };
repo.Attach(item); //Snapshot item at this time
item.Title = "newtitle";
repo.Update(item); //Compare the changes before and after the snapshot.
//UPDATE `Topic` SET `Title` = @p_0
//WHERE (`Id` = 1)

Method 2: (Original)

//v1.5.0 Ignore properties that update null values
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))

After the specified Set column is updated, SetSource will become invalid

SetSource defaults to relying on the IsPrimary attribute of the entity, and temporary primary keys can use SetSource (items, a=>a. Code)

Oracle CASE when N '' character set mismatch,

• Reason: Inconsistency between entity primary key Column DbType and table type,

• Solution: [Column (DbType = "varchar2", StingLength = 255)]

4. Custom SQL

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

5. Update According to the 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. The difference between Set, SetSource and SetDto

The three of them are functions of the same level, corresponding to:

• Set/SetRaw is used when the entity is known, corresponding to update t set x = x

• SetSource updates the entire entity, you can use UpdateColumns and/or IgnoreColumns to specify or ignore fields

• SetDto is a batch operation of Set

7、Dictionary Update

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

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

8. Optimistic Lock

When updating the entire entity data, it is very easy to cause the old data to update the new record in the case of concurrency.

The principle of optimistic locking: use a certain field of the entity, such as long version. Query the data before updating, and then version is 1. The SQL generated during the update will append where version = 1, and an exception (DbUpdateVersionException) will be thrown when the modification fails (ie, Affrows == 0).

Each entity only supports one optimistic lock attribute, mark the attribute before the property: [Column(IsVersion = true)].

Applicable to SetSource update, the value of version will increase by 1 each time it is updated.

9. Pessimistic Lock

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

ForUpdate is a common way of writing in Oracle/PostgreSQL/MySql. We have made a special adaptation to SqlServer. The SQL statements executed are roughly as follows:

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

10. Advanced Update: ISelect.ToUpdate

IUpdate does not support navigation objects, multi-table association, etc. by default. ISelect.ToUpdate can convert the query to IUpdate to update the data using the navigation object, as follows:

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

Note: This method is not to query the data to the memory and then update, the above code produces the following SQL execution:

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)

The benefits of using this program for dang complex update:

• Data can be previewed before updating to prevent wrong update operations;
• Support complex update operations, for example: Use Limit(10) on ISelect to update the first 10 records that meet the conditions;

11、UpdateJoin

v3.2.692+ (High risk operation, high risk operation, high risk operation, please use with caution, test and check the content returned by 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) //Fixed value
  .Where((a, b) => a.id > 0 && b.id > 0)
  .ExecuteAffrows();

The SQL generated by different databases is different. Take MySql as an example:

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

More complex joint table update:

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 Batch Update

package name	method	desc (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	人大金仓

Principle: Use BulkCopy to insert data into the temporary table, and then use UPDATE FROM JOIN to update the associated table.

Tip: When the number of updated fields exceeds 3000, the benefits are large.

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

Reference

• 《Database Transaction》
• 《FreeSql 101, Part 1: Insert Data》
• 《FreeSql 101, Part 2: Delete Data》
• 《FreeSql 101, Part 4: Query Data》
• 《Repository Layer》
• 《Filters and Global Filters》
• 《UnitOfWork》

API

Methods	Return	Parameters	Description
SetSource	<this>	T1 | IEnumerable<T1>	Update data, set updated entity
IgnoreColumns	<this>	Lambda	Ignored columns
Set	<this>	Lambda, value	Set the new value of the column Set(a => a.Name, "newvalue")
Set	<this>	Lambda	Set the new value of the column based on the original value Set(a => a.Clicks + 1), which is equivalent to clicks=clicks+1
SetDto	<this>	object	Update according to DTO
SetRaw	<this>	string, parms	Set value, custom SQL syntax SetRaw("title = @title", new {title = "newtitle" })
Where	<this>	Lambda	Expression conditions, only support entity members (not including navigation objects)
Where	<this>	string, parms	Raw SQL syntax conditions Where("id = @id", new {id = 1 })
Where	<this>	T1 | IEnumerable<T1>	Pass in the entity or collection, and use its primary key as the condition
CommandTimeout	<this>	int	Command timeout setting (seconds)
WithTransaction	<this>	DbTransaction	Set transaction object
WithConnection	<this>	DbConnection	Set the connection object
ToSql	string		Return the SQL statement to be executed
ExecuteAffrows	long		Execute SQL statement and return the number of rows affected
ExecuteUpdated	List<T1>		Execute SQL statement and return the updated record
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

信息

• 服务支持
• 更新日志