UPSERT

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.

Upsert customization

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