Continue inserting data after duplicate exception - SQL Server / Entity Framework-CodePudding

I have an issue, I want continue to insert data after the exception was raised by SQL Server.

I got an Unique Index on 3 different columns in table to detect duplicates.

For example I am trying to insert 2 rows, the first one is an duplicate, the second one is not.

When the duplicate is detected it goes in the catch, then I'm doing nothing, but when it comes on the second row which is not an duplicate, an exception is raised again for the previous row.

This is my code:

public async Task<IEnumerable<Result>> Handle(NewResultCommandDTO requests, CancellationToken cancellationToken) 
{
    var results = new List<Result>();

    ...

    for (var j = 0; j < resultDetails.Count(); j  )
    {
        var rd = resultDetails.ElementAt(j);

        var newResult1 = new Result
                             {
                                 AthleteFEIID = rd.AthleteFEIID,
                                 CompetitionCode = competition.CompetitionCode,
                                 HorseId = horse.Id,
                             };

        results.Add(newResult1);

        try
        {
            await _resultsService.AddResultAsync(newResult1);
            await _resultsService.CompleteAsync();
        }
        catch (Exception ex) 
        {
            var x = ex;
        }
    }
}

public async Task AddResultAsync(Result result)
{
    Context.Results.AddAsync(result);
}

public async Task CompleteAsync()
{
    await Context.SaveChangesAsync().ConfigureAwait(false);
}

Thank you for your help !

CodePudding user response：

await _resultsService.CompleteAsync(); is throwing the sql exception statement.

await _resultsService.AddResultAsync(newResult1); statement is already adding the entity in the db context. Even if the next statement throws the exception and it goes to catch block, the duplicated entity is still added in the context. So, when you are adding the next entity in the context and trying to save it, it is throwing exception because of the previous duplicated entity which is not removed from the context.

One solution is to remove the duplicated entity from the context when it goes to catch block.

try
{
    await _resultsService.AddResultAsync(newResult1);
    await _resultsService.CompleteAsync();
}
catch (Exception ex) {
    var x = ex;
    _resultsService.RemoveResult(newResult1);
}


public void RemoveResult(Result result)
{
    Context.Results.Remove(result);
}

Another solution is to check if the duplication already exists in the table before adding it. For that you will have to write a get method using the unique indexed columns.