Using a GUID as an EntityKey in Entity Framework 4

Do you have tables that use GUIDs (columns of type uniqueidentifier) as primary keys in SQL Server? This scenario is now fully supported in Entity Framework 4.

The Scenario

Like many developers, I happen to be a fan of using uniqueidentifier columns as primary keys on database tables (as opposed to the other common practice of using integers). While I don’t do this all the time, in many cases I think it just makes sense, especially when the records in and of themselves don’t share a “sequential” relationship to one another. I understand this is a rather controversial topic in some circles; with good arguments on both sides. Quite frankly, whether you personally agree with this practice or not is beside the point. For the purposes of this discussion, it’s just important that you understand two things: one, that this done all the time; and two, that this scenario was not properly supported under Entity Framework V1.

The Problem

The specific issue concerns how identity values are assigned to new records under this scenario. Usually, a new GUID is generated in the database by using the SQL Server NEWID() function as the default binding for the PK column. In Entity Framework, as you probably know, when you insert a new record by calling the SaveChanges method on the ObjectContext, you are supposed to be able to access all of the properties of that record, including the new server-generated ID. In order for this to work, the provider has to support returning the identity value from the server. Under EF 1, the provider didn’t generate the required TSQL to make that happen.

When an entity is created from a database table in Entity Framework, it uses the primary key (or keys) from the table to create an EntityKey. Under EF1, there was certainly no problem creating entities with EntityKey of type Guid. You could query those entities just fine. You could even update them. But doing inserts with server-generated GUID values was a big fat no-go.

The usual workaround for this was to simply forgo generating the identity values in the database with the NEWID() function, and instead  generate it on the client and pass it in as part of the entity’s constructor, as you can see in Listing 1.

using (EFSampleEntities context = new EFSampleEntities())
{
   Motorcycle motorcycle = new Motorcycle
                              {
                                 ID = Guid.NewGuid(),
                                 Make = "Suzuki", 
                                 Model = "B-King", 
                                 EngineCC = 1340
                              };
   context.Motorcycles.AddObject(motorcycle);
   context.SaveChanges();
}

Listing 1

How It Works Under Entity Framework 4

Obviously, failing to support server-generated GUID identity values was one (unfortunately, one of the many) shortcomings of Entity Framework V1. This was something the EF Team promised to address under the second version of the Entity Framework (dubbed Entity Framework “4” by the marketing geniuses at Microsoft, in an effort to make the EF version number match the .NET Framework version number, while thoroughly confusing us at the same time).

I was eager to give it a go the other day, so I set up a little test project with a SQL Server 2008 database, one tiny little table and a single-entity model. As you can see in Figure 1, the ID column of this table in the database is a primary key, is of typeuniqueidentifier, and derives its default value from the NEWID() function.



Figure 1

The EF Designer screenshot in Figure 2 shows that the corresponding  Motorcycle entity has an EntityKey of type Guid that maps to the table’s ID column.



 Figure 2

Fantastic. So, I proceed to run the code from Listing 2 below, except this time I don’t generate the identity value on the client. The database is going to do that for me, thank you very much!
class Program
{
   static void Main(string[] args)
   {
      InsertRecord("Suzuki", "B-King", 1340);
   }

   private static void InsertRecord(string make, string model, int engineCC)
   {
      using (EFSampleEntities context = new EFSampleEntities())
      {
         Motorcycle motorcycle = new Motorcycle
                                    {
                                       Make = make, 
                                       Model = model, 
                                       EngineCC = engineCC
                                    };
         context.Motorcycles.AddObject(motorcycle);
         context.SaveChanges();
      }
   }
}
Listing 2





I type this in, press F5, and it all appears to work. Or… does it?

The answer is, yes and no. The record did get inserted into the database. But take a look at Figure 3:



Figure 3




The new GUID is all zeros! Well, that’s not cool. And so I go poking around the IDE for a few minutes and discover that Entity Framework has, for some reason, failed to recognize ID as an identity field that is generated on the database side. That is indicated by the StoreGeneratedPattern property of the ID property, visible in the Properties window, which was still set to None. No problem… clickety click, and that’s all fixed.





Figure 4




And so I run the code again and… whaaaaaaaa?!? The new GUID is still 00000000-0000-0000-0000-000000000000.



The Payoff



The issue lies in the
fact that the Entity Framework designer is a representation of only the
conceptual part of the data model. When you set the StoreGeneratedPattern
attribute on an entity property using the designer, it merely adds an
annotation to the property in the CDSL part of the underlying .edmx file.








<!-- CSDL content -->
<edmx:ConceptualModels>
  <Schema Namespace="EFSampleModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
    <EntityContainer Name="EFSampleEntities" annotation:LazyLoadingEnabled="true">
      <EntitySet Name="Motorcycles" EntityType="EFSampleModel.Motorcycle" />
    </EntityContainer>
    <EntityType Name="Motorcycle">
      <Key>
        <PropertyRef Name="ID" />
      </Key>
      <Property Name="ID" Type="Guid" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
      <Property Name="Make" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />
      <Property Name="Model" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" />
      <Property Name="EngineCC" Type="Int32" Nullable="false" />
    </EntityType>
  </Schema>
</edmx:ConceptualModels>
Listing 3




However, this action does not add the required attribute to the storage part of the data model. You actually have to open the .edmx  file in an XML editor, find the identity column in the SSDL, and add the StoreGeneratedPattern attibute by hand.


<!-- SSDL content -->
<edmx:StorageModels>
  <Schema Namespace="EFSampleModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
    <EntityContainer Name="EFSampleModelStoreContainer">
      <EntitySet Name="Motorcycles" EntityType="EFSampleModel.Store.Motorcycles" store:Type="Tables" Schema="dbo" />
    </EntityContainer>
    <EntityType Name="Motorcycles">
      <Key>
        <PropertyRef Name="ID" />
      </Key>
      <Property Name="ID" Type="uniqueidentifier" Nullable="false" StoreGeneratedPattern="Identity" />           
      <Property Name="Make" Type="nvarchar" Nullable="false" MaxLength="50" />
      <Property Name="Model" Type="nvarchar" Nullable="false" MaxLength="50" />
      <Property Name="EngineCC" Type="int" Nullable="false" />
    </EntityType>
  </Schema>
</edmx:StorageModels>
Listing 4


Once you do this, inserts performed by the provider when calling SaveChanges will allow the database to generate the identity value properly, and retrieve the value reliably so that you can work with it through the ObjectContext.

In case you are wondering about the T-SQL that the provider actually generates to do the insert, then retrieve the GUID, you can find it below in Listing 5. It creates a temporary table to hold the identity value, uses an OUTPUT clause to grab the new value and insert it into the temporary table, then does a join between the temporary and main tables to select and return the value. Pretty clever stuff.
declare  @generated_keys  table(
                                [ID] uniqueidentifier
                                )
insert [dbo].[Motorcycles]
      ([Make],
       [Model],
       [EngineCC])
output inserted.[ID] into @generated_keys
values('Suzuki' /* @0 */,
       'B-King' /* @1 */,
       1340 /* @2 */)
select t.[ID]
from   @generated_keys as g
       join [dbo].[Motorcycles] as t
         on g.[ID] = t.[ID]
where  @@ROWCOUNT > 0