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