ADO.NET Entity Framework: unexpected behaviour with MergeOptions

I’ve started to look closer at the ADO.NET Entity Framework that ships with .NET 3.5 SP1. There are some interesting blogs from the ADO.NET Product Team that you should read. I started with a sample database that contains a simple Address table. I created an Entity Model that automatically created my ObjectContext, my Entity Classes and the accessor method for an ObjectQuery to my Addresses.

Accessing Entity Objects

The following code snippet iterates through all my address objects twice. Twice because I wanted to find out if objects are actually cached by the ObjectStateManager and how database updates are handled:

Database1Entities1 myReadDatabase = new Database1Entities1();
Trace.WriteLine("Iterate through all addresses");
foreach (Address a in myReadDatabase.Address)
Trace.WriteLine("Iterate through all addresses a 2nd time");
foreach (Address a in myReadDatabase.Address)

I had 5 entries in my Address table and therefore the above code resulted in the following PurePath:

Two iterations showing that only the first access actually creates new Address objects
Two iterations showing that only the first access actually creates new Address objects

The image above shows that the first time I access the list of Addresses a SQL Statement is executed to retrieve all rows from the database. For every row an Address object is created. For the 2nd time the ADO.NET Entity Framework again retrieves all the rows from the database in order to check for additional rows. The default option of the ObjectQuery property MergeOption is MergeOption.AppendOnly which means that only new objects will be created. Existing objects will remain the current state including potentially modified property values. First lesson learned though is that accessing the same list of objects again ALWAYS results in a database roundtrip.

MergeOption.NoTracking and MergeOption.OverwriteChanges

NoTracking means that the ObjectStateManager is bypassed and therefore every access to the Entity Objects results in a fetch from the database and the creation of new objects.
OverwriteChanges is basically the same as NoTracking. All data is fetched from the database and new objects are created and replace the objects currently held by the ObjectStateManager.

Extending the above example with one of the following two lines of code

myReadDatabase.Address.MergeOption = System.Data.Objects.MergeOption.NoTracing;
myReadDatabase.Address.MergeOption = System.Data.Objects.MergeOption.OverwriteChanges;

result in the following execution

Using MergeOption.OverwriteChanges or MergeOption.NoTracking results in creating new objects
Using MergeOption.OverwriteChanges or MergeOption.NoTracking results in creating new objects


MergeOption.PreserveChanges preserves the changes that have been done to objects managed by the ObjectStateManager. Only unchanged properties will be updated with new values from the database.
My logical conclusion was that no new objects had to be allocated as the framework would just need to store the values retrieved from the database in the existing objects properties in case they have not been modified locally. It however turned out that even with MergeOption.PreserverChanges new Address objects got created. The PurePath looked identical to the one with NoTracking and OverwriteChanges. It seems that the Entity Framework creates objects but then only sets those properties that have changed in the database and that have not been modified locally on the existing objects.

Conclusion – be aware of the memory overhead

The conclusion that I draw is that the ADO.NET Entity Framework – in the way that I used it in my sample above – creates a lot of short living objects which result in high memory usage and frequent garbage collections.
Fortunately the default option is MergeOption.AppendOnly which only creates new objects in case there are new rows in the database. Using AppendOnly still allows you to query updated data from existing Entity Objects via the Refresh method of your ObjectContext.

Visit our dedicated webpage about ADO.NET monitoring to learn more about how Dynatrace supports ADO.NET.

Andreas Grabner has 20+ years of experience as a software developer, tester and architect and is an advocate for high-performing cloud scale applications. He is a regular contributor to the DevOps community, a frequent speaker at technology conferences and regularly publishes articles on You can follow him on Twitter: @grabnerandi