Question: Proper way to Upsert

[ben-page-csat]

I'm trying to implement a Postgres Upsert using the MergeAsync method, but run into a strange behavior. RepoDB inserts 0 into identity column rather than allowing Postgres to create the next sequence value.

Here's a simplified example:

CREATE TABLE public.part_attributes (
	part_attribute_id int4 GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY,
	part_number varchar(50) NOT NULL,
	"name" varchar(200) NOT NULL,
	value varchar(200) NOT NULL
);

public class PartAttribute
{
    public int PartAttributeId { get; set; }
    public string PartNumber { get; set; }
    public string Name { get; set; }
    public string Value { get;  set; }
}

FluentMapper.Entity<PartAttribute>()
   .Table(@"part_attributes")
   .Primary(p => p.PartAttributeId)
   .Identity(b => b.PartAttributeId)
   .Column(b => b.PartAttributeId, "part_attribute_id")
   .Column(b => b.PartNumber, "part_number")
   .Column(b => b.Name, "name")
   .Column(b => b.Value, "value");

foreach (var attribute in part.Attributes)
{
    var id = await this.connection.MergeAsync(
        attribute,
        transaction: transaction,
        cancellationToken: cancellationToken);

    attribute.PartAttributeId = (int)id;
}

id is always 0, because RepoDB is inserting the property's default value.

I can work around this by specifying the columns in the merge.

var fields = Field.Parse<PartAttribute>(e => new
{
    e.PartNumber,
    e.Name,
    e.Value
});

foreach (var attribute in part)
{
    var id = await this.connection.MergeAsync(
        attribute,
        transaction: transaction,
        fields: fields,
        cancellationToken: cancellationToken);

    attribute.PartAttributeId = (int)id;
}

My question is: is there an easier way to accomplish this without than have specify the fields (or using an anonymous type)? The default behavior of RepoDB in this case seems like the least desirable behavior for most upsert use cases.

[rhuijben]

Current master at this repository doesn't detect generated/computed columns. I implemented support for this in the fork on https://github.com/ampscm/repodb / AmpScm.RepoDB.* on NuGet.

Behavior on mikependon/repodb depends on your database. In sqlite generated columns are completely ignored, while on others they are handled as normal columns... Both break hard under different circumstances.

[rhuijben]

By default RepoDB merges on the single primary key column. My forked version will take all primary key columns here. Don't think you can really workardound this here where the key you want to merge on is 100% different.

I think MergeAllAsync([attribute], ..) would set the PartAttributeId in the existing instance though. (Not entirely sure, but this is what .InsertAll() does)