Entity Framework inserting entity twice in two different contexts

Question

I'm using Entity Framework Core to store an object graph in a database. At various times while I'm building the graph, I create an entity, store it to the database, and the release the context. However, I'm running into an issue where EFC is trying to insert an entity that has already been inserted when it is connected to a new entity. This is best explained with code. Here's a short repro piece of code (this is straight line code, but the two uses of contexts happen at different times and places in the code).

In the second call to context.SaveChanges(), I get the following exception:

SqlException:

Cannot insert explicit value for identity column in table 'Namespace' when IDENTITY_INSERT is set to OFF.

When I look at the SQL being executed, it is trying to insert the namespace entity again, presumably because myType is being saved to the DB and it has a reference to the dbNamespace entity.

// see if namespace is in the db and add it if not
string someNamespaceString = "foobar";
CodeDatabase.Models.Namespace dbNamespace;
using (var context = new CodeFactsContext())
{
    dbNamespace = context.Namespace.FirstOrDefault(ns => ns.Namespace1 == someNamespaceString);
    if (dbNamespace == null)
    {
        dbNamespace = new Namespace() { Namespace1 = someNamespaceString };
        context.Namespace.Add(dbNamespace);
    }
    context.SaveChanges();
}

// Type entity created somewhere from the code
var myType = new CodeDatabase.Models.Type()
{
    FullName = "foobar.mytype",
    ShortName = "mytype",
    Namespace = dbNamespace // this is already in the DB
};

// check if myType is in the db and add it if not
using (var context = new CodeFactsContext())
{
    var dbType = context.Type.FirstOrDefault(t => t.FullName == myType.FullName);
    if (dbType == null)
    {
        dbType = myType;
        context.Add(dbType);
    }
    context.SaveChanges(); // throws exception
}

Any idea how to get EF Core to recognize (in the second context.SaveChanges()) that myType should be inserted into the database, but myType.Namespace should not because it's already there? Both of the entities have an int id that is autogenerated by the DB and the id of Namespace is set to the database value after the first call to SaveChanges. I thought EF Core would recognize that the id is not 0 and not try to save it. Any help/suggestions very welcomed.

This just means your Namespace table has an identity column. Something like `PRIMARY KEY IDENTITY (1, 1)` and your save is trying to write to that column which SQL server does not like. — Steve, Dec 12 '18 at 18:33
I understand that's what it means. I'm asking how to get EFC to understand that it shouldn't write it again since the entity was already added to the DB. — cabird, Dec 12 '18 at 18:36
nope. I think you are still misunderstanding what it means. It means you should NEVER write to that column (unless you are trying to copy data with identity), not that you shouldnt write to it again. — Steve, Dec 12 '18 at 18:38
OK. I think I am misunderstanding. I'm not explicitly trying to write to that column. I'm creating the Namespace entity and in the first call to context.SaveChanges(), it successfully saves the Namespace entity. Then the second SaveChanges() (which is trying to write the Type entity) is where the failure occurs. I'm not trying to write that column and I'm not sure what I'm doing that makes EFC try to write to that column. — cabird, Dec 12 '18 at 18:43
I think you should take a look at https://stackoverflow.com/questions/25441027/how-do-i-stop-entity-framework-from-trying-to-save-insert-child-objects. — obl, Dec 12 '18 at 18:44

score 3 · Answer 1 · answered Dec 12 '18 at 20:48

I thought EFC would recognize that the id is not 0 and not try to save it.

The problem is that you are using Add method which marks all reachable and not tracked entities as new, regardless of the key value (this is to allow the identity insert scenarios). This is explained in the Disconnected Entities - Working with graphs - All new/all existing entities. While your screnario falls into Mix of new and existing entities.

Any idea how to get EFC to recognize (in the second context.SaveChanges) that myType should be inserted into the database, but myType.Namespace should not because it's already there? Both of the entities have an int id that is autogenerated by the DB and the id of Namespace is set to the database value after the first call to SaveChanges.

Actually there is a simple solution explained in the second documentation link:

With auto-generated keys, Update can again be used for both inserts and updates, even if the graph contains a mix of entities that require inserting and those that require updating

where "again" refers to Saving single entities:

The Update method normally marks the entity for update, not insert. However, if the entity has a auto-generated key, and no key value has been set, then the entity is instead automatically marked for insert.

Luckily your entities use auto-generated keys, so simply use Update instead of Add:

if (dbType == null)
{
    dbType = myType;
    context.Update(dbType); // <--
}

Entity Framework inserting entity twice in two different contexts

1 Answers1