AddOrUpdate leads to incorrect foreign key update

Question

I am having the same error as the one reported at Stack Overflow: EntityFramework's AddOrUpdate leads to incorrect foreign key update

Keeping it short, after I call AddOrUpdate to insert a new record, if I then call AddOrUpdate to update the same record, it throws an exception.

Example:

State.cs
int Id;
string Name;

City.cs
int Id;
int StateId;
string Name;
int Location;

// Crud
var state = new State { Name = "NY" };
Context.States.AddOrUpdate(p => p.Name, state);
Context.SaveChanges();

// Adds with location equals to 1
var city = new City { Name = "NYC", Location = "1", State = state };
Context.Cities.AddOrUpdate(p => p.Name, city);
Context.SaveChanges();

// Updating the location to 2, leads to EF trying to set StateId to 0
var city = new City { Name = "NYC", Location = "2", State = state };
Context.Cities.AddOrUpdate(p => p.Name, city);
Context.SaveChanges();

Exception is thrown, saying Foreign Key StateId = 0

Stack trace:

"The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_dbo.City_dbo.State_StateId". The conflict occurred in database "C:\X\APP_DATA\LOCAL.MDF", table "dbo.State", column 'Id'.
The statement has been terminated."

System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<NonQuery>b__0(DbCommand t, DbCommandInterceptionContext`1 c)
System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery()
System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()

THANKS

Did you try using `StateId = state.StateId` instead of `State = state` there? — JohnnyHK, Aug 29 '15 at 15:47
I did and it worked: var city = new City { Name = "NYC", Location = "2", State = state, stateId = State.stateId }; But, to me, that sounds like a workaround. I opened a bug with the entity framework's team, but if you guys can confirm this is a bug, would help a lot. — Fernando Silva, Aug 29 '15 at 15:54
This is not a bug as the correct property to set is stateId. The State property is a navigation property and stateId is your field in the City table which references the State table. — Ariel Moraes, Aug 29 '15 at 16:04
Not sure how you are using it, but remember AddOrUpdate is primarily for seeding so I only use it for lookup type tables. Anything needing a relation I go the normal EF route and maybe wrap it in something like "if (!db.Cities.Any(c => c.Name == "New York") ... See http://thedatafarm.com/data-access/take-care-with-ef-4-3-addorupdate-method/ — Steve Greene, Aug 29 '15 at 16:10
I guess you are using the `AddOrUpdate` method for seeding data during migrations (check my [answer](http://stackoverflow.com/questions/31272455/ef-is-it-ok-to-use-dbsett-addorupdate-outside-of-migrations/31273044#31273044) in case you are using it in another context ). Another thing is why are you calling `SaveChanges` method multiple times? You should call `SaveChanges` only one time at the end of your code to make just one transaction. — ocuenca, Aug 29 '15 at 16:11
I am using for non seeding operations. Do you think this would be a problem? Also, the same problem is reported in the comments in the link you provided: "The only problem i’ve found with AddOrUpdate, is that it doesn’t update Navigation Properties Lets say you have a Category and a Post object (and you didn’t create the Foreign Key yourself) class Post { public Category Cat… } if you then want to change category for the Post, you can’t use AddOrUpdate !!" — Fernando Silva, Aug 29 '15 at 16:14
On the multiple SaveChanges(), that was only an example. I am using the Unit of work pattern. Thanks for pointing it out. — Fernando Silva, Aug 29 '15 at 16:16

Gert Arnold · Accepted Answer · 2015-08-29T22:42:17.050

AddOrUpdate is intended for simple seeding operations only. It's know to be buggy in some scenarios and this should go down as a bug too. It's unexpected behaviour.

It probably has to do with how property values are copied from the submitted instance (your city variable) to the instance fetched from the database. Clearly, only primitive properties are taken into account.

After your statement...

var city = new City { Name = "NYC", Location = "2", State = state };

...city will have StateId = 0. It's this value that is assigned to the database instance (which is a different instance than city).

Once you're aware of it, you can fix this by setting StateId in stead of State.

But I think you shouldn't use AddOrUpdate in regular business logic. For one, because it's buggy (or incomplete at best) and I don't know what else is in store. But also because it's a pretty heavy method. It fetches a complete entity from the database, with tracking and all.

But normally, if you want to update an entity, it's very likely that you already fetched it before, then got it updated in some process and now want to submit it again. Even in N-tier applications (with serialization/deserialization etc.) you probably still have the object's Id by which you can determine whether it should be inserted or updated. So in most cases you can do without re-fetching the entity from the database, while AddOrUpdate will always do that if it exists.

AddOrUpdate leads to incorrect foreign key update

1 Answers1