Entity Framework does not update a trigger-modified value.

asp.net-mvc c# entity-framework sql-server

Question

my deskSections The (SQL Server)ID as the main essential(int, identity) and SortIndex column (int) to be used for sorting.

A trigger in the database setsSortIndex := ID at everyINSERT . Naturally, I want to switch the values for two rows later to modify the sorting index.

Entity Framework, together with an MVC3 web application, is how I access the data.

The issue is that Entity Framework fails to alter the value ofSortIndex after the addition of a new item to the table. The subsequent request to obtain all items from this table will provide incorrect results since it also caches all the data.SortIndex for this object, value.

I made some changesStoreGeneratedPattern in this columnEDMX . Although it appears wonderful and beautiful, this doesn't resolve the issue.

If I decide toIdentity , it causes EF to update the value correctly, but it becomes read-only (exception thrown when trying to change). Changing it toComputed is similar, but values are simply not sent to the database rather than an exception being raised.

After entering an item, I can always rebuild the EF object if I need to utilize it by performing:

DatabaseEntities db = new DatabaseEntities()

But to me, that looks like a crude workaround.

What is the answer to this issue?

Something that obviously doesn't need me to take action after every thought isinsert (and run the danger of it being overlooked and forgotten) is preferable.

1
11
3/8/2012 10:52:51 AM

Accepted Answer

In essenceStoreGeneratedPattern indicates that your application will never change the value, which is managed by the store. If so, you will immediately get the value created by the store after calling.SaveChanges .

If you don't useStoreGeneratedPattern You won't get any benefit, and you'll have to force the execution of another query in order to update your object. You might, for instance, do:

objectContext.Refresh(RefreshMode.StoreWins, yourSection);

In general, EF doesn't work well in scenarios when you need to change variables in the database and the application simultaneously (and probably also other ORM tools).

15
3/8/2012 10:55:41 AM

Popular Answer

I accepted the response from "Ladislav Mrnka" because I thought it was accurate. Here are some other workarounds that I discovered while looking for a solution. The answer I was seeking for, though, is often impossible.

Among the options is to setStoreGeneratedPattern = Computed This value is computed to inform EF. Next, create a stored procedure to modify the value ofSortIndex . Normally, to modify the sorting order, values in two rows would be changed (swapped). Using this process and a trigger atINSERT offers assurance that the data in the DB is consistent. Without the appropriate value placed in, it is impossible to create a new row.SortIndex Due to the inability to modify using EF, it is impossible to manually break the value or make two objects have the same value (unless a defect in the stored process makes this feasible). Looks like a very good answer.

The mapping of stored procedures to functions in EF is simple.

The issue is that although it is now acceptable to insert a new row, EF does not correctly update the data in its cache after running the stored method. However, some manual updating or refreshing is still required. Alternatively, the next call to receive items ordered bySortIndex will provide false results.

Other than that, you may also setMergeOption = MergeOption.OverwriteChanges This improves how well EF updates data from the DB for a number of entities. By doing this, the object may be refreshed when read again after being inserted or called from a saved method. However, perusing a group of items havingdb.Section.OrderBy(o => o.SortIndex) still return cached results that are sorted incorrectly.

If anybody is interested, they may createMergeOption EF partial class and then partial method are added to change the default to something else.OnContextCreated like this:

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow