How to write Merge which only updates some columns?

[DDSGooch]

How would I go about doing the equivalent to the following SQL in Linq2Db? I've used the Merge to update all columns in a record before but I'm not sure how or if it's possible to do this for a few selected columns.

SQL

CREATE TABLE Contacts (
    Name varchar(255),
    City varchar(255),
	Modified datetime NULL
);

INSERT Contacts (Name, City)
	VALUES ('John', 'London'),
			('Alex', 'NewYork'),
			('Fred', 'Sydney')

MERGE Contacts
USING (VALUES ('John'), ('Fred'))
       as Source (Name)  
ON Contacts.Name=Source.Name
WHEN MATCHED THEN
UPDATE SET Modified=GetDate();

SELECT * FROM Contacts

Having thought about this before, I was surprised to find that this actually seems to work

var names = new List<string> { "John", "Fred" };
await connection.Contacts
                    .Merge()
                    .Using(names.Select(n => new
                    {
                        Name = n,
                    }))
                    .On((target, source) => target.Name == source.Name)
                    .UpdateWhenMatched((t, s) => new Contact { Modified = DateTime.Now })
                    .MergeAsync();

The documentation (https://linq2db.github.io/articles/sql/merge/Merge-API-Description.html#setter-1) is not clear, as it says: "When not specified, source record values used to update target record". This can be read as Properties not specified or the setter is not specified and I'm not sure which it means.

Also there is another thread (#4239) which requests the Set() function, as used elsewhere in Linq2Db is added to the Merge, which I agree would be a nicer solution.

If you can clarify how this works, that would much appreciated.

[viceroypenguin]

"When not specified, source record values used to update target record".

This refers to the UpdateWhenMatched() overload that takes no parameters. If this overload is used, then the values in the source record are applied to the target record. This overload is only available/valid if TSource/TTarget are the same type.

[DDSGooch]

@viceroypenguin thanks for the quick response. I'd read that bit of documentation a few times and shown it to a colleague and we'd come to different conclusions. Thanks for answering that part of my question.

[MaceWindu]

update (and insert) operations support two modes:

• explicit setter using new Contact { Modified = DateTime.Now } init expressions to specify which columns to update with which value/expression
• implicit setter referenced by phrase When not specified, source record values used to update target record. In this case we will update/insert all columns except columns marked for ignore in mapping using SkipOnInsert/SkipOnUpdate flags using value from source record from same column as updated column. As mentioned already above this option supported only when source and target records mapped using same class (so we can map source column to target column). Note that it doesn't mean you can use it only when source and merge reference to same table. Source could be client side value (like in your example), subquery with .Select(r => new TargetRecord() { ... }) projection or table with same structure but different name (e.g. set using otherTable.TableName("my_source_table") method)

Issue you mentioned is about adding alternative explicit syntax using Set\Value per-column setters. Such setters could be handy for dynamic query generation as dynamic generation of (source, target) => new Contact { Modified = DateTime.Now } lambda expression is possible, but quite cumbersome