NOTE as of 3/27/07: In more recent CTPs, the mechanism described below has been replaced by something much simpler.  See this follow-up post for more details. 

 

Here’s another subject on which we of the ADO.Net team spent a lot of time while getting ready for this release: automatic generation of key values.  This seemingly simple topic turns out to be a bit more subtle when you dig into it.

 

The idea is pretty straightforward.  In order to make my object model simple, in many cases I’d like to be able to just create an object, add it to the context, call SaveChanges and know that the system will set an appropriate value for my key.  Yes, the key is needed for the database to keep the entities straight, and it is used for identity resolution by the query/materializer system, but when I’m programming at the object layer (and given that I have identity resolution) I generally just want to use object references for identity.  So why should I have to think about the key at all?

 

To answer this question, the first thing you need to know is that object services uses the key in a number of indexes so a key value is required from the moment you add an object to the context.  To make matters worse, the key must be unique—not only among objects you have created that are waiting to be added to the database but across all objects in the context (which could be anything in the database if you perform a query). 

 

If the key for your entity is a natural value (such as a social security number) or something which can be generated on the client with a guarantee that it is truly unique (like a guid—I know guids are guaranteed to be unique but often folks treat them that way), then this task is easy: set the appropriate property on your object, add the object to the context, and you are good to go.

 

If, however, your key value is one which cannot be generated on the client in a way that guarantees uniqueness across all values on the server—maybe it’s an auto-incrementing integer ID and you have more than one client running against the database at a time (I know this scenario is really “out there” but maybe one or two of you have encountered something like it before)—if you are in that kind of situation, then you have a much harder problem.  There are two big issues:

 

  1. How do you get a temporary value that you know won’t collide with something on the server?

  2. How do you fix up the key once you save and the server determines the real value? This question is especially difficult given that the context forces key values on its objects to be read-only so that its indexes don’t get confused.  You can modify the key values of an object which is detached from the context, but once you add it, you can’t change the values.

We solve both of these problems with specific runtime support in the Entity Framework which you can turn on with annotations in your CSDL and SSDL schemas. 

 

Problem #1 is solved by adding the attribute “ClientAutoGenerated = true” to the appropriate property in an entity type definition.  This tells the object services layer to automatically generate a new value for the property which is guaranteed to be unique across all new objects added on this client (modulo the possibility of specifying a property type which is relatively small—say Int16—and then adding so many objects in a single session without performing a save that the values wrap around).  Further, it tells the object services layer to set a special flag in the EntityKey which can only be set by this internal code path and which is used to guarantee that the temporary client-generated value will never be equal to a value retrieved from the server through a query.

 

Problem #2 is solved by adding a second attribute “StoreGenerated = true” to the same property in the entity type definition.  This instructs object services to examine the result of saving an object to the database and write returned values back to the entity.  Unlike ClientAutoGenerated, this attribute can be placed on a property regardless of whether or not it is part of the key.  If it is part of the key, not only will the object be updated to have the value set by the server, but also the EntityKey will be set to its new, permanent value and the context’s indexes will be fixed up to match.

 

So, if I had this in my CSDL:

 

    <EntityType Name="Room" Key="ID">

        <Property Name="ID" Type="Int32" ClientAutoGenerated="true

                  StoreGenerated="true" />

        <Property Name="Name" Type="String" Nullable="false"

                  MaxLength="50" />

    </EntityType>

 

with an appropriate SSDL and mapping between them I could write code like this:

 

    Room newRoom = new Room();

    newRoom.Name = "the dragon's lair";

    db.AddObject(newRoom);

 

    // Save away the ID generated when the object was added to the

    // context so that we can check it later.

    int tempID = newRoom.ID;

 

    // Query all the existing rooms into the context.  None of the keys

    // for these rooms will conflict with the temp key generated for

    // the new room.

    foreach (Room dbRoom in db.Rooms)

    {

        Console.WriteLine(dbRoom.Name);

    }

 

    // save the new room & then verify that the ID was updated

    db.SaveChanges();

    Debug.Assert(newRoom.ID != tempID);


Ta da!  Now you have key values that you can treat as “fire and forget” and know that you will never have a collision (at least as long as the type of your key property is large enough for the number of entity instances).

 

For the sake of completeness, there are a few other points that I should mention:

 

  • First, because we wanted to hit this core scenario and didn’t have time to address the full range of possibilities, ClientAutoGenerated currently only works for the data types Int16, Int32 and Int64.  We can certainly imagine extending it to a range of types with appropriate declarative specifications of how to handle them.  In a later release, we might support GUIDs, DateTimes and other things either as temporary client-only values or even as client-generated values that will be permanent.  For now, though, you must either use integers or add some logic of your own to handle client key generation.  Since the uniqueness of the key is not an issue until the object is added to the context, if you want to add your own logic for generating values, you can either do some custom construction (see this blog post about the joys of custom construction for more details) or just set the property after constructing but before adding to the context.

  • Secondly, while setting the above attributes in the CSDL is enough to tell object services how to handle this scenario, it is not enough to tell the update pipeline how to deal with server generated values.  If you want this to work end-to-end, you also need to specify the “StoreGeneratedPattern” attribute in the SSDL.  This attribute’s value is more than just a Boolean because a server generated value can be something that is generated only once when the row is first added to the database or something that changes every time the row is updated or it can follow other patterns.  For now I’ll just give you the standard pattern which works for fire-and-forget keys (generate once when the row is added known as “identity”).  To make this scenario work the SSDL would need to contain something like this:

 

    <EntityType Name="rooms" Key="id">

        <Property Name="id" Type="int" Nullable="false"

                  StoreGeneratedPattern="identity" />

        <Property Name="name" Type="nvarchar" Nullable="false"

                  MaxLength="50" />

    </EntityType>

 

Maybe I’ll dive into the other store generation patterns in some other blog post, but my kids are about to go nuts because I’ve been ignoring them and staring at this computer so long, so I’m going to call it a day.

 

- Danny