Cannot insert duplicate key row in object

Question

The first time I add a migration my seeding works. However, if I run it again I get the exception

System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object 'dbo.Attributes' with unique index 'IX_AttributeName'. The duplicate key value is (State).

From my understanding, AddOrUpdate should not add if the entity already exists. Am I misunderstanding?

Attribute Entity

 public class Attribute
    {
        public Attribute()
        {
            IsList = false;
        }

        public int AttributeId { get; set; }

        [Required]
        [StringLength(255)]
        [Index("IX_AttributeName",1,IsUnique = true)]
        public string AttributeName { get; set; }
        public bool IsPHI { get; set; }
        public bool IsList { get; set; }
        public virtual ICollection<AttributeTerm> AttributeListTerms { get; set; }

    }

Portion of Seeding class (updated after Gusman's advice)

 public Configuration()
        {
            AutomaticMigrationsEnabled = false;
        }

        protected override void Seed(GBARDbContext context)
        {

            var attributes = new[]
            {
            new Attribute {AttributeId = 2, AttributeName = "First Name"},
            new Attribute {AttributeId = 3, AttributeName = "Last Name"},
            new Attribute {AttributeId = 4, AttributeName = "Middle Name"},
            new Attribute {AttributeId = 5, AttributeName = "Street"},
            new Attribute {AttributeId = 1, AttributeName = "State", IsList = true},
            };

           context.Attributes.AddOrUpdate(a => a.AttributeId, attributes);
           context.SaveChanges();

Could be a race condition if `Seed` is called from different threads at the same time. — Tim Schmelter, Jul 13 '17 at 14:53
If I recall it right the update will only update a record according to it's primary key, not by secondary indexes. — Gusman, Jul 13 '17 at 14:55
@Gusman I tried that at first and when that didn't work I read this https://stackoverflow.com/a/15413951/5004521, and realized it shouldn't be an identity column as it is auto-generated/incremented — Eitan K, Jul 13 '17 at 15:00
before run this code again please check, probably you already have the same data on the database, and now you are trying insert the same data. As @Gusman mentioned, this code will never try to update, it will always try to insert data — Alex S, Jul 13 '17 at 15:24
I do have the same data in the database, but I thought AddOrUpdate will avoid duplicates. I changed it to be with the primary key but still does now work — Eitan K, Jul 13 '17 at 16:27

score 1 · Answer 1 · answered Jul 13 '17 at 16:43

I fixed this by listening to Gusman and Alex S's advice of using the primary key to seed with. I had to delete all data and reset the identity counter first for the Attribute table and its depending AttributeTerm table

DBCC CHECKIDENT ('Attributes', RESEED, 0)
DBCC CHECKIDENT ('AttributeTerms', RESEED, 0)

score 0 · Answer 2 · answered Jul 13 '17 at 16:35

You have two columns that are unique right? The AttributeId and the AttributeName, so in the AddOrUpdate you should set this. Here's the solution:

context.Attributes.AddOrUpdate(a => new { a.AttributeId , a.AttributeName} , attributes);
context.SaveChanges();

Cannot insert duplicate key row in object

2 Answers2