dotnet / dotnet/efcore

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

Open
#38,505 1 comment 0 reactions 0 assignees View on GitHub
area-bulkupdate customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

### 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:

```csharp
target.TotalQuantity = target.TotalQuantity + delta
```

But the generated SQL uses:

```sql
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:

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

because `s1` is the actual row being updated.

Example lost-update scenario:

```text
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

```csharp
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:

```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:

```sql
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

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the supplied ExecuteUpdateAsync LINQ query and inspecting the generated PostgreSQL SQL. Trace the ExecuteUpdate translation path to determine why the derived table supplies the updated column; done means the SET expression references the update target alias and concurrent increments preserve the combined total.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, postgresql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.