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!