Saving records in Entity Framework using a loop not moving to the next record after a failure

Question

I have a list of records that I'm trying to insert into a SQL database using Entity Framework Core.

• Record 1 is Valid
• Record 2 is Not valid (let's say it's a duplicate key violation but it could also be bad data)
• Record 3 is Valid
• Record x is Valid

I'm trying to save the record using a loop. My crud service just adds the record to the DbSet and calls SaveChangesAsync() (yes, I know I should call SaveChanges once or use AddRange, but it's split like this at the moment).

foreach (var record in records)
{
    var result = await _crud.CreateAsync(record);

    if (result.HasError)
        // log error
}

Now, the first record is valid and saves fine.

The 2nd record then causes an error, fine as it's logged.

But, here is the problem. Every record after that fails even though they are valid. The error message received (and data) is the same as record 2. It's as if that record doesn't get removed or cleared from the context.

The crud.CreateAsync method is doing the following:

try
{
    await _entity.AddAsync(record);
    await _context.SaveChangesAsync();

    return Result.Success()
}
catch (Exception ex)
{
    return Result.Fail(ex);
}

What is wrong?

Answer 1

Depending on what this "_crud" reference is, the issue you are facing is called a poisoned DbContext. You have told EF to add (or update) an entity that is not valid. That entity will remain in the change tracker until you tell the DbContext to ignore it. (Detaching it)

So if your _crud.CreateAsync(record) looks something like:

try
{  
    _context.Records.Add(record);
    await _context.SaveChangesAsync();
    return new SuccessResult(record);
}
catch (Exception ex)
{
    return new FailureResult(ex);
}

You need to remove any invalid entity or entities from the DbContext that are poisoning if you want to continue using that DbContext instance. Otherwise your unit of work (lifetime of the DbContext) needs to be shortened to processing each row so that a fresh DbContext instance is used for each insert. That would typically mean passing in a DbContextFactory reference rather than a DbContext instance.

So in the above example to try and de-poison the DbContext:

catch (Exception ex)
{
    context.Entry(record).State = EntityState.Detached;
    return new FailureResult(ex);
}

Note that if record includes any child entities that were created/added, those need to be detached as well. Any references to existing entities should not be detached.