My musings about .NET and what not

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, 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.

image

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.

image

 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:

image

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.

image

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!

kick it on DotNetKicks.com

shout it on DotNetShoutOut.com

vote it on WebDevVote.com

Bookmark / Share

    » Similar Posts

    1. Singletons vs. Static Classes
    2. Should We Return Null From Our Methods?
    3. Building a TweetThis Extension with Automatic Bit.ly for Graffiti CMS

    » Trackbacks & Pingbacks

    1. You've been kicked (a good thing) - Trackback from DotNetKicks.com

      Using a GUID as an EntityKey in Entity Framework 4 — May 14, 2010 9:29 PM
    2. Thank you for submitting this cool story - Trackback from DotNetShoutout

      Using a GUID as an EntityKey in Entity Framework 4 — May 15, 2010 1:58 PM
    3. Pingback from Dew Drop – Weekend Edition – May 15-16, 2010 | Alvin Ashcraft's Morning Dew

      Dew Drop – Weekend Edition – May 15-16, 2010 | Alvin Ashcraft's Morning Dew — May 16, 2010 11:36 AM
    Trackback link for this post:
    http://leedumond.com/trackback.ashx?id=87

    » Comments

    1. Jule Lerman avatar

      StoreGenerated Pattern setting in the EDM Designer has NO IMPACT on the SSDL

      It is very misleading.

      It is only there for model first so that you can define SGP so that the generated DDL properly defines columns to be computed or identity.

      But are you saying that the EDM wizard did not recognize and mark that column as an Identity column? It should have. I've always used ints and it's never been a problem.

      Sorry I didn't think to ask you about that.

      Could you compare with a new model bringin in one talbe that has an Int identity column and another that has a GUID identity column and see what you get in SSDL?

      Julie

      Jule Lerman — May 14, 2010 11:06 AM
    2. Julie again avatar

      I looked through our email conversation because I thought for sure I had mentioned this. It turns out that yesterday someone with the SAME scenario was asking about this and I had said in an email to him to check that the SGP was set to Identity for the GUID column.

      Sheesh!

      Too little too late. :)

      Julie again — May 14, 2010 11:12 AM
    3. Dan Esparza avatar

      Wow. My hair (and sanity) thank you. Works as written, however -- just wanted to emphasize the fact there is a CSDL *and* an SSDL section in the .edmx file.

      This attribute goes on the specification in the SSDL section (NOT the CSDL section -- leave the 'annotation' alone there).

      Dan Esparza — May 16, 2010 6:51 PM
    4. Lee Dumond avatar

      Dan,

      I probably didn't make this too clear in the post, but in fact the annotation in the CSDL section (which is set when you set StoreGeneratedPattern to Identity in the designer) doesn't even really need to be there. That attribute only exists to support Model-First scenarios.

      As I point out, a StoreGeneratedPattern attribute does need to go into the SSDL, and you need to type it in manually.

      Lee Dumond — May 17, 2010 1:19 AM
    5. Roboblob avatar

      I was actually having same problem but failed to find solution and used the old trick with Guid.NewGuid() in the constructor of the entity.

      Very nice article, thanks for sharing this tip!

      Roboblob — June 2, 2010 4:28 PM
    6. Nikola Malovic avatar

      How lame it is that:

      - I have to (beside using designer) edit any other file by hand?

      - I know one can use newsequentialid() but generating GUID identities in middle tier makes a lot of sense to me.. How lame is it that EF4 deosn't have anything like NHibernate CombGuid identity generator or extension point for pluging in our implementation

      I have ditched NH in favor of EF4 and so far so good, but support for Guids in EF4 is very weak

      Nikola Malovic — June 2, 2010 6:37 PM
    7. Louis avatar

      Just-in-time. I'm working on this problem today since 1H00PM and I found your article... and it's working after reading it! THANK A LOTTTTTTTTTTTTTTTTTTTTTTT!!

      Louis — June 2, 2010 10:06 PM
    8. Simon Stewart avatar

      When I read "open" and "XML editor" in the same sentence, then I don't think it got much attention from the design team.

      From everything I've read, EF4 is still nowhere near LLBLGen and others.

      It's a pity, because I don't see the dev community having much faith in future EF* products.

      Simon Stewart — June 3, 2010 12:18 AM
    9. Paul avatar

      Model First...Model First...Model First...

      I can't get this to work "nicely" at all. If I edit the SSDL as you advise then create database from model (Model First) I get had over by VS 2010.

      The edit's I do are lost... This is ridiculous. We're not trying rocket science here are we ? This is a simple way of using GUIDs as Key's - after all, an "int" key has to come from a "magic somewhere" right ?... Why can't the GUID do the same "behind the scenes" ?...

      It's absurd to keep re-editing my ID columns only to have them lost. Open the XML... stop right there... These tools are supposed to make our life easier. Why would I EVER consider "Model First"; which incidetntally I love; if all I keep having to do is go off and edit the ruddy XML, its not even as pretty to read as T-SQL !!!

      We need BETTER tools for this. People use GUID's for ID's (an increasing number actually by my experience).

      Why can't we simply have a model first, a GUID key, and an acceptable default value of "(newID())" without the designer moaning. This is bizaare to say the least.

      Paul — June 23, 2010 5:11 AM
    10. @jeffreywatson avatar

      Awesome article and great comments! Good clarification on the CSDL vs. SSDL sections of the .edmx. I ran into this exact issue today. THANKS EVERYONE!

      @jeffreywatson — July 15, 2010 11:18 PM
    11. David avatar

      Could you not just make a parital class to handle the GUID StoreGeneratedPattern to identity. That way you don't have to edit the entity framework, in case it is regenerated? I guess since it is something this simple it will be fixed in a patch. I agree with Paul on this one.

      David — July 30, 2010 10:28 AM
    12. Terry avatar

      So suppose I have a table with a RowID (uniqueidentifier) and a ParentRowID (uniqueidentifier) with a parent - foreign key relationship on the same table. How can I create new rows so that calling SaveChanges will not throw a fit?

      Terry — August 4, 2010 3:45 PM
    13. nachid avatar

      This issue is more general that the GUID case.

      NEWID is only a particular case of a default value that should be generated by the database

      If you take any column whith a default value, it won't work.

      a very common scenerio is a datetime column with default value.

      It is a shame that EF in its version 4 does not handle default value when L2S does from the very beginning

      Default values are very comment needs

      nachid — August 5, 2010 2:44 PM
    14. Cloud Hosting avatar

      Very nice article, thanks for sharing this tip!

      Cloud Hosting — August 11, 2010 7:55 AM
    15. KristoferA avatar

      If you want to avoid the manual chasing-down and updating of CSDL <-> SSDL diffs using an XML editor you may want to take a look at my 'Model Comparer' for EFv4. It compares the database <-> SSDL <-> CSDL , displays the diffs in a tree (or reports) and allows you to bring changes across. It also detects diffs in areas where the VS2010 EFv4 designer (or 'update model wizard') only update the CSDL (or SSDL in the case of the update wizard), such as Store Generated, default values/default constraints, size/precision/scale, nullability etc.

      You can read more (and watch a couple of screencasts) showing what the model comparer can do here:

      huagati.blogspot.com/.../introducing-mod huagati.blogspot.com/.../whats-new-in-mo huagati.blogspot.com/.../using-model-com

      ...and if you want to take it for a test spin you can download it and get a trial license from www.huagati.com/.../dbmltools

      KristoferA — August 17, 2010 6:03 AM
    16. Duncan avatar

      Totally agree with Paul above. We should be able to use model first and generate from the model without having to add newguid() by hand in the DB, which gets wiped out the next time you regenerate. It's sad they didn't get this right - didn't they even try it?

      Duncan — August 19, 2010 2:17 PM
    17. Scott avatar

      Lee, where did you get listing 5 from? I'm trying to do 'database first' using INT IDENTITY instead of UNIQUEIDENTITY and while VS 2010 is correctly setting the StoreGeneratedPattern to Identity in the SSDL, it is still not producing server-side keys. Being able to view the T-SQL would be helpful.

      Scott — August 24, 2010 9:14 AM
    18. Lee Dumond avatar

      @Scott - Listing 5 is from EF Profiler. I would think if your database isn't generating INT keys, that's a SQL Server issue rather than an EF issue, in which case seeing the generated T-SQL probably won't help that much.

      Lee Dumond — August 24, 2010 10:54 AM
    19. Deegii avatar

      Very very thanks man. Is it Microsoft's mistake?

      Deegii — October 26, 2010 12:37 AM
    20. karate kid avatar

      That's a great find and you should actually report it to microsoft!

      karate kid — October 26, 2010 11:42 PM
    21. chris haas avatar

      As great of a find as this is, it's really annoying that MS didn't think far enough (or at least correct this after the fact) so we don't need this workaround. This is a workaround and it will help me, but it makes re-apply this change each and every time I update my model from the database. Annoying to say the least. Having GUIDS as a primary key is very common, MS should fix this now.

      chris haas — November 4, 2010 1:41 PM
    22. Nicolas avatar

      Totally right Chris, Julie Lerman herself knows this behavior "bug" (int vs guid). We are now 6 months later and it's not corrected, and with a big database model, it's not reasonable to manually edit the edmx file, cancelled by "Update dabase from model". It's just unproductive, just a shame that's they did not consider this scenario as Guid is now very used.

      Lee Dumond said : Entity Framework designer is a representation of only the conceptual part of the data model.

      Yes it's right and we know many problem with this "conceptual" approach.

      For example, a database inheritance is not reported in the model (update model from database), you have to remove the association and the primary key in the "child table" and create the inheritance with the designer.

      Really great, there is many tricks like that in the entity framework. It's not very nice to realize and make you lost a lot of time.

      Thanks EF Team !

      Nicolas — December 7, 2010 1:38 AM
    23. Jan avatar

      What news about to add newid() as default value for UNIQUEIDENTITY when we generate database from the model of EF4? Should I wait for EF4 CTP5 RTM?

      Jan — December 21, 2010 6:06 PM
    24. GrayGoblin avatar

      Many thanks! I am writing my very first application with EF4 and MVC2 and decided to use a GUID as an identity key. How fortunate I was to find your article! I could have spent hours and hours trying to figure out why the field contained all zeroes.

      GrayGoblin — January 23, 2011 4:47 PM
    25. Andrew Heys avatar

      This *seems* to be still an issue, that true?

      Any tools out there to help?

      Thx

      - Andy

      Andrew Heys — January 26, 2011 2:52 AM
    26. Ben avatar

      Man, you just saved my life and, in addition, you taught me some notions I missed on EF4.

      Thanks !

      My case : I used DB auto-generated GUID for months and after updating my EDMX, GUID didn't work anymore.

      Ben — February 11, 2011 7:14 AM

    » Leave a Comment