Insert or Update

中文 | English

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)

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)
  //.UpdateSet((a, b) => a.Count == b.Count + 10)
  .ExecuteAffrows();

//or..
var sql = fsql.Select<T2, T3>()
  .ToSql((a, b) => new
  {
    id = a.id + 1,
    name = "xxx"
  }, FieldAliasOptions.AsProperty);

fsql.InsertOrUpdate<T>()
  .SetSource(sql)
  .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.

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

fsql.InsertOrUpdateDict(dic).AsTable("table1").WherePrimary("id").ExecuteAffrows();
//The generated SQL is the same as above

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

State management is a data replica that is automatically generated during queries. Only when using DbContext/Repository queries can state management be implemented.

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

Principle: Use BulkCopy to insert data into a temporary table, and then use MERGE INTO to join the table.

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

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

SELECT ... INTO #temp_T1 FROM [T1] WHERE 1=2

MERGE INTO [T1] t1 USING (select * from #temp_user1) t2 ON (t1.[id] = t2.[id])
WHEN MATCHED THEN
  update set ...
WHEN NOT MATCHED THEN
  insert (...)
  values (...);

DROP TABLE #temp_user1

var list = fsql.Select<T>().Where(...).ToList();

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