Additional update criteria

When you are executing upserts with Debaser, a MERGE statement is executed, matching potentially existing rows either by the Id property, or by a combination of all properties decorated with [DebaserKey].

However, sometimes you want to ensure that e.g. newer data is not overwritten by old data, which can usually be implemented either by checking a LastUpdated timestamp or a Revision field.

This mechanism can be implemented with Debaser as well, as it is possible to specify an additional criteria which must be satisfied for an update to be carried out.

This means that the usual logic

• IF an existing row matches by ID
  • UPDATE the row
• ELSE
  • INSERT the row

can be turned into

• IF an existing row matches by ID
  • IF an additional criteria is met
    • UPDATE the row
  • ELSE
    • do nothing
• ELSE
  • INSERT the row

which is done by applying the [DebaserUpdateCriteria(...)] attribute to your model. The attribute accepts a string as an argument, which must be a SQL predicate that formulates the criteria in terms of S (for "source row") and T (for target row) and the respective column names.

An example could be this:

[DebaserUpdateCriteria("[S].[Rev] > [T].[Rev]")]
class SomeRowWithIntegerRevision
{
    public SomeRowWithIntegerRevision(int id, string data, int rev)
    {
        Id = id;
        Data = data;
        Rev = rev;
    }

    public int Id { get; }
    public string Data { get; }
    public int Rev { get; }
}

where the [S].[Rev] > [T].[Rev] criteria then demands that the revision of the upserted row must ALWAYS be greater than that of an existing row, otherwise the update is not carried out.

This particular type of revision comparison criteria is probably very common, which is why the [DebaserRevision(...)] criteria support this particular scenario simply by passing the name of the property to be used for comparing revisions – IOW the above scenario can be easily implemented like this:

[DebaserRevision(nameof(Rev))]
class SomeRowWithIntegerRevision
{
    public SomeRowWithIntegerRevision(int id, string data, int rev)
    {
        Id = id;
        Data = data;
        Rev = rev;
    }

    public int Id { get; }
    public string Data { get; }
    public int Rev { get; }
}