Seven approaches to bulk-updating rows in PostgreSQL from .NET using Dapper and EF Core, from naive per-row updates to binary COPY. Each step cuts down on round-trips or removes dynamic SQL entirely.

MilanJovanovic's platform  covers topics related to software development, technology trends, and coding tutorials. Through articles, tutorials, and code examples, MilanJovanovic offers insights into programming languages, development frameworks, and best practices in software engineering. Readers can learn about software design principles, development methodologies, and emerging technologies to enhance their coding skills and build high-quality software applications.

Milan Jovanović

A benchmarked comparison of seven approaches for bulk updating rows in PostgreSQL from .NET, where each row requires a unique value (e.g., a per-row timestamp). The scenario involves marking orders as processed with individual timestamps. Results at 10,000 rows range from 2,414ms (naive per-row Dapper loop) down to 41ms (UNNEST or binary COPY). Key findings: round-trips are the primary bottleneck, not SQL execution speed. EF Core SaveChanges batches statements but still generates N individual UPDATEs. Single-statement approaches using a VALUES table, CTE, UNNEST, or temp table + binary COPY dramatically outperform. For PostgreSQL specifically, UNNEST (fixed query text, two array parameters, stable query plans) and binary COPY (no parameter limits, best for very large batches) are the recommended approaches. Dapper and EF Core can be mixed within the same transaction.

Approach 1: Naive Dapper, One UPDATE Per Row

Approach 2: EF Core SaveChanges, Batched Round-Trips

Approach 3: Dapper with a VALUES Table, One Statement, One Round-Trip

Approach 4: EF Core ExecuteSqlRaw, Same SQL Inside EF Core

Approach 5: Dapper CTE (WITH ... AS VALUES)

Approach 6: Dapper with UNNEST (PostgreSQL)