Setting Entity Framework MergeOptions – What Works, What Doesn’t

The Basics
When retrieving entities using object queries with the Entity Framework, there is a MergeOption setting that allows you to specify how you want entities to be retrieved. The default value is AppendOnly, which specifiies that if an entity already exists in the ObjectContext, to retrieve it from there. Otherwise, it is retrieved from the database. This means that if you retrieve an entity, change some of its property values and then retrieve it again from the ObjectContext, you will not get a fresh copy from the database–you’ll get the cached object in the ObjectContext. For a great explanation of how all this works, check out this link:


http://blogs.msdn.com/dsimmons/archive/2010/01/12/ef-merge-options-and-compiled-queries.aspx 


How to Specify the MergeOption
There are a few ways to specify the MergeOption you want. I’ll concnetrate on doing this with a LINQ query, and for these examples, I will change the value to OverwriteChanges, which retrieves a new entity from the data store rather than a cached instance from the ObjectContext.


1. Set the value of the EntitySet.MergeOption property


When you do this, any queries you create from the entity set will use the MergeOption value you specify. For example:


this.ObjectContext.EmployeeEntitySet.MergeOption = MergeOption.OverwriteChanges;
IQueryable<EmployeeEntity> query = from e in this.ObjectContext.EmployeeEntitySet
      where e.EmployeeID == employeeID
      select e;
return query.FirstOrDefault();


2. You can create an object query and set its MergeOption property

When you do this, the setting only applies to the current query. For example:


ObjectQuery<EmployeeEntity> query = this.ObjectContext.CreateQuery<EmployeeEntity>(“[EmployeeEntitySet]“);
query.MergeOption = MergeOption.OverwriteChanges;
var res = from ei n query
      where e.EmployeeID == employeeID
      select e;
return res.FirstOrDefault();

3. You can convert the IQueryable<T> into an object query, call Execute() and specify the MergeOption

This also sets the MergeOption on a specific query, rather than all queries of that type. For example:


IQueryable<EmployeeEntity> query = from e in this.ObjectContext.EmployeeEntitySet
      where e.EmployeeID == employeeID
      select e;
ObjectQuery objectQuery = (ObjectQuery)query;
ObjectResult<EmployeeEntity> result = (ObjectResult<EmployeeEntity>objectQuery.Execute(MergeOption.OverwriteChanges);
return result.FirstOrDefault();

4. The following does NOT work.


IQueryable<EmployeeEntity> query = from e in this.ObjectContext.EmployeeEntitySet
      where e.EmployeeID == employeeID
      select e;
ObjectQuery objectQuery = (ObjectQuery)query;
objectQuery.MergeOption = MergeOption.OverwriteChanges;
return query.FirstOrDefault();

Setting the MergeOption at this point is ‘too late to the dance!”. The query will use the default AppendOnly option instead.

Best Regards,
Kevin McNeish
INETA Speaker
Chief Architect, MM .NET Application Framework
www.oakleafsd.com