Contrib request for upsert method

[ghost]

It would be nice (and easy) to have a upsert method in the standard package based on primary key 0.

[NickCraver]

What if the primary key is not an integer? V2 will support generic args - are we comparing to default(T)? If so, what then is the behavior when 0 is valid? It's perfectly valid behavior to start a integer primary key identity column at 0 for example:

Create Table MyTable (
    Id int Primary Key Identity(0,1) Not Null,

I'm not fundamentally against an Upsert<T, TKey> (to match the new Insert<T, TKey>), but I'm not sure what the differentiator would be based on. Unless every provider implements a "try update and if rows = 0 then insert", which also has race semantics in play but is more generally applicable. It'd be nice to skip the shenanigans and just do an insert if we knew that was the case though.

[ghost]

In my opinon 0, null, or its default value regarding type would be a candidate for insert. But shure I agree it it is not foolproof.

If it is clearly stated along with the method use, I beleve it would make sense to most developers. And also the fact that one could use native insert/update at will.

[R2D221]

In my opinion, it's not about looking for the default(T) key. Instead, the way upsert should work is by looking if an entry already exists with the same primary key before deciding to insert or update.

Of course, if you're intentionally using a table with a valid 0 identity key, you should set your keys to -1 (or other invalid value, or maybe even null) for the new records.

The problem here is about what to do in the query to get such results. The obvious solution in SQL Server is a MERGE, with the table as TARGET and the upsert record as SOURCE. However, if I understand correctly, not all DBs support the MERGE operation, so the query would be specifically created for each possible DB (and maybe a fallback with separate try update and insert operations).

[gismofx]

Any interest in collaborating? I've written an UpsertAsync method which works for sqlite and mysql/mariadb.

I'd like to use the private helper methods in SqlMapperExtensions.cs like GetTableName. Is there a way to access them without having to fork the whole repo?

Could the private methods be re-scoped to public Inside a separate namespace?

[gismofx]

Here's what I put together for upsert and is more a Contrib-style method: https://github.com/gismofx/DapperUpsert

Would be easy to add Postgres support.

Should I attempt to fork and integrate with Dapper and/or Dapper Contrib?