UPSERT
sdrapkin edited this page Mar 26, 2017
·
2 revisions
PostgreSQL defines UPSERT as follows:
UPSERT is a DBMS feature that allows a DML statement's author to atomically either
insert
a row, or on the basis of the row already existing,update
that existing row instead, while giving no further thought to concurrency. One of those two outcomes must be guaranteed, regardless of concurrent activity, which has been called "the essential property of UPSERT".
TinyORM provides QB.Upsert()
helper, which uses MERGE
command to build UPSERT:
public class Person
{
public string Name { get; set; } = "[Default]";
public Guid Id { get; set; } = SequentialGuid.NewSequentialGuid();
public DateTime BirthDate { get; set;} = System.Data.SqlTypes.SqlDateTime.MinValue.Value;
}
var p = new Person()
{
Id = new Guid("cf9fad7a-9775-28b9-7693-11e6ea3b1484"),
Name = "John",
BirthDate = new DateTime(1975,03,17)
};
var query = QB.Upsert(p);
query.Dump(); // shows nicely in LinqPad - comment out otherwise
// [QueryInfo]
// [SQL] : ";WITH S([Name],[Id],[BirthDate]) AS (SELECT @@Name,@@Id,@@BirthDate)
// MERGE [Person] WITH (HOLDLOCK) T USING S ON S.Id=T.Id
// WHEN NOT MATCHED THEN
// INSERT ([Name],[Id],[BirthDate]) VALUES (@@Name,@@Id,@@BirthDate)
// WHEN MATCHED THEN
// UPDATE SET [Name]=@@Name,[Id]=@@Id,[BirthDate]=@@BirthDate"
// [ParameterMap] : [Key : Value]
// "@@Name" : "John"
// "@@Id" : cf9fad7a-9775-28b9-7693-11e6ea3b1484
// "@@BirthDate" : 1975-03-17 00:00:00
await db.QueryAsync(query); // executing the upsert query
HOLDLOCK
(SERIALIZABLE
) query hint ensures that the UPSERT is atomic under all transaction isolation levels.
The full Upsert<T>
signature is:
QueryInfo Upsert<T>(
T obj,
string tableName = null, // custom Target table name (class T name is default)
Predicate<string> excludedInsertProperties = null, // property names to exclude for INSERT
Predicate<string> includedUpdateProperties = null, // property names to only-include for UPDATE
string mergeOnSql = null) // custom MERGE condition ("Id" equality is default)
where T : class
Copyright (c) 2016-2022 Stan Drapkin