Upsert on conflict - Alias instead of exclude

[Inexad]

I currently have a problem with Upsert not updating my entries in database.

 await _context.Articles.UpsertRange(articles)
                .On(x => x.Id)
                .WhenMatched(x => new Article()
                {
                    AccessId = x.AccessId
                })
                .AllowIdentityMatch()
                .RunAsync(cancellationToken);

Above generates following:

INSERT INTO "Articles" AS "T" ("Id", "AccessId") 
VALUES ('12345', '0') 
ON CONFLICT ("Id") DO UPDATE SET "AccessId" =  "T"."AccessId"

That query will run without any errors but the AccessId in database won't be updated.

If i instead manually change the SQL to:

INSERT INTO "Articles" AS "T" ("Id", "AccessId") 
VALUES ('12345', '0') 
ON CONFLICT ("Id") DO UPDATE SET "AccessId" =  excluded."AccessId"

It works as intended.

Anyone have any idea what could be the problem here? I run .net 7, latest upsert package, postgresql 14.

[Inexad]

When i remove the .WhenMatched() it seems to work? Then it produces the following SQL:

INSERT INTO "Articles" AS "T" ("Id", "AccessId") 
VALUES ('12345', '0') 
ON CONFLICT ("Id") DO UPDATE SET "AccessId" =  excluded."AccessId"

Any ideas why this is?

[Inexad]

Anyone?

[xamele0n]

It seems that you should use another version of method. see..

        /// <summary>
        /// Specifies which columns should be updated when a matched entity is found.
        /// The second type parameter points to the entity that was originally passed to be inserted
        /// </summary>
        /// <param name="updater">The expression that returns a new instance of TEntity, with the columns that have to be updated being initialised with new values</param>
        /// <returns>The current instance of the UpsertCommandBuilder</returns>
        public UpsertCommandBuilder<TEntity> WhenMatched(Expression<Func<TEntity, TEntity, TEntity>> updater)
        {

Try this

await _context.Articles.UpsertRange(articles)
                .On(x => x.Id)
                .WhenMatched((_, x) => new Article()
                {
                    AccessId = x.AccessId
                })
                .AllowIdentityMatch()
                .RunAsync(cancellationToken);