Entity Framework Core 3.1.6 Item With Same Key Has Already Been Added

Question

I have a MVC website in .NET Core 3.1 using Entity Framework 3.1.6. My issue is that one of my entities now throws and error:

System.ArgumentException: An item with the same key has already been added. Key: Assessor

It was working fine up until recently and I'm not sure what changed.

Model

public class FacilityQCResult
{
    [Key]
    public int ID { get; set; }
    public Guid? QCID { get; set; }
    public string QCSeverity { get; set; }
    public string QCType { get; set; }
    public string QCShortName { get; set; }
    public string Resolution { get; set; }
    public string Base { get; set; }
    public string FacilityNumber { get; set; }
    public string FacilityName { get; set; }
    public DateTime? DataEntryComplete { get; set; }
    public string TeamAssignment { get; set; }
    public string Assessor { get; set; }
    public string TripWeek { get; set; }
    public string Details { get; set; }
    public Guid? FacilityID { get; set; }
}

Context

public partial class SQLDBContext : DbContext
{
    public SQLDBContext()
    {
    }

    public SQLDBContext(DbContextOptions<SQLDBContext> options)
        : base(options)
    {
    }
    
    
    public virtual DbSet<FacilityQCResult> FacilityQCResults { get; set; }
    
 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<FacilityQCResult>(entity =>
        {
            entity.ToTable("FacilityQCResult");
            entity.HasKey(e => e.ID);

            entity.Property(e => e.DataEntryComplete)
                .HasColumnName("Data_Entry_Complete")
                .HasColumnType("datetime");

            entity.Property(e => e.TeamAssignment)
                .HasColumnName("Team_Assignment")
                .HasMaxLength(100)
                .IsUnicode(false);

            entity.Property(e => e.TripWeek)
                .HasColumnName("Trip_Week")
                .HasMaxLength(10)
                .IsUnicode(false);
        });
    }
}

Table Structure

It throws an error when it gets to the following line:

var result = await context.FacilityQCResults.FromSqlRaw(" exec [dbo].[pr_ExecuteFacilityQCRules]").ToListAsync();

The stored procedure does some other stuff and spits out rows of data that models the table structure. Funny thing is I have another object that is set up similar that runs with no issues. This one seemed to work until recently, but I'm not sure exactly when this starting happening or why as I have not changed the object or table structure. Basically, EF Core thinks the Assessor column is a key despite me not specifying so. How is this happening and how do I tell it that it is not a key?

Answer 1

That isn't likely a SQL Server related exception (i.e. I get the impression that you're thinking the exception is being thrown due to a SQL duplicate key exception, akin to what you would see if you tried to create multiple rows with a duplicate PK and/or unique key/index key). Were that the case, you'd be seeing a different exception (likely a SqlException or related).

That is most likely a C# exception bubbling up from somewhere in EF or related, and if I had to take a wild guess, my first hunch would be that you are possibly getting the "Assessor" column returned multiple times from the result set returned by the pr_ExecuteFacilityQCRules stored procedure. That's just a bit of a guess however - a simple test would be to execute the procedure in a SQL client and see the columns returned. You'd typically see this type of exception in C# by trying to add multiple duplicate key names to a dictionary or similar (i.e. myDictionary.Add(key)).

If that is not the case, I think there'd need to be a bit more info added to help diagnose beyond making a guess.