CODEWITHSUNDEEP
c#entity-frameworkentity-framework-core
cabird
cabird

Reputation: 541

Entity Framework inserting entity twice in two different contexts

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.

Upvotes: 2

Views: 2715

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205589

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); // <--
}

Upvotes: 3

Related Questions