Entity Framework relations and entityKey

Suppose you generate an Entity Framework model on standard northwind database, you have a customer id and you want to generate an order for that customer. A possible solution is

using (ModelTestBase context = new ModelTestBase()) { Orders order = new Orders(); order.Freight = 1.0M; order.RequiredDate = order.OrderDate = DateTime.Now; order.Customers = context.Customers.Where(c => c.CustomerID == "ALFKI").First(); context.AddToOrders(order); context.SaveChanges(); }

This code is perfectly valid, but is not the smartest thing to do, if you analyze traffic with profiler you can verify that this code executes a select to retrieve the customer, and then inserts the order into orders table. The question is, since I already have the ID of the customer why I need to retrieve the whole customer instance from database only to set a relation?. When you need to setup a relations EF needs only to know the key of related object, it does not really cares about other properties of Customers object, it needs only the key to set the foreign key, a better solution is the following

1 using (ModelTestBase context = new ModelTestBase()) 2 { 3 Orders order = new Orders(); 4 order.Freight = 1.0M; 5 order.RequiredDate = order.OrderDate = DateTime.Now; 6 IEnumerable<KeyValuePair<string, object>> entityKeyValues = 7 new KeyValuePair<string, object>[] { 8 new KeyValuePair<string, object>("CustomerID", "ALFKI") }; 9 EntityKey key = new EntityKey("ModelTestBase.Customers", entityKeyValues); 10 order.CustomersReference.EntityKey = key; 11 12 context.AddToOrders(order); 13 context.SaveChanges(); 14 }

The core difference is in lines 6-10, I simply create an EntityKey object for the "ModelTestBase.Customers" object specifying its key value. This code inserts the order and sets the relation without the need to load the customer object. This stuff works only to set relations, if you try to call order.CustomersReference.Load() to load related customer you will get exception, if you call it before the order object is attached to the context you get:

The EntityReference could not be loaded because it is not attached to an ObjectContext.

This is the expected behavior, after all if the order is still transient it cannot load anything, but if you attach the order with AddToOrders() before calling the Load method you will get another exception

The source query for this EntityCollection or EntityReference cannot be returned when the related object is in either an added state or a detached state and was not originally retrieved using the NoTracking merge option.

This is annoying because I have a valid order object, it is attached to the context so the RelationshipManager should really loads the Customer Entity for me. It turns out that loading from an entity that is in state added is not a safe stuff, here is correct snippet

EntityKey key = new EntityKey("ModelTestBase.Customers", entityKeyValues); order.CustomersReference.EntityKey = key; context.AddToOrders(order); context.SaveChanges(); Console.WriteLine(order.Customers); order.CustomersReference.Load(); Console.WriteLine(order.Customers);

Now after the order object was committed and is not anymore in added state you can load related customer.

Another interesting operation you can do with an entity key is retrieving an entity given its key.

using (ModelTestBase context = new ModelTestBase()) { IEnumerable<KeyValuePair<string, object>> entityKeyValues = new KeyValuePair<string, object>[] { new KeyValuePair<string, object>("CustomerID", "ALFKI") }; // Create the key for a specific SalesOrderHeader object. EntityKey key = new EntityKey("ModelTestBase.Customers", entityKeyValues); Customers c = (Customers) context.GetObjectByKey(key); }

This code is really verbose,  but it is clearer than retrieving the object with a LINQ query, after all I should have a good way to load an object by key, not using a query with First(). It turns out that you could create an extension method to make your life simpler.

public static class EFExtensions { public static T LoadByKey<T>(this ObjectContext context, String propertyName, Object keyValue) { IEnumerable<KeyValuePair<string, object>> entityKeyValues = new KeyValuePair<string, object>[] { new KeyValuePair<string, object>(propertyName, keyValue) }; // Create the key for a specific SalesOrderHeader object. EntityKey key = new EntityKey(context.GetType().Name + "." + typeof(T).Name, entityKeyValues); return (T)context.GetObjectByKey(key); } }

This extension method will apply to all objectcontext objects, it needs caller to specify type of the entity to load, name of the key property and value to retrieve key property. It works only with entities with single value key, but I left to the reader the task to extend it to supply multiple valued keys. Now you can load a Customer by Key with this code

Customers c = context.LoadByKey<Customers>("CustomerID", "ALFKI");

I wonder why such a method was not included in the base definition of the objectContext.



Published by

Ricci Gian Maria

.Net programmer, User group and community enthusiast, programmer - aspiring architect - and guitar player :). Visual Studio ALM MVP

6 thoughts on “Entity Framework relations and entityKey”

  1. Let me relate my experience with this nasty error and point out the terrain chasing it will take you over leading to a
    tremendously simple solution.

    CompanyGroup is pretty simple. It has a name and it has a Company object.

    I started with this:

    public static void Add(CompanyGroup item)
    using (Entities scope = new Entities())
    catch (Exception ex)
    LogException(ex, item);

    And got this error:

    {“An entity object cannot be referenced by multiple instances of IEntityChangeTracker.”}

    So, I added this between lines 6 and 7:


    That rewarded me with:

    {“The object cannot be added to the ObjectStateManager because it already has an EntityKey.
    Use ObjectContext.Attach to attach an object that has an existing key.”}

    So I changed


    to scope.Attach(item);

    Now it complained about:

    {“An object with a temporary EntityKey value cannot be attached to an object context.”}

    (beginning to sound like some of the girls I dated in my youth, but I digress)

    So I made the entity key null (didn’t work) and used the method to create new (didn’t work, either)

    Along the way, I got this error, too:

    {“The source query for this EntityCollection or EntityReference cannot be returned when the related object is in either an
    added state or a detached state and was not originally retrieved using the NoTracking merge option.”}

    The Solution?

    Replace the core, lines 7 and 8, with:

    CompanyGroup newcg = new CompanyGroup();
    newcg.GroupName = item.GroupName;
    newcg.Company = scope.Company.Where(c => c.CompanyID == item.Company.CompanyID).First();


    Essentially, I took the data passed via ‘item’, and moved it to newly created object of the same type that introduces the same
    scope as the one used in the Add.

  2. The reason that your extension method is not part of the framework is that the first parameter to the EntityKey constructor is the entity set name, not the entity type name. By default (in 1.0) these are the same. In 4.0, the set will be automatically pluralized. But even in 1.0, it is possible to pluralize it yourself. You can even define multiple set of the same entity type. In these situations, your extension method will break.

  3. thanks for your code, it works for insert but for update, it updates all properties successfully but the parent not updated.

    public void UpdateGame(Games game)
    EntityKey key = null;
    object original = null;
    using (EntitiesContx contx = new EntitiesContx ())
    Games g = new Games();
    g.GameName = game.GameName;
    g.GameDescription = game.GameDescription;
    g.GameId = game.GameId;

    EntityKey _relationshipKey = new EntityKey();
    _relationshipKey.EntityContainerName = typeof(EntitiesContx).Name;
    _relationshipKey.EntitySetName = typeof(Genre).Name;
    _relationshipKey.EntityKeyValues = new EntityKeyMember[1] { new EntityKeyMember(“GenreId”, game.Genre.GenreId) };

    g.GenreReference = new EntityReference();
    g.GenreReference.EntityKey = _relationshipKey;

    contx.AttachTo(typeof(Games).Name, g);

    catch (Exception ex)



    any help

Comments are closed.