How to convert postgres query with returning clause into linq2db?

[ademchenko]

I have a pretty easy Postgres query but don't quite understand how to convert it into linq2db (UpdateWithOutput or whatsoever):

update persons set balance = balance + 100 where id = 1 returning balance;

This query updates the particular person and returns the updated value of balance. I was digging into the UpdateWithOutput API but failed to find a suitable override - they are either about source-target table update or contain references to deleted or inserted records but not to the updated ones.

Could you, please, help me with that?

[viceroypenguin]

You are looking in the right spot. In this case, you want UpdateWithOutput(), and the data you want is in the inserted record (so inserted.balance).

There is a bit of a misnomer when dealing with update/returning, which makes sense when you understand how the db engine handles update, but it is not intuitively obvious. Technically, (internally) an update is not a single operation, but actually both a delete and an insert. It is done atomically, but when processing rows, the db engine removes the old row and inserts a new row. This means that an update has both deleted and inserted output tables, which are used to reference and output the data that is removed and the data that is inserted.

[ademchenko]

Wow! Thank you :) I have already checked that in Postgres, it works perfectly.