Cannot insert duplicate key in object 'dbo.Account'. The duplicate key value is (2)

Question

I have the following structure and am using Entity Framework 6.2:

    public class MainModel
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long Id { get; set; }
        public ModelB ModelB { get; set; }
        public ModelC ModelC { get; set; }
    }

    public class ModelB
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long Id { get; set; }
        public Account Account { get; set; }
    }

    public class ModelC
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long Id { get; set; }
        public Account Account { get; set; }
    }

    public class Account
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public long Id { get; set; }
        public string Description { get; set; }
    }

I don't want SQL to assign PK automatically as these are entities that will already have a unique id (ex. there is only ever 1 account with Id 2, etc..)

When I try to run the following, I get a private key violation Cannot insert duplicate key in object dbo.Account. The duplicate key value is (2).

        var context = new MyContext();

        myOrders.ToList().ForEach(p =>
        {
             context.MyOrders.AddOrUpdate(p);

        });
        context.SaveChanges();

I've tried AsNoTracking() and setting the EntityState to Modified if it's found in the table already.

This is the JSON data that is populating myOrders. myOrders is a List

[   {
    "MainModel": {
        "Id": 100,
        "ModelB": {
            "Id": 1,
            "Account": {
                "Id": 2,
                "Description":"This is a test account"
            }
        },
        "ModelC": {
            "Id": 1,
            "Account": {
                "Id": 2,
                "Description":"This is a test account"
            }
        }
    }
},
{
    "MainModel": {
        "Id": 200,
        "ModelB": {
            "Id": 5,
            "Account": {
                "Id": 2,
                "Description":"This is a test account"
            },
        }
        "ModelC": {
            "Id": 6,
            "Account": {
                "Id": 2,
                "Description":"This is a test account"
            }
        }
    }
}
}
]

Any thoughts?

Possible duplicate[Entering Keys manually with Entity Framework](https://stackoverflow.com/questions/18907411/entering-keys-manually-with-entity-framework) — DCCoder, Dec 13 '18 at 15:27
@DCCoder this just explains how to stop SQL from auto assigning PK. I'm already doing this. — HeonAle, Dec 13 '18 at 15:31
@DevilSuichiro what do you mean by navigation properties? I'm able to insert my seed data (EF automatically created the tables, PK and FK) but when I try to insert an object that has more than one reference to "account", this is when I get this error — HeonAle, Dec 13 '18 at 15:32
Please provide an [mcve], we can't guess as to the content of `myOrders` or even what type that is. Likely you are providing a double instance in a relation somewhere with the same key but who knows. — Igor, Dec 13 '18 at 15:33
Well, the error that you are getting is pretty straightforward. Double check your database. It seems the value that you are trying to insert already exists in a field that is marked as Unique. — DCCoder, Dec 13 '18 at 15:33
Maybe I'm not asking the correct question. Say, for instance ModelB and ModelC both reference the same Account with Id 2, I thought when I used AddOrUpdate, EF wouldn't try to insert that row if it found the PK? It seems this is what is happening. Do I need to account for that manually? — HeonAle, Dec 13 '18 at 15:36
@Igor I added the JSON object that creates myOrders which is a List — HeonAle, Dec 13 '18 at 16:07
How are the primary keys mapped in your c# code? Could be that because the json would serialize to multiple Account instances (not the same instance) that the equality check does not see them as the same because you did not map the primary key. — Igor, Dec 13 '18 at 16:09
@Igor that is entirely plausible. I'm new to EF. Currently, I do not have any keys mapped in code aside from decorating my Id columns with [DatabaseGenerated(DatabaseGeneratedOption.None)] — HeonAle, Dec 13 '18 at 16:12

score 0 · Answer 1 · answered Dec 13 '18 at 21:52

Firstly, if this is application code rather than migration/test setup you should avoid the use of AddOrUpdate. It is not intended to be used for general insert or update scenarios. As a general rule I don't advise passing entities around at all. Entities represent your domain, not your model. Model classes (or ViewModels) should be simple, serializable POCO classes with just the information that a view needs to consume. Passing entities around leads to complexity around re-attaching, performance issues with potential lazy loads and the simple fact you're often querying and passing more data than necessary, and security issues in that more information is passed, and in your case trusted, meaning it is relatively easy to corrupt your data source. I.e. modify an entity to change a FK reference or other data via debugging tools that you may not intend for the client to change. Your code trusts the entity coming back and simply looks to attach it and commit the change as an update. I could change anything in the object graph regardless of what you show in the UI.

The issue you are likely facing is that EF is attempting to resolve the related entities and since they aren't associated with that DBContext it is treating them as new records. You may use AddOrUpdate on the top level, but I don't believe that applies through to related entities. It doesn't automatically with Attach, so I wouldn't be surprised if it doesn't work magic with AddOrUpdate. If you need to re-attach entity graphs to a context then it can be rather cumbersome to dive through the graph and handle updates, inserts, and/or deletes. You can use a library like GraphDiff (https://github.com/zzzprojects/GraphDiff) to assist with this.

Steve, thanks for the response. I mislabeled them for the example but these are my entities. The data isn't coming from a UI but is being deserialized from JSON. I have a one to many relationship (a few of them) and I thought this was the proper way to have EF handle the updates and inserts into SQL. Is this not the case? Any examples of how to handle this properly with EF? — HeonAle, Dec 14 '18 at 15:51
Have you had a look at GraphDiff? If you trust the data in the JSON then this can help manage the updates and inserts, Normally when working with bulk updates I prefer to use a bounded context where entities only expose FKs for reference data instead of references, and the only references to be updated or created are for children. In this sense the Model entity would just have an AccountID property to update. Synchronizing data is a messy business regardless of the underlying technology. — Steve Py, Dec 16 '18 at 21:52

Cannot insert duplicate key in object 'dbo.Account'. The duplicate key value is (2)

1 Answers1