MergeOptions in Entity Framework

Understanding MergeOption when using Entity Framework is important, especially when you do multiple operations of the same object within a context.

If we have codes like these ones and assume the MergeOption is applied to all operations:

// first query
var listOne = Context.Students.OrderBy(s => s.Id).Take(2).ToList();
var studentA = listOne.First();

// first update
studentA.FirstName = "Updated";


// second query
var listTwo = Context.Students.OrderBy(s => s.Id).Take(5).ToList();
var studentB = listTwo.First();

// second update
studentB.FirstName = "UpdatedB";

The four options are:
- NoTracking
Objects are maintained in a Detached state and are not tracked in the ObjectStateManager. As the object is not tracked, object cannot be updated. On the second query, EF will get fresh records from the data source, including the ones that have been retrieved by query one then populate listTwo.

- AppendOnly (default)
Objects that do not exist in the context are attached to the context. Object that is already tracked in the context, will not be overwritten when EF retrieves records again from data source. On the second query, only three other student objects are retrieved from data source.
If there's change in values in the data source between first and second queries, all tracked objects in the context will not have the latest values from data source after retrieving on the second query.

- PreserveChanges
Similar like AppendOnly, but will replace unmodified objects (objects with EntityState Unchanged) from data source. On the second query, the other student object from first query and three new ones will be retrieved from data source.
If there's change in values in the data source between first and second queries, modified objects in the context will not have the latest values from data source after retrieving on the second query. The unmodified objects will be refreshed and have latest values from data source.

- OverwriteChanges
Objects will be replaced with fresh records from data source. If a change is made and not committed to data source yet, when retrieving from data source again the change will be lost and replaced with the retrieved value from data source. In our example, the first update is lost when second query is made. All objects will then reset to Unchanged state.
If there's change in values in the data source between first and second queries, all objects in the context will have the latest values from data source after retrieving on the second query.


References:
https://docs.microsoft.com/en-us/dotnet/api/system.data.objects.mergeoption?view=netframework-4.8
https://community.dynamics.com/crm/b/develop1/posts/do-you-understand-mergeoptions