FreeSql supports rich methods for updating data, including single or batch updates, and returns updated record values when executed in a specific database.

var connstr = "Data Source = 127.0.0.1; Port=3306; User ID=root; Password=root;" + 
    "Initial Catalog=cccddd; Charset=utf8; SslMode=none; Max pool size=10";

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

[Table(Name = "tb_topic")]
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; }}Copy the code

Batch update

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

var t6 = fsql.Update<Topic>().SetSource(items).ExecuteAffrows();
//UPDATE `tb_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
/ / the WHERE (` Id `,2,3,4,5,6,7,8,9,10) (1) IN
Copy the code

Batch update scenario, first query 10 records, according to the local very complex rules to change the value of the set

The traditional way is to loop 10 times and save, case when just once

Batch updates, ignoring some columns

fsql.Update<Topic>().SetSource(items).IgnoreColumns(a => new { a.Clicks, a.CreateTime }).ExecuteAffrows();
//UPDATE `tb_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
/ / the WHERE (` Id `,2,3,4,5,6,7,8,9,10) (1) IN
Copy the code

Update specified columns in batches

//UPDATE `tb_topic` SET `CreateTime` = ? p_0
/ / the WHERE (` Id `,2,3,4,5,6,7,8,9,10) (1) IN
Copy the code

The batch save becomes invalid after the specified column is updated

Internal implementation

When updating a large amount of data, internal split batch execution logic is used. The segmentation rules are as follows:

	The number of	And the number of
MySql	5000	3000
PostgreSQL	5000	3000
SqlServer	1000	2100
Oracle	500	999
Sqlite	5000	999

Data: indicates the split size of each batch. For example, insert 10000 pieces of data in a batch and split them into two batches during mysql execution. Number of parameters: specifies the number of parameters to be split in each batch. For example, if 10000 data is inserted in a batch, five parameters are required for each row. The number of parameters to be split in each batch is 3000/5.

After the split execution, when the external transaction is not provided, the internal self-open transaction, to achieve insertion integrity.

FreeSql ADAPTS every data type for parameterized, as well as non-parameterized use. You are advised to disable the parameterization function and run.nonoparameter ().

API

methods	The return value	parameter	describe
SetSource	<this>	T1 | IEnumerable	Update the data and set the updated entity
IgnoreColumns	<this>	Lambda	Ignore the column
Set	<this>	Lambda, value	Set(a => a.name, “newValue “)
Set	<this>	Lambda	Set(a => a.clicks +1), equivalent to clicks=clicks+1
SetDto	<this>	object	Update method according to DTO
SetRaw	<this>	string, parms	SetRaw(“title =? title”, new { title = “newtitle” })
Where	<this>	Lambda	Expression conditions that support only base members of entities (not navigation objects)
Where	<this>	string, parms	Where(“id =? id”, new { id = 1 })
Where	<this>	T1 | IEnumerable	Pass in an entity or collection with its primary key as a condition
WhereExists	<this>	ISelect	Whether the subquery exists
WithTransaction	<this>	DbTransaction	Setting transaction objects
ToSql	string		Returns the SQL statement to be executed
ExecuteAffrows	long		Execute the SQL statement and return the number of affected rows
ExecuteUpdated	List<T1>		Execute the SQL statement to return the updated record

Series article navigation

• (1) Introduction

• (2) Automatic migration of entities

• (3) Entity characteristics

• (4) Solid features of Fluent Api

• (5) Insert data

• (6) Batch insert data

• (7) Ignore columns when inserting data

• (8) Specify columns when inserting data

• (9) Delete data

• (x) Update data

• (11) Update data Where

• (12) Specify columns when updating data

• (13) Ignore columns when updating data

• (14) Batch update data

• (15) Query data

• (16) paging query

• (17) joint table query

• (18) Navigation attributes

• (19) multi-table query

• (20) query where ecascade

• (21) Query returned data

• (22) Dto mapping query

• (23) Grouping and aggregation

• (24) Introduction To Linq To Sql syntax

• (25) delayed loading

• Include, IncludeMany, Dto, ToList

• (27) the SQL statement has been written, and entity class mapping for the second query

• (28) Business

• Lambda expression

• (30) Reading and writing separation

• (31) Zoning table

• (32) Aop

• CodeFirst type mapping

• (34) CodeFirst migration instructions

• CodeFirst custom features

The resources

	Beginner’s Guide | “Select” | “Update” | “Insert” | “Delete”
	Expression function | “CodeFirst” | “DbFirst” | “The BaseEntity”
	“Repository” | “The UnitOfWork” | The Filter | Optimism Lock | “The DbContext”
	Unread | Partition table | “The tenants” | The AOP | Black Tech | Update log