ExecuteUpdate over projection uses derived-table copy of updated column, causing possible lost updates

[Dreamescaper]

Bug description

ExecuteUpdate over a projection can generate SQL where an increment expression uses a value read into a derived table instead of referencing the actual update target alias.

This can cause lost updates under concurrent executions.

The LINQ expression represents an atomic increment:

target.TotalQuantity = target.TotalQuantity + delta

But the generated SQL uses:

SET "TotalQuantity" = s2."TotalQuantity" + s2."QuantityDelta"

where s2."TotalQuantity" is read inside a derived table before the update.

For concurrent counter updates, the generated SQL should reference the update target alias:

SET "TotalQuantity" = s1."TotalQuantity" + s2."QuantityDelta"

because s1 is the actual row being updated.

Example lost-update scenario:

Initial TotalQuantity = 100

Transaction 1 delta = 10
Transaction 2 delta = 20

Both transactions can read s2.TotalQuantity = 100.

Transaction 1 updates row to 110.
Transaction 2 waits for the same row, then updates row to 100 + 20 = 120.

Expected final value: 130.
Possible final value: 120.

Your code

await context.ShortageOrderLines
    .Where(line => line.ShortageOrderId == orderId)
    .GroupBy(line => line.ShortageArticleTask!)
    .Select(g => new
    {
        Task = g.Key,
        QuantityDelta = g.Sum(l => l.Quantity)
    })
    .ExecuteUpdateAsync(setters => setters.SetProperty(
        t => t.Task.TotalQuantity,
        t => t.Task.TotalQuantity + t.QuantityDelta));

Generated SQL:

UPDATE shortage."ShortageArticleTasks" AS s1
SET "TotalQuantity" = s2."TotalQuantity" + s2."QuantityDelta"
FROM (
    SELECT s0."Id", s0."TotalQuantity", COALESCE(sum(s."Quantity"), 0.0) AS "QuantityDelta"
    FROM shortage."ShortageOrderLines" AS s
    LEFT JOIN shortage."ShortageArticleTasks" AS s0 ON s."ShortageArticleTaskId" = s0."Id"
    WHERE s."ShortageOrderId" = @orderId
    GROUP BY s0."Id", s0."ArticleId", s0."LogisticVariantId", s0."TotalQuantity"
) AS s2
WHERE s1."Id" = s2."Id"

Expected SQL shape:

UPDATE shortage."ShortageArticleTasks" AS s1
SET "TotalQuantity" = s1."TotalQuantity" + s2."QuantityDelta"
FROM (
    SELECT
        s."ShortageArticleTaskId",
        COALESCE(sum(s."Quantity"), 0.0) AS "QuantityDelta"
    FROM shortage."ShortageOrderLines" AS s
    WHERE s."ShortageOrderId" = @orderId
    GROUP BY s."ShortageArticleTaskId"
) AS s2
WHERE s1."Id" = s2."ShortageArticleTaskId"

Stack traces

No exception is thrown. The generated SQL executes successfully, but it may have incorrect concurrency semantics for counter increments.

EF Core version

10.0.8

Database provider

Npgsql.EntityFrameworkCore.PostgreSQL

Target framework

net10.0

Operating system

Amazon Linux

IDE

VS 2026

[Dreamescaper]

I did the other query, originating from updated DbSet:

        var linesDeltaQuery = context.ShortageOrderLines
            .Where(line => line.ShortageOrderId == orderId)
            .GroupBy(line => line.ShortageArticleTaskId)
            .Select(g => new { ShortageArticleTaskId = g.Key, QuantityDelta = g.Sum(l => l.Quantity) });

        await context.ShortageArticleTasks
            .Join(linesDeltaQuery, t => t.Id, d => d.ShortageArticleTaskId, (task, delta) => new { Task = task, delta.QuantityDelta })
            .ExecuteUpdateAsync(x => x.SetProperty(
                t => t.Task.TotalQuantity,
                t => t.Task.TotalQuantity + t.QuantityDelta));

This one generates a proper SQL:

UPDATE shortage."ShortageArticleTasks" AS s
SET "TotalQuantity" = s."TotalQuantity" + s1."QuantityDelta"
FROM (
    SELECT s0."ShortageArticleTaskId", COALESCE(sum(s0."Quantity"), 0.0) AS "QuantityDelta"
    FROM shortage."ShortageOrderLines" AS s0
    WHERE s0."ShortageOrderId" = @orderId
    GROUP BY s0."ShortageArticleTaskId"
) AS s1
WHERE s."Id" = s1."ShortageArticleTaskId"