Entity Framework 6 won't save (update) an entity returned from Stored Procedure


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. 


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.  


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. 


account.IsProperty = true;
return OK();


account.IsProperty = true;
int result= _context.SaveChanges();
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

Once the row count was returned as zero, the issue was why? To see the actual T-SQL sent to the database, I added the following code to the BotContext class, which inherits from DbContext. The constructor now includes:

Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

This shows all T-SQL from Entity Framework in the output window. This showed that EF wasn't producing any T-SQL which meant there was either an issue with the variables (those hadn't changed) or there were no changes detected in the entity. 

Step to debug issue: see the EF changes detected

To see the changes detected, I added the following code to my BotContext class:

        public void DisplayTrackedEntities(DbChangeTracker changeTracker)
            var entries = changeTracker.Entries();
            foreach (var entry in entries)
                System.Diagnostics.Debug.WriteLine("Entity Name: {0}", entry.Entity.GetType().FullName);
                System.Diagnostics.Debug.WriteLine("Status: {0}", entry.State);

Then to use the code, call this method after the entity is changed but before it is saved:

account.IsProperty = false;
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.



Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape