Saving single objects with Entity Framework code first

Question

I am using Entity Framework 4.3.1 in a project, using code first and the DbContext API. My app is an n-tier app where disconnected objects may come in from a client. I am using SQL Server 2008 R2 but will be moving to SQL Azure soon. I am running into an issue I just can't seem to solve.

Imagine I have a few classes:

class A {
    // Random stuff here
}
class B {
    // Random stuff here
    public A MyA { get; set; }
}
class C {
    // Random stuff here
    public A MyA { get; set; }
}

By default, EF operates on object graphs. For instance, if I have an instance of B that encapsulates an instance of A and I call myDbSet.Add(myB);, it will also mark the instance of A as being added (assuming it is not yet being tracked).

I have a scenario in my app where I need to be explicit about which objects get persisted to the database, rather than have it track entire object graphs. The order of operations is as follows:

A myA = new A(); // Represents something already in DB that doesn't need to be udpated.
C myC = new C() { // Represents something already in DB that DOES need to be updated.
    A = myA;
}
B myB0 = new B() { // Not yet in DB.
    A = myA;
}
B myB1 = new B() { // Not yet in DB.
    A = myA;
}

myDbSetC.Attach(myC);
context.Entry(myC).State = Modified;

myDbSetB.Add(myB0); // Tries to track myA with a state of Added
myDbSetB.Add(myB1);

context.SaveChanges();

At this point I get an error saying AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges. I believe this happens because calling add on myB0 marks the instance of A as being Added, which conflicts with the instance of A already being tracked.

Ideally I could do something like call myDbSet.AddOnly(myB), but obviously we don't have that option.

I have tried several workarounds:

Attempt #1: First, I tried creating a helper method to prevent myA from being added a second time.

private void MarkGraphAsUnchanged<TEntity>(TEntity entity) where TEntity : class {
        DbEntityEntry entryForThis = this.context.Entry<TEntity>(entity);
        IEnumerable<DbEntityEntry> entriesItWantsToChange = this.context.ChangeTracker.Entries().Distinct();

        foreach (DbEntityEntry entry in entriesItWantsToChange) {
            if (!entryForThis.Equals(entry)) {
                entry.State = System.Data.EntityState.Unchanged;
            }
        }
    }

...

myDbSetB.Add(myB0);
MarkGraphAsUnchanged(myB0);

While this solves the problem of it trying to add myA, it still causes key violations within the ObjectStateManager.

Attempt #2: I tried doing the same as above, but setting the state to Detached instead of Unchanged. This works for saving, but it insists on setting myB0.A = null, which has other adverse effects in my code.

Attempt #3: I used a TransactionScope around my the entire DbContext. However, even when calling SaveChanges() between each Attach() and Add(), the change tracker does not flush its tracked entries so I have the same problem as in attempt #1.

Attempt #4: I continued with the TransactionScope, except I used a repository/DAO pattern and internally create a new DbContext and call SaveChanges() for each distinct operation I do. In this case, I got an error 'Store update, insert, or delete statement affected an unexpected number of rows.' When using the SQL Profiler, I find that when calling SaveChanges() on the second operation I did (the first Add()), it actually sends the UPDATE SQL to the database from the first operation a second time -- but doesn't change any rows. This feels like a bug in Entity Framework to me.

Attempt #5: Instead of using the TransactionScope, I decided to use use a DbTransaction only. I still create multiple contexts but pass a pre-built EntityConnection to each new context as it's created (by caching and manually opening the EntityConnection built by the first context). However, when I do this, the second context runs an initializer I have defined, even though it would have already run when the app first started up. In a dev environment I have this seeding some test data, and it actually times out wating for a database lock on a table my first Attach() modified (but is still locked due to the transaction still being open).

Help!! I've tried about everything I can think of, and short of completely refactoring my app to not use navigation properties or using manually constructed DAOs to do INSERT, UPDATE, and DELETE statements, I'm at a loss. It seems there must be a way to get the benefits of Entity Framework for O/R mapping but still manually controlling operations within a transaction!

Answer 1

There must be something else you are not showing because there is no problem with the way how you attach and add entities. The following code will attach myA, myC, myB0 and myB1 to context as unchanged and set state of myC to modified.

myDbSetC.Attach(myC);
context.Entry(myC).State = Modified;

the following code will correctly detect that all entities are already attached and instead of throwing exception (as it would do in ObjectContext API) or inserting all entities again (as you expect) it would just change myB0 and myB1 to added state:

myDbSetB.Add(myB0);
myDbSetB.Add(myB1);

If your myA and myC are correctly initialized with keys of existing entities whole code will correctly execute and save except the single problem:

C myC = new C() { 
    A = myA;
}

This looks like independent association and independent association has its own state but API to set its state is not available in DbContext API. If this is a new relation you want to save it will not be saved because it is still tracked as unchanged. You must either use foreign key association or you must convert your context to ObjectContext:

ObjectContext objectContext = ((IObjectContextAdapter)dbContext).ObjectContext;

and use ObjectStateManager to change state of the relation.