Entity Framework 6 won't save (update) an entity returned from Stored Procedure
Symptom
The issue was that code that previously worked, update property then save, wasn't working any more. The application is a .NET 4.6 Web API project using Entity Framework 6.
Issue
The fix isn't in the code which saved (updated) the entity, but was caused because the entity that is updated isn't correctly formed by the Stored Procedure. Before the SP was added, the entity was returned from EF directly.
Fix
After the stored procedure completes, refetch the entity using straight Entity Framework code such as `await TwitterAccounts.FirstAsync(table => table.TwitterHandle == twitterHandle);` where TwitterAccounts is a table in the EF Context.
Step to debug issue: capture save result
The original EF code to save didn't capture the returned int of number of rows changed.
Before
account.IsProperty = true;
_context.SaveChanges();
return OK();
After
account.IsProperty = true;int result= _context.SaveChanges();if(result==0){throw new Exception("property was not updated");}return Ok(account);
Step to debug issue: see T-SQL from Entity Framework in Visual Studio Output window
#if DEBUGDatabase.Log = s => System.Diagnostics.Debug.WriteLine(s);#endif
Step to debug issue: see the EF changes detected
account.IsProperty = false;DisplayTrackedEntities(context.ChangeTracker);int result = context.SaveChanges();if (result == 0){throw new Exception("disable-auto-tweets not updated successfully");}return Ok(account);
At this point, because no changes were detected, I knew the recent switch to a SP caused the issue that changes couldn't be detected on this artificial entity.
Step to debug issue: get EF entity after SP update
Originally the code checked a value and returned the account using something like:
await TwitterAccounts.FirstAsync(table => table.TwitterHandle == twitterHandle);
That method changed to use a stored procedure which returned the TwitterAccount entity type. That entity wasn't getting updated. Changing the method to fetch and return the EF entity after the SP updated fixed the issue - the account was updated.
Have a better fix?
There is probably some way to get EF to update my entity returned from the SP. Do you know what it is? Let me know on Twitter.