An item with the same key has already been added. Key: Id when trying to insert using stored procedure in .NET

Question

I am trying to implement a method that executes a stored procedure in a foreach loop using and I keep getting an error that says

An item with the same key has already been added. Key: StudentId

Controller:

[HttpPost("{id}/link-to-subjects")]
public async Task<ActionResult> LinkStudentToSubjects(int id, int[] subjectIds)
{
    try
    {
        await _studentRepository.LinkStudentToSubjectsAsync(id, subjectIds);
    
        return NoContent();
    }
    catch (Exception ex)
    {
        return BadRequest(ex.Message);
    }
}

Repository method that calls the stored procedure:

public async Task<bool> LinkStudentToSubjectsAsync(int studentId, int[] subjectIds)
{
    DeleteSubjectStudentLinks(studentId);

    var parameters = new Dictionary<string, object>();

    foreach (var subjectId in subjectIds)
    {
        parameters.Add(StoredProcedures.Params.StudentId, studentId);
        parameters.Add(StoredProcedures.Params.SubjectId, subjectId);
               
        linkResult = await _repository.ExecuteStoredProcAsync<StudentSubject>(StoredProcedures.LinkStudentSubjects, parameters);
    }

    if (linkResult is not null)
    {
        return true;
    }

    return false;
}

Stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LinkStudentSubjects] 
    @StudentSubjectID int,
    @StudentID int,
    @SubjectID int
AS
    DECLARE @ID int
    DECLARE @TableTestID TABLE (ID int)
BEGIN
    DECLARE @TableStudentSubjectID TABLE (ID int)

    -- IF NOT EXISTS (SELECT StudentID , SubjectID FROM [dbo].[StudentSubjects]
    --                WHERE StudentID = @StudentID)
    BEGIN
        INSERT INTO [dbo].[StudentSubjects] (StudentId, SubjectId)
        OUTPUT inserted.id INTO @TableStudentSubjectID(ID)
        VALUES (@StudentID, @SubjectID)

        SELECT ID FROM @TableStudentSubjectID
    END
END

Answer 1

Change your code from

public async Task<bool> LinkStudentToSubjectsAsync(int studentId, int[] subjectIds)
{
    DeleteSubjectStudentLinks(studentId);
    var parameters = new Dictionary<string, object>();
    foreach (var subjectId in subjectIds)
    {
        parameters.Add(StoredProcedures.Params.StudentId, studentId);
        parameters.Add(StoredProcedures.Params.SubjectId, subjectId);
           
        linkResult = await _repository.ExecuteStoredProcAsync<StudentSubject>(StoredProcedures.LinkStudentSubjects, parameters);
    }
    if (linkResult is not null)
    {
        return true;
    }
    return false;
}

to

public async Task<bool> LinkStudentToSubjectsAsync(int studentId, int[] subjectIds)
{
    DeleteSubjectStudentLinks(studentId);
    var parameters = new Dictionary<string, object>();
    foreach (var subjectId in subjectIds)
    {
        parameters.Clear();
        parameters.Add(StoredProcedures.Params.StudentId, studentId);
        parameters.Add(StoredProcedures.Params.SubjectId, subjectId);

        linkResult = await _repository.ExecuteStoredProcAsync<StudentSubject>(StoredProcedures.LinkStudentSubjects, parameters);
    }

    if (linkResult is not null)
    {
        return true;
    }
    return false;
}

Reason

The new Dictionary<string, object>(); you defined is outside the foreach loop, so after the first execution, the previous studentId will exist in the dictionary. The solution to this problem is to clear the previous record before executing the stored procedure or define new Dictionary in the loop.