Implement an EF Core import that does not track the entire file in one DbContext.

Input rows contain SourceRowId with a database unique constraint. Process rows in batches of 500. Each batch gets a fresh context and commits independently so memory and failure recovery are bounded. Existing source ids should be skipped.

A 50,000-row file uses roughly one batch of tracked entities at a time. If batch 40 fails, the first 39 remain committed and a rerun safely skips their source ids.

• Use a unique database key for SourceRowId.
• Create and dispose a DbContext per batch.
• Pass cancellation through reads and writes.
• Record progress and rejected rows outside this simplified method in a real workflow.

I chunk the input, open a short-lived context for one chunk, load existing source ids with one query, add only missing rows, and save. The unique constraint remains authoritative under concurrent imports. Fresh contexts bound the tracker and make retry checkpoints clear.

public static async Task<int> ImportAsync(
    IDbContextFactory<AppDbContext> contextFactory,
    IEnumerable<ImportRow> rows,
    CancellationToken cancellationToken)
{
    var imported = 0;
    foreach (var batch in rows.Chunk(500))
    {
        cancellationToken.ThrowIfCancellationRequested();
        imported += await ImportBatchAsync(
            contextFactory,
            batch,
            cancellationToken);
    }
    return imported;
}

private static async Task<int> ImportBatchAsync(
    IDbContextFactory<AppDbContext> contextFactory,
    ImportRow[] batch,
    CancellationToken cancellationToken)
{
    var sourceRows = batch
        .GroupBy(row => row.SourceRowId)
        .Select(group => group.First())
        .ToArray();

    for (var attempt = 1; attempt <= 3; attempt++)
    {
        await using var db =
            await contextFactory.CreateDbContextAsync(cancellationToken);

        var sourceIds = sourceRows
            .Select(row => row.SourceRowId)
            .ToArray();
        var existing = await db.ImportedRecords
            .Where(record => sourceIds.Contains(record.SourceRowId))
            .Select(record => record.SourceRowId)
            .ToHashSetAsync(cancellationToken);
        var newRecords = sourceRows
            .Where(row => !existing.Contains(row.SourceRowId))
            .Select(ImportedRecord.From)
            .ToList();

        if (newRecords.Count == 0)
            return 0;

        db.ImportedRecords.AddRange(newRecords);
        try
        {
            await db.SaveChangesAsync(cancellationToken);
            return newRecords.Count;
        }
        catch (DbUpdateException exception) when (
            attempt < 3 &&
            DatabaseErrors.IsUniqueViolation(
                exception,
                "ux_imported_records_source_row_id"))
        {
            // SaveChanges rolled back. A fresh context re-reads winners
            // and retries only the rows that are still missing.
        }
    }

    throw new InvalidOperationException("Unreachable batch retry state.");
}

Memory remains bounded by one 500-row chunk and one short-lived context. The uncontended path uses an existence query and one save per batch. A named unique-key conflict causes a bounded fresh-context retry, so rows won by another importer are skipped and the remaining rows can still commit. Persistent contention or unrelated database errors surface instead of looping forever.

• Duplicate source ids occur inside one input batch.
• A source id already exists from an earlier run.
• Another importer inserts the same id after the existence query.
• Cancellation happens after several committed batches.

• Use a large generated input and observe bounded tracker counts.
• Repeat the same import and expect zero new records.
• Include duplicates within and across batches.
• Race two importers on overlapping ids in an isolated relational database and verify the union is stored once.
• Force repeated contention and verify retry is bounded and unrelated constraints are not swallowed.

• Using one DbContext for the entire file.
• Calling SaveChanges once per row.
• Treating an existence query as a replacement for uniqueness.
• Assuming partial commits are acceptable without progress and retry semantics.