PlateLabelMapping: Hybrid EF Core + Dapper Bulk Data Layer

A showcase ASP.NET Core Web API demonstrating a hybrid data-access strategy for a many-to-many relationship between PlateNumber and Label — using EF Core for single-row CRUD and Dapper + raw SQL (COPY / UNNEST) for bulk operations.

Built around a real production trade-off: EF Core is great until you need to insert/upsert tens of thousands of rows, at which point its change tracker and per-entity hydration become the bottleneck. This project keeps both tools in their lane.

---

Why hybrid

Operation	Tool	Reason
Single-row CRUD	EF Core	Change tracking, navigation properties, migrations — all the productivity wins.
Bulk UPDATE / DELETE by predicate	EF Core (ExecuteUpdateAsync / ExecuteDeleteAsync)	Server-side single statement; identical to raw SQL.
Bulk INSERT of new rows	Dapper + COPY FROM STDIN	EF Core's AddRange melts down on the change tracker past ~10k rows. COPY is 50–100× faster.
Bulk upsert (ON CONFLICT DO NOTHING)	Dapper + raw SQL	EF Core has no native upsert.
Bulk attach/detach mapping rows	Dapper + UNNEST	Single round-trip, no entity hydration, no N+1.

The rule

If the operation touches one row, use EF Core. If it filters and mutates by predicate, use EF Core's ExecuteUpdate / ExecuteDelete. If it inserts or upserts a list, use BulkMappingRepository.

This removes the decision from every PR.

---

Stack

• .NET 8 · ASP.NET Core Web API
• PostgreSQL (Npgsql)
• EF Core 8 · Dapper 2
• Swagger / OpenAPI

---

Domain model

PlateNumber (id, number, created_at, updated_at)
   │
   │  many-to-many via
   ▼
plate_number_labels (plate_number_id, label_id, assigned_at, assigned_by)
   ▲
   │
Label (id, name, color, created_at)

The join is a first-class entity (PlateNumberLabel) so the payload columns (assigned_at, assigned_by) are addressable from both EF Core and raw SQL.

---

Use cases covered

CRUD (EF Core)

Use case	Endpoint
List plates (paged)	GET /api/plates?page=&pageSize=
Get one plate	GET /api/plates/{id}
Create one plate	POST /api/plates
Update plate number only (labels untouched)	PUT /api/plates/{id}
Delete plate	DELETE /api/plates/{id}
List labels	GET /api/labels
Get one label	GET /api/labels/{id}
Create label	POST /api/labels
Update label	PUT /api/labels/{id}
Delete label	DELETE /api/labels/{id}

Bulk (Dapper / raw SQL)

Use case	Endpoint	SQL pattern
Bulk insert plate numbers (100k+)	POST /api/plates/bulk	COPY FROM STDIN BINARY + INSERT … ON CONFLICT DO NOTHING
60k plates → one label, in one shot	POST /api/labels/{labelId}/plates/bulk-create	COPY into temp + upsert plates + attach label to all matched (one transaction, idempotent)
Attach one label to N existing plate ids	POST /api/labels/{labelId}/plates	INSERT … FROM UNNEST(@ids) ON CONFLICT DO NOTHING
Attach N labels to one plate	POST /api/plates/{plateId}/labels	UNNEST
Detach one label from N plate ids	DELETE /api/labels/{labelId}/plates	DELETE … WHERE label_id = @id AND plate_number_id = ANY(@ids)
Detach N labels from one plate	DELETE /api/plates/{plateId}/labels	DELETE … = ANY
Detach a label from every plate it's on	DELETE /api/labels/{labelId}/plates/all	Single DELETE
Replace label set on a plate (sync)	PUT /api/plates/{plateId}/labels	CTE: DELETE not-in-list + INSERT new
Replace plate set on a label (sync)	PUT /api/labels/{labelId}/plates	CTE: DELETE not-in-list + INSERT new
Apply many arbitrary (plate, label) pairs	POST /api/mappings/bulk	UNNEST(arr1, arr2) two parallel arrays
List labels on a plate	GET /api/plates/{plateId}/labels	JOIN
List plates for a label (paged)	GET /api/labels/{labelId}/plates?page=&pageSize=	JOIN + COUNT

Loading-strategies showcase (EF Core)

Three endpoints to compare what hits the wire:

Endpoint	Strategy	Round-trips
GET /api/plates/{id}/eager	Eager — Include(p => p.Labels).AsSplitQuery()	1–2 (split)
GET /api/plates/{id}/explicit	Explicit — Find + Entry().Collection().LoadAsync()	2 (deliberate)
GET /api/plates/{id}/lazy	Lazy — UseLazyLoadingProxies(), nav touched in serializer	N+1 risk

The lazy endpoint runs against a separate LazyAppDbContext so proxies don't leak into the rest of the app.

---

Bulk SQL patterns in detail

1. Bulk insert with idempotency — COPY FROM STDIN BINARY + temp table

The fastest path on Postgres. Used by BulkInsertPlateNumbersAsync.

CREATE TEMP TABLE _stage_plate_numbers (number TEXT NOT NULL) ON COMMIT DROP;
-- COPY _stage_plate_numbers (number) FROM STDIN (FORMAT BINARY)  (issued via NpgsqlBinaryImporter)
INSERT INTO plate_numbers (number)
SELECT s.number FROM _stage_plate_numbers s
ON CONFLICT (number) DO NOTHING;

2. The 60k-plates-with-one-label case — atomic upsert + attach

Used by BulkInsertPlatesWithLabelAsync. Pre-existing plates are reused, new ones are created, and the label is applied uniformly to all of them in the same transaction.

-- 1. Stage via COPY (binary)
-- 2. Upsert plates
INSERT INTO plate_numbers (number)
SELECT s.number FROM _stage_plates_with_label s
ON CONFLICT (number) DO NOTHING;

-- 3. Attach label to every plate that matches the staged numbers
INSERT INTO plate_number_labels (plate_number_id, label_id, assigned_at, assigned_by)
SELECT p.id, @LabelId, NOW(), @AssignedBy
FROM plate_numbers p
JOIN _stage_plates_with_label s ON s.number = p.number
ON CONFLICT (plate_number_id, label_id) DO NOTHING;

3. Attach one label to N plate ids — UNNEST

INSERT INTO plate_number_labels (plate_number_id, label_id, assigned_at, assigned_by)
SELECT pid, @LabelId, NOW(), @AssignedBy
FROM UNNEST(@PlateIds) AS pid
ON CONFLICT (plate_number_id, label_id) DO NOTHING;

One round-trip, no entity hydration, fully idempotent.

4. Sync — replace label set on a plate (additions + removals atomically)

Used by SetLabelsForPlateAsync. Single CTE, returns precise added / removed counts.

WITH deleted AS (
    DELETE FROM plate_number_labels
    WHERE plate_number_id = @PlateId
      AND label_id <> ALL(@LabelIds)
    RETURNING 1
),
inserted AS (
    INSERT INTO plate_number_labels (plate_number_id, label_id, assigned_at, assigned_by)
    SELECT @PlateId, lid, NOW(), @AssignedBy
    FROM UNNEST(@LabelIds) AS lid
    ON CONFLICT (plate_number_id, label_id) DO NOTHING
    RETURNING 1
)
SELECT
    (SELECT COUNT(*) FROM inserted)::int AS Added,
    (SELECT COUNT(*) FROM deleted)::int  AS Removed;

Empty @LabelIds is valid sync semantics — removes all labels from the plate.

5. Apply arbitrary pairs — two-array UNNEST

INSERT INTO plate_number_labels (plate_number_id, label_id, assigned_at, assigned_by)
SELECT p, l, NOW(), @AssignedBy
FROM UNNEST(@PlateIds, @LabelIds) AS t(p, l)
ON CONFLICT (plate_number_id, label_id) DO NOTHING;

---

Scale guidance

Where each tool fits — committed thresholds:

Row count	Insert	Update / delete by predicate
< 5,000	EF Core AddRange is fine	ExecuteUpdate / ExecuteDelete
5,000 – 50,000	Dapper + COPY	ExecuteUpdate / ExecuteDelete
50,000 – 1,000,000 (10 lakh)	Dapper + COPY (mandatory)	ExecuteUpdate / ExecuteDelete (still fine)
> 1,000,000	Dapper + COPY, chunked to 100k per request	Same

Rough numbers for inserting 1M rows on a local Postgres:

• EF Core AddRange + SaveChanges: 3–10 minutes (often OOM).
• EFCore.BulkExtensions.BulkInsert: 15–30 s.
• NpgsqlBinaryImporter (this repo's path): 3–8 s.

---

Run it

Requires .NET 8 SDK and a running PostgreSQL.

# 1. Update connection string in PlateLabelMapping.Api/appsettings.json
# 2. Restore tools and apply migrations on first run (auto on startup)
dotnet restore
dotnet build

# 3. Run
dotnet run --project PlateLabelMapping.Api

# Swagger UI at https://localhost:<port>/swagger

Smoke test flow (Swagger or curl):

1. POST /api/labels ×3 → suspicious, thief, wanted.
2. POST /api/labels/{suspiciousId}/plates/bulk-create with 10,000 numbers → returns platesInserted, mappingsInserted.
3. GET /api/labels/{suspiciousId}/plates?page=1&pageSize=50 → paged read via Dapper JOIN.
4. PUT /api/plates/{plateId}/labels with { "labelIds": [thiefId] } → sync to a single label, returns { added: 1, removed: 1 }.
5. GET /api/plates/{id}/eager vs /explicit vs /lazy → watch the SQL in console for the round-trip difference.
6. Re-issue any bulk-create call → second call returns 0 newly inserted (idempotent via ON CONFLICT).

---

Project layout

PlateLabelMapping.sln
PlateLabelMapping.Api/
├── Program.cs                          # DI, EF Core, Npgsql data source, Swagger, migrate-on-start
├── BulkLimitsOptions.cs                # Per-request size caps
├── appsettings.json                    # Connection string + bulk limits
├── Data/
│   ├── AppDbContext.cs                 # Both AppDbContext and LazyAppDbContext
│   └── Entities/
│       ├── PlateNumber.cs
│       ├── Label.cs
│       └── PlateNumberLabel.cs         # Join entity with payload
├── Repositories/
│   ├── IPlateNumberRepository.cs
│   ├── PlateNumberRepository.cs        # EF Core single-row CRUD
│   ├── ILabelRepository.cs
│   ├── LabelRepository.cs              # EF Core single-row CRUD
│   ├── IBulkMappingRepository.cs
│   └── BulkMappingRepository.cs        # Dapper + COPY + UNNEST — all bulk paths
├── Dtos/
│   ├── PlateNumberDtos.cs
│   ├── LabelDtos.cs
│   └── MappingDtos.cs
├── Controllers/
│   ├── PlateNumbersController.cs       # CRUD + 3 loading-strategy endpoints + bulk
│   ├── LabelsController.cs             # CRUD
│   └── PlateNumberLabelsController.cs  # All mapping endpoints
└── Migrations/                         # EF Core Init migration

---

Production touches

• TreatWarningsAsErrors=true — clean build, no warnings tolerated.
• All async with CancellationToken plumbed end-to-end.
• AsNoTracking() on every EF Core read path.
• Postgres unique-violation (SQLSTATE 23505) mapped to HTTP 409 Conflict.
• One shared NpgsqlDataSource (singleton) feeds both EF Core and Dapper — single connection-pool config.
• All bulk endpoints are idempotent via ON CONFLICT DO NOTHING, safe to retry.
• Per-request size caps configurable via the BulkLimits section (default: 100k plates / 50k plate-ids / 1k label-ids per request).
• Database.Migrate() on startup so the demo runs cleanly against a fresh database.

---

When not to reach for this pattern

• The data access in your app is exclusively single-row reads/writes — EF Core alone is enough; the second tool is overhead you don't need.
• Your "bulk" operation is < 5k rows and runs once a day — AddRange + SaveChanges is fine; don't over-engineer.
• You can express the operation as a predicate (UPDATE … WHERE … or DELETE … WHERE …) — use EF Core's ExecuteUpdateAsync / ExecuteDeleteAsync and skip Dapper entirely.

The hybrid pattern earns its keep specifically for high-cardinality INSERT / upsert / mapping-attach paths.

---

License

MIT