CODEWITHSUNDEEP
c#entity-framework
Fernando Silva
Fernando Silva

Reputation: 364

AddOrUpdate leads to incorrect foreign key update

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

Upvotes: 1

Views: 752

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109079

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.

Upvotes: 3

Related Questions