How would you import a large CSV or external dataset with EF Core without exhausting memory or creating duplicates?

Design a large EF Core import with change tracker control

Explains batching, tracking control, idempotency, validation, and operational safety for large EF Core imports.

1Say itAnswer out loud first

2CompareCompare with Core answer

3RefineRefine with Stronger and Advanced

TL;DR

I would process the file in batches instead of loading everything into memory. Each row needs validation, normalization, and a stable identity such as an external id or natural key. EF Core can save batches with bounded SaveChanges calls, then clear the change tracker or use a fresh context per batch to avoid memory growth. The import needs a status record so users can see progress and failures.

Full answer below

Practice focus

Batching, SaveChanges strategy, change tracker behavior, validation, idempotent keys, transactions, and progress.

Answer first

Answer path

Core answer first, then checkpoints toward a stronger answer.

Start
Core answer
Start here. This is the direct spoken answer to practice first.
Why this question matters
Imports are a strong real-world test because they combine database constraints, EF Core memory behavior, validation, progress, and retry safety.
I would process the file in batches instead of loading everything into memory. Each row needs validation, normalization, and a stable identity such as an external id or natural key. EF Core can save batches with bounded SaveChanges calls, then clear the change tracker or use a fresh context per batch to avoid memory growth. The import needs a status record so users can see progress and failures.
Checkpoint
Stronger answer
Optional upgrade with examples, trade-offs, and sharper phrasing.
I would make the import idempotent. Re-running the same file after a failure must not create duplicates, so I would use unique constraints and upsert-like logic where appropriate. I would decide the transaction boundary carefully: one transaction for the whole file can be too large, while one per row is slow and hard to recover. Batch-level transactions with checkpoints often give a better balance. Failed rows need clear reporting without losing the whole import unnecessarily.
Finish
Advanced answer
Optional deeper angle for production judgment and nuance.
In production I would treat imports as background jobs with limits and observability. I would monitor rows per second, failed rows, database duration, lock waits, memory, and queue age. I would throttle import concurrency so normal API traffic is not starved. If the import changes derived data, I would plan cache invalidation, search reindexing, or outbox events. The design needs a repair path for bad mappings and partial imports.

Review signals

Use these to catch weak phrasing, shallow reasoning, and risky claims.

Common mistakesCandidateCommon mistakesMistakes that weaken the answer or signal shallow understanding.Open details

Details

Loading the entire file and all entities into one DbContext.
No stable key for detecting duplicate rows.
One huge transaction that locks too much for too long.
No progress, failure report, or retry strategy.

Red flagsCandidateRed flagsSignals that an interviewer may treat as risky.Open details

Details

Cannot explain change tracker memory growth.
Has no idempotency key.
Ignores partial failure and retry.
No protection for normal API traffic during imports.

Follow-up prompts

Quick interviewer prompts inside this drill.

Follow-up questionsCandidateFollow-up questionsLikely next questions after the main answer.Open details

Details

Treat these as the next interviewer turn. Answer one out loud, then save the gap in your practice notes.

When would you clear the change tracker?
How would you resume a failed import?
What database constraints help here?

Continue practice

Move to the linked follow-up, next path step, prerequisite, or deeper variant.

1 linked

Go deeper

Practice the next layer of the same subject.

Idempotent Upsert

Opening drill

How would you import a large CSV or external dataset with EF Core without exhausting memory or creating duplicates?

Practice focus

Answer path

Why this question matters

Review signals

Follow-up prompts

Continue practice

Go deeper

How would you import a large CSV or external dataset with EF Core without exhausting memory or creating duplicates?

Practice focus

Answer path

Why this question matters

Review signals

Follow-up prompts

Continue practice

Go deeper