LeeDumond.com

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, as long as you remember one little gotcha.

---

Yes, it’s been months since I’ve blogged, as I’ve been hammer-down on my upcoming book since the beginning of the year. However, I thought I’d put out a quick post as a heads-up to my fellow developers who, like me, are trying to get up to speed with Entity Framework 4. I recently ran into an issue concerning a rather common database scenario, which bugged me for the better part of a day. Surprisingly, I found no mention of it on any search engine. Hopefully, this post should help to remedy that situation.

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 type uniqueidentifier, 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

Okay, at this point I’ll admit I’m completely stuck for an answer. I write to my friend Julie Lerman, author of Programming Entity Framework and one of the most knowledgeable EF developers on planet Earth, and even she is stuck for an answer – a rare event indeed. I post a question on Stack Overflow. No answers there either.

Now, I’m wondering if this scenario really is supported in EF4 at all, as the team promised it would be. As seemed to happen so often in the first version of Entity Framework, it was difficult to know if it was the framework that was causing the problem, or if it was just me.

One pot of coffee, half a tank of premium unleaded, and a few hours of sleep later, I figured it out.

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.

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.

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

Listing 5

Subscribe to this blog for more cool content like this!

Bookmark / Share