Insert or Update

中文 | English

1. IFreeSql.InsertOrUpdate

IFreeSql defines the InsertOrUpdate method, which uses the characteristics and functions of the database to implement the added or modified functions. (since 1.5.0)

Database	Features		Database	Features
MySql	on duplicate key update		Dameng	merge into
PostgreSQL	on conflict do update		Kingbase ES	on conflict do update
SqlServer	merge into		Shentong Database	merge into
Oracle	merge into		MsAccess	not support
Sqlite	replace into
Firebird	merge into

fsql.InsertOrUpdate<T>()
  .SetSource(items) //Data to be processed
  //.IfExistsDoNothing() //If the data exists, do nothing (that means, insert the data if and only if the data does not exist)
  .ExecuteAffrows();

When the entity class has auto-increment properties, batch InsertOrUpdate can be split into two executions at most. Internally, FreeSql will calculate the data without self-increment and with self-increment, and execute the two commands of insert into and merge into mentioned above (using transaction execution).

Note: the common repository in FreeSql.Repository also has InsertOrUpdate method, but their mechanism is different.

---

2. InsertOrUpdate in FreeSql.Repository

To use this method, you need to reference the FreeSql.Repository or FreeSql.DbContext extensions package.

var repo = fsql.GetRepository<T>();
repo.InsertOrUpdate(YOUR_ENTITY);

If there is data in the internal state management, then update it.

If there is no data in the internal state management, query the database to determine whether it exists.

Update if it exists, insert if it doesn't exist

Disadvantages: does not support batch operations

---

3. Batch Editing: BeginEdit

[Fact]
public void BeginEdit()
{
    fsql.Delete<BeginEdit01>().Where("1=1").ExecuteAffrows();
    var repo = fsql.GetRepository<BeginEdit01>();
    var cts = new[] {
        new BeginEdit01 { Name = "Category1" },
        new BeginEdit01 { Name = "Category1_1" },
        new BeginEdit01 { Name = "Category1_2" },
        new BeginEdit01 { Name = "Category1_3" },
        new BeginEdit01 { Name = "Category2" },
        new BeginEdit01 { Name = "Category2_1" },
        new BeginEdit01 { Name = "Category2_2" }
    }.ToList();
    repo.Insert(cts);

    repo.BeginEdit(cts); //Start editing `cts`

    cts.Add(new BeginEdit01 { Name = "Category2_3" });
    cts[0].Name = "123123";
    cts.RemoveAt(1);

    Assert.Equal(3, repo.EndEdit());
}
class BeginEdit01
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

When the above code EndEdit method is executed, 3 SQL statements are generated, as follows:

INSERT INTO "BeginEdit01"("Id", "Name") VALUES('5f26bf07-6ac3-cbe8-00da-7dd74818c3a6', 'Category2_3')


UPDATE "BeginEdit01" SET "Name" = '123123' 
WHERE ("Id" = '5f26bf00-6ac3-cbe8-00da-7dd01be76e26')


DELETE FROM "BeginEdit01" WHERE ("Id" = '5f26bf00-6ac3-cbe8-00da-7dd11bcf54dc')

Use case: In winform, after loading the table data, perform some operations such as adding, modifying, and deleting, and then click [Save]

Note: This operation is only valid for the variable cts, not for comparison and update of the entire table.

4. On Duplicate Key Update (MySql only)

FreeSql.Provider.MySql 和 FreeSql.Provider.MySqlConnector 支持 MySql 特有的功能，On Duplicate Key Update。

这个功能也可以实现插入或更新数据，并且支持批量操作。

class TestOnDuplicateKeyUpdateInfo {
    [Column(IsIdentity = true)]
    public int id { get; set; }
    public string title { get; set; }
    public DateTime time { get; set; }
}

var item = new TestOnDuplicateKeyUpdateInfo { id = 100, title = "title-100", time = DateTime.Parse("2000-01-01") };
fsql.Insert(item)
    .NoneParameter()
    .OnDuplicateKeyUpdate().ToSql();
//INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`, `time`) VALUES(100, 'title-100', '2000-01-01 00:00:00.000')
//ON DUPLICATE KEY UPDATE
//`title` = VALUES(`title`), 
//`time` = VALUES(`time`)

OnDuplicateKeyUpdate() 之后可以调用的方法：

方法名	描述
IgnoreColumns	忽略更新的列，机制和 IUpdate.IgnoreColumns 一样
UpdateColumns	指定更新的列，机制和 IUpdate.UpdateColumns 一样
Set	手工指定更新的列，与 IUpdate.Set 功能一样
SetRaw	作为 Set 方法的补充，可传入 SQL 字符串
ToSql	返回即将执行的 SQL 语句
ExecuteAffrows	执行，返回影响的行数

IInsert 与 OnDuplicateKeyUpdate 都有 IgnoreColumns、UpdateColumns 方法。

当插入实体/集合实体的时候，忽略了 time 列，代码如下：

fsql.Insert(item)
    .IgnoreColumns(a => a.time)
    .NoneParameter()
    .OnDuplicateKeyUpdate().ToSql();
//INSERT INTO `TestOnDuplicateKeyUpdateInfo`(`id`, `title`) VALUES(200, 'title-200')
//ON DUPLICATE KEY UPDATE
//`title` = VALUES(`title`), 
//`time` = '2000-01-01 00:00:00.000'

我们发现，UPDATE time 部分变成了常量，而不是 VALUES(`time`)，机制如下：

当 insert 部分中存在的列，在 update 中将以 VALUES(`字段`) 的形式设置；

当 insert 部分中不存在的列，在 update 中将为常量形式设置，当操作实体数组的时候，此常量为 case when ... end 执行（与 IUpdate 一样）；

---

5. On Conflict Do Update (PostgreSQL only)

FreeSql.Provider.PostgreSQL 支持 PostgreSQL 9.5+ 特有的功能，On Conflict(id) Do Update。

使用方法 MySql OnDuplicateKeyUpdate 大致相同。

class TestOnConflictDoUpdateInfo {
    [Column(IsIdentity = true)]
    public int id { get; set; }
    public string title { get; set; }
    public DateTime? time { get; set; }
}

var items = new [] {
    new TestOnConflictDoUpdateInfo { id = 200, title = "title-200", time = DateTime.Parse("2000-01-01") },
    new TestOnConflictDoUpdateInfo { id = 201, title = "title-201", time = DateTime.Parse("2000-01-01") },
    new TestOnConflictDoUpdateInfo { id = 202, title = "title-202", time = DateTime.Parse("2000-01-01") }
};
fsql.Insert(items)
    .IgnoreColumns(a => a.time)
    .NoneParameter()
    .OnConflictDoUpdate().ToSql();
//INSERT INTO ""testonconflictdoupdateinfo""(""id"", ""title"") VALUES(200, 'title-200'), (201, 'title-201'), (202, 'title-202')
//ON CONFLICT(""id"") DO UPDATE SET
//""title"" = EXCLUDED.""title"", 
//""time"" = CASE EXCLUDED.""id"" 
//WHEN 200 THEN '2000-01-01 00:00:00.000000' 
//WHEN 201 THEN '2000-01-01 00:00:00.000000' 
//WHEN 202 THEN '2000-01-01 00:00:00.000000' END::timestamp