Entity Framework doesn't update value which is modified by a trigger

Question

My table Sections (SQL Server) has ID as a primary key (int, identity) and SortIndex column (int) for sorting purposes.

The database has a trigger which sets SortIndex := ID at each INSERT. Obviously I want to change the sorting index later, by swapping the values for two rows.

I access the data using Entity Framework, all with MVC3 web application.

The problem is, Entity Framework doesn't update the value of SortIndex after I insert a new object into the table. It also caches all the data, so the following call to get all objects from this table will also give wrong SortIndex value for this object.

I tried changing StoreGeneratedPattern for this column in EDMX. This seems to be great and elegant but doesn't solve the problem.

If I set to Identity, it causes EF to properly update the value, but it becomes read only (exception thrown when trying to change). Setting it to Computed is similar, but instead of exception being thrown the values are just not written to the DB.

I can recreate the EF object every time if I need to use it after inserting an object, just by doing:

DatabaseEntities db = new DatabaseEntities()

But it seems like ugly workaround for me.

What's a solution to this problem?

Obviously something, what doesn't require me to do some action after every insert (and take a risk that it's forgotten and unnoticed) is preferred.

Answer 1

In short StoreGeneratedPattern means: the value is handled by the store and your application will never modify it. In such case you will get store generated value automatically after you call SaveChanges.

If you don't use StoreGeneratedPattern you will not get value and you will have to force another query execution to refresh your entity. You can for example do:

objectContext.Refresh(RefreshMode.StoreWins, yourSection);

Generally situations where you need to update values in both database through triggers and application don't play very nicely with EF (and probably also other ORM tools).

Answer 2

I found the answer from 'Ladislav Mrnka' being exact and marked it as accepted. Here are other workarounds, which I found while trying to find some solution. However, the solution I was looking for is in general not possible.

One of possibilities is to set StoreGeneratedPattern = Computed to let EF know, this value is calculated. And then, make a Stored Procedure to actually change the value of SortIndex. Typically it would change values in two rows (swap them), to change the sorting order. This procedure along with a trigger at INSERT gives guarantee the data stays consistent in the DB. It's not possible to create new row without proper value set in SortIndex, it's not possible to make two objects have the same value (unless stored procedure has a bug) and it's not possible to manually break the value somehow, because it's not possible to edit through EF. Looks like a great solution.

It's easily possible to have stored procedures mapped to functions in EF.

The problem is, it's now fine to enter a new row and EF properly updates data in its cache, but the cache is not updated after calling the stored procedure. Still some manual updated or refresh function is needed. Otherwise the following call to get objects sorted by SortIndex will give wrong results.

Other than that, it's possible to set MergeOption = MergeOption.OverwriteChanges for several entities, which causes EF to update data from the DB somewhat better. With this being done, it's possible to reread the object after inserting it or calling stored procedure and it will get refreshed. However, reading a collection of objects with db.Section.OrderBy(o => o.SortIndex) will still return cached results with wrong sorting order.

If anyone is interested, it's possible to make MergeOption default to something else by adding EF partial class and then partial method OnContextCreated, like here:

public partial class DatabaseEntities
{
    partial void OnContextCreated()
    {
        Subsection.MergeOption = MergeOption.OverwriteChanges;
        Section.MergeOption = MergeOption.OverwriteChanges;
        Function.MergeOption = MergeOption.OverwriteChanges;
    }
}