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();

        await using var db =
            await contextFactory.CreateDbContextAsync(cancellationToken);

        var sourceIds = batch
            .Select(row => row.SourceRowId)
            .Distinct()
            .ToArray();

        var existing = await db.ImportedRecords
            .Where(record => sourceIds.Contains(record.SourceRowId))
            .Select(record => record.SourceRowId)
            .ToHashSetAsync(cancellationToken);

        var newRecords = batch
            .Where(row => !existing.Contains(row.SourceRowId))
            .GroupBy(row => row.SourceRowId)
            .Select(group => ImportedRecord.From(group.First()))
            .ToList();

        db.ImportedRecords.AddRange(newRecords);
        await db.SaveChangesAsync(cancellationToken);
        imported += newRecords.Count;
    }

    return imported;
}

Memory is bounded by one 500-row chunk plus one context's tracked entities. Database work becomes roughly two commands per batch in the uncontended case. Concurrent import races still need the unique constraint and a deliberate duplicate-conflict policy.

• 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.
• Use an isolated relational database to test the unique constraint race.

• 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.