The key to AddOrUpdate

The DbSet.Find method provides an easy way to lookup an entity given its primary key. One place this can be useful is in an AddOrUpdate method when loading an entity which can then be updated with values from another application tier—for example, updating an entity with values from a client in a web application.

However, it isn’t so easy to do this in a generic way on any entity type without specific knowledge of which properties make up the primary key. This is something we will make easier in a future release of EF, but for now this blog post shows how to write some extension methods that make this process easier.

The scenario

Let’s say we have an object that has been created outside the context and contains values that need to be written back to the database. There are many opinions out there on how to do this and they each have their advantages and disadvantages. Some options are:

  1. Write all property values (or a fixed sub-set) to the database always even if this means sending updates for properties that haven’t changed.
  2. Query for the entity before updating and use the property values of the queried entity to determine which values have actually changed so that updates are only sent for only those properties.
  3. Track the original values of all properties across tiers and then use these values to determine which property values have actually changed so that updates are sent only for those properties.

Concurrency tokens may also be used with options 2 and 3. The point of this post is not to make any judgment about which is best but rather to show how using EF to obtain primary key information can enable generic methods to be written for options 1 and 2.

The non-generic AddOrUpdate

Assuming we’re going for option 2 above (option 1 is covered later), then, using the model at the end of this post, a method to add or update an OrderLine might look like this:

public OrderLine AddOrUpdate(WindyContext context, OrderLine orderLine)
{
    var trackedOrderLine = context.OrderLines.Find(orderLine.OrderId, orderLine.ProductId);
    if (trackedOrderLine != null)
    {
        context.Entry(trackedOrderLine).CurrentValues.SetValues(orderLine);
        return trackedOrderLine;
    }

    context.OrderLines.Add(orderLine);
    return orderLine;
}

The keys points from this code are:

  • Find is used to query the database for the entity with the same primary key values as the object passed to AddOrUpdate. Two values are passed to Find since OrderLine has a two-part composite key.
  • If Find returns null it means that no OrderLine with the given key was found. In this case the new OrderLine needs to be inserted, so it is passed to the Add method.
  • If Find returned an entity then it must be updated with values from the object passed to AddOrUpdate. The SetValues method does this. Crucially, only those properties with values that are different will be marked as modified.
  • After calling AddOrUpdate SaveChanges must be called. This either inserts a new entity or sends an update for the properties that were modified. SaveChanges does nothing if no properties were modified.
  • The method returns the entity added or updated because this can be a useful pattern for composition.

A generic AddOrUpdate using Find

To make the AddOrUpdate method generic all uses of OrderLine need to be replaced by uses of a generic type—let’s call it TEntity. This is easy for the OrderLines property—it can be replaced with a call to Set<Tentity>().

The trickier part is the use of the OrderLine to get the primary key values for Find. This is where the extension method will be used—let’s call it KeyValuesFor. The generic code will then look like this:

public TEntity AddOrUpdate<TEntity>(DbContext context, TEntity entity)
    where TEntity : class
{
    var tracked = context.Set<TEntity>().Find(context.KeyValuesFor(entity));
    if (tracked != null)
    {
        context.Entry(tracked).CurrentValues.SetValues(entity);
        return tracked;
    }

    context.Set<TEntity>().Add(entity);
    return entity;
}

Finding primary key property names

Before looking at property values let’s take a step back and write a method to find the property names that make up the primary key. As of EF5 this code requires dropping down to ObjectContext and using the MetadataWorkspace:

public static IEnumerable<string> KeysFor(this DbContext context, Type entityType)
{
    Contract.Requires(context != null);
    Contract.Requires(entityType != null);

    entityType = ObjectContext.GetObjectType(entityType);

    var metadataWorkspace =
        ((IObjectContextAdapter)context).ObjectContext.MetadataWorkspace;
    var objectItemCollection = 
        (ObjectItemCollection)metadataWorkspace.GetItemCollection(DataSpace.OSpace);

    var ospaceType = metadataWorkspace
        .GetItems<EntityType>(DataSpace.OSpace)
        .SingleOrDefault(t => objectItemCollection.GetClrType(t) == entityType);

    if (ospaceType == null)
    {
        throw new ArgumentException(
            string.Format(
                "The type '{0}' is not mapped as an entity type.",
                entityType.Name),
            "entityType");
    }

    return ospaceType.KeyMembers.Select(k => k.Name);
}

This method returns a list of names because for entities with composite keys (like OrderLine) there are multiple properties that form the key. For entities that don’t have composite keys (which is the common case) the returned list will only contain one item.

You don’t really need to know the details of what is happening here, but for those interested:

  • Metadata for all the o-space types known about by the context is requested. This is the ObjectItemCollection. (O-space is jargon for object-space which means metadata about your CLR types.)
  • O-space metadata specifically for entity types is requested.
  • This is filtered for the o-space type that matches the CLR type of the entity. Since the code is using EF 4.1 or above its safe to assume there will be zero or one matches.
  • Assuming that a type is found the KeyMembers property of its metadata is used to obtain and return a list of key names.
  • The code uses Code Contracts but if you’re not using these then just remove the Contract.Requires calls.
  • Thanks to wiky87 for pointing out that the original code didn’t work for proxy types. This is fixed by the GetObjectType call which returns the real entity type when given a proxy type.

Finding primary key property values

Once the primary key properties are known it is fairly easy to get the values of these properties:

public static object[] KeyValuesFor(this DbContext context, object entity)
{
    Contract.Requires(context != null);
    Contract.Requires(entity != null);

    var entry = context.Entry(entity);
    return context.KeysFor(entity.GetType())
        .Select(k => entry.Property(k).CurrentValue)
        .ToArray();
}

This method returns an array of values because that’s what the params parameter of Find requires.

A different generic AddOrUpdate

Suppose that instead of using Find you decided to go with option 1 from those listed above. A common non-generic way to write this method is:

public OrderLine AddOrUpdate(WindyContext context, OrderLine orderLine)
{
    context.Entry(orderLine).State =
        (orderLine.OrderId == 0 && orderLine.ProductId == 0)
            ? EntityState.Added
            : EntityState.Modified;
    
    return orderLine;
}

This method uses the convention that if the primary key is zero then the entity is new and so should be inserted. Otherwise it already exists in the database and so should be updated.

This convention can be generalized to say that if all the properties that make up an entity’s primary key have default values (0, null, etc.) then the entity is new otherwise it already exists.

Using this generalized convention and the KeyValusFor method the AddOrUpdate can again be made generic:

public TEntity AddOrUpdate<TEntity>(DbContext context, TEntity entity)
    where TEntity : class
{
    context.Entry(entity).State =
        context.KeyValuesFor(entity).All(IsDefaultValue)
            ? EntityState.Added
            : EntityState.Modified;

    return entity;
}

private static bool IsDefaultValue(object keyValue)
{
    return keyValue == null
           || (keyValue.GetType().IsValueType
               && Equals(Activator.CreateInstance(keyValue.GetType()), keyValue));
}

(Note that the helper method that checks whether or not a key value is default might not work for some corner case types like void, but that doesn’t matter for our purposes because such types will never be used for EF key properties anyway.)

The model

For reference here’s the model I used while writing this post:

public class Order
{
    public int Id { get; set; }

    public virtual ICollection<OrderLine> OrderLines { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<OrderLine> OrderLines { get; set; }
}

public class OrderLine
{
    [Key, Column(Order = 1)]
    public int OrderId { get; set; }
    [Key, Column(Order = 2)]
    public int ProductId { get; set; }

    public int Quantity { get; set; }

    public virtual Order Order { get; set; }
    public virtual Product Product { get; set; }
}

public class WindyContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<OrderLine> OrderLines { get; set; }
}

Thanks
Arthur

About Arthur Vickers

Developer on the Entity Framework team at Microsoft.
This entry was posted in Change Tracking, DbContext API, Entity Framework and tagged , , , , , , , . Bookmark the permalink.

18 Responses to The key to AddOrUpdate

  1. wiky87 says:

    Very nice post – thanks a lot!

    I have missed suppport for proxy entities at the KeyValuesFor-method and ToList class against lazy execution, so i have modified your code. Take a look at (its german – but you can read code only ;) ):
    http://blog.technophobie.de/2012/05/ef-entitaten-finden-der-primary-keys.html

  2. Matt says:

    This is awesome. I did not know about DbPropertyValues.SetValues. That in itself made this post worth reading.

    Are you able to suggest any other blogs that also contain information on EF?

    Please keep posting!

    • Glad it was helpful. There is a list of links to other blogs on the homepage. Rowan’s, Julie’s and the EF Team blog probably have the most recent EF posts.

  3. Merdan Gochmuradov says:

    Hello Arthur!
    Thanks for grate post!
    I have a question. I used to use entity classes with base class where custom change tracking is applied. And every time I save a list of entities, I check for change state in base class and apply it to context as ” db.Entry(model).State = model_state; “. The reason I don’t use “Find” and “SetValues” method is because of performance thoughts. There can be lists with 100,000 items and I don’t want the context to fetch and compare each row from db. So what do you think of this strategy? Or what strategy could you advise?
    Thanks!

    • I think that tracking the state of the entity in the entity itself can work fine for n-tier scenarios so long as you are okay with mixing the persistence code with the rest of your app code and so long as the change tracking you are trying to do is relatively simple and constrained. If you try to track every possible change to a graph this way you end up with something similar to EF self tracking entities where the complexity of the code in the entities often leads to problems.

  4. Arthur,
    Excellent. I am fairly new to EF, and the new Code First model finally has me onboard. I was looking at the DbContext.entity.AddOrUpdate() method as I have heard it is not intended for this type of scenario. I stumbled across this post and this not only replaces my use of AddOrUpdate but also gets me to the level of generic entity methods in general; somewhere I knew I needed to get to eventually.
    Much appreciated!

  5. Fariborz seyedi says:

    Hello Arthur and Thanks For your very nice Post!

  6. Félix says:

    Hi Arthur,
    Let me congratulate you for a clean and intelligible article. But I have an issue with these lines, I never achieve calling it:

    return context.KeysFor(entity.GetType())
    08 .Select(k => entry.Property(k).CurrentValue)
    09 .ToArray();

    I believe I use VS2012 and EF 4.1.
    The problem is: context.KeysFor.
    My EntityRepositoryBase has within an context instance, that inherates from DBContext.
    Any suggestions?
    Thanks in advanced.

  7. Eric says:

    Thanks for this. Is this better to use in practice than IDbSetExtensions.AddOrUpdate()? (http://msdn.microsoft.com/en-us/library/hh846521(v=vs.103).aspx)

  8. @Eric The AddOrUpdate extension method is primarily intended to help with writing seed methods when using Migrations. It can also be used in other situations, but I believe (if my memory is correct) that it uses a database round trip so it may not be the best performing option depending on exactly when and how it is used,

  9. Ben Junior says:

    The example is excellent. Thanks so much. I don’t want to diverge from the subject but, in my case, I would need to know how many rows were created and how many updated. No problem to implement this on the former, but on the later, do I need to write code to compare the two entities to see if they are different, or is there a method that can tell me that ?

    • @Ben You can count the number of entities in the Modified state using something like

      var count = context.ChangeTracker.Entries().Count(e => e.State == EntityState.Modified);

      This should give you the number of updates that will be sent to the database. There is also a generic version of Entries if you want to count only entries for a certain type of entity.

  10. Arturo Hernandez says:

    This is really useful for web applications where the objects are “new”. In my use case I am also updating the order header. Plus for updates, I need to verify that the order owner is the same as the one doing the update. So I do a context.Order.Find first. That means that now the order is loaded in the context. Therefore I need to do the following or else I get an error.

    if (!order.OrderLines.Exists(e => e.OrderId == orderLine.OrderId && e.ProductId == orderLine.ProductId))
    order.OrderLines.Add(orderLine);

    Also I need to delete the non-matching orderLine. So I have to compare it to the old OrderLines in Order to find the deletes. I can probably figure it out how to do this but if you have any pointers at all I would appreciate them. I would be more than happy to share the resulting code.

  11. Arturo Hernandez says:

    Great article!

    I would like to be able to do something like this but for One to Many updates. To keep it simple maybe something like this.

    ctx.OrderLines.AddOrUpdateSet(order.OrderLines, a => a.Order)

    I posted the question on stackoverflow, I hope I can get some answers. http://stackoverflow.com/questions/17604836/how-can-i-create-a-generic-update-method-for-one-to-many-structures-in-entity-fr

    • @Arturo I’m having difficulty understanding what your specific question is. Are you asking if somebody can write this, or did you try to write it and find it didn’t work in some way? If the latter, what specifically is the problem?

      Thanks,
      Arthur

  12. Works like a charm. Exactly what I was looking for when using my Generic Repository in a Detached manner (Web).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s