none
EF model and GUID RRS feed

  • Question

  • It's the first time when I'm using the GUID field type and I got an unexpected behavior with an EF model.

     

    I have a table with the PK a GUID field, with the default set to newsequentialID().

     

    I created the EF model for this and I try to add a new entity (record in table). I don't set explicity any value to the primary key property / field.

     

    Only the first Add works, all the other fail with 'primary key violation' exception on the SQL server.

     

    It found out that the EF model always sets the same 'initial' value to the GUID field (a 00000-000...). If I do the INSERT directly in the database without a value for the GUID field, it uses the default I set, as expected.

     

    Why EF don't let the SQL to handle the empty value?

     

    Do I do something wrong? How should I set the GUID property to let the SQL server to create a new value or, at least, setting a new GUID value on the client?

     

    Thanks,

    Ioan

     

    Thursday, February 7, 2008 4:28 PM

Answers

  • Arrrggg...  I forgot about this limitation.  It turns out that there's no reliable way to get back a guid value after an insert if you use sql server 2000, and we don't currently distinguish between sql server 2000, 2005, 2008, etc. in the sql server provider where it generates code for this scenario.  So only identity columns are allowed with storegeneratedpattern="identity" on sql server (as the exception message tells you).

     

    In your case, though, the good news is that generating the guids on the client is basically safe.  So you can just add a constructor for your entity in the partial class and in that constructor assign a newly generated guid to the property.  This isn't as nice as having the server generate the value for you, but it should work.

     

    - Danny

    Thursday, February 7, 2008 4:58 PM
  • This won't change in v1 because the architecture changes to distinguish between the various versions of sql server and such are significant.  This will never work on sql 2000, but could theoretically work against newer versions of sql server.  In a release of the EF after v1, we might add support for this with sql 2005 and later.

     

    - Danny

     

    Thursday, February 7, 2008 5:14 PM

All replies

  • In your SSDL file (or the SSDL section of your EDMX file), you need to add an attribute to the property that is your GUID that looks like this:

     

    StoreGeneratedPattern="Identity"

     

    This tells the system that the database will generate a value for that field when new records are inserted and then the value will stay the same.  So new records will be written to the DB without including that field, and further, after the save, the value generated in the database will be propagated back into the property on your object and the EntityKey (and any references to it) will be fixed up.

     

    - Danny

     

    Thursday, February 7, 2008 4:34 PM
  •  

    I added the attribute and I got the (inner) exception:

     

    {"Server generated keys are only supported for identity columns. Key column 'pkFacility' has type 'SqlServer.uniqueidentifier' which is not a valid type for an identity column."}

     

    Thursday, February 7, 2008 4:43 PM
  • Arrrggg...  I forgot about this limitation.  It turns out that there's no reliable way to get back a guid value after an insert if you use sql server 2000, and we don't currently distinguish between sql server 2000, 2005, 2008, etc. in the sql server provider where it generates code for this scenario.  So only identity columns are allowed with storegeneratedpattern="identity" on sql server (as the exception message tells you).

     

    In your case, though, the good news is that generating the guids on the client is basically safe.  So you can just add a constructor for your entity in the partial class and in that constructor assign a newly generated guid to the property.  This isn't as nice as having the server generate the value for you, but it should work.

     

    - Danny

    Thursday, February 7, 2008 4:58 PM
  • OK. (I mean is not OK, but I'll see Sad )

     

    I there any chance that in a future version this will be 'fixed'?

     

    Thanks,

    Ioan

     

     

     

     

    Thursday, February 7, 2008 5:11 PM
  • This won't change in v1 because the architecture changes to distinguish between the various versions of sql server and such are significant.  This will never work on sql 2000, but could theoretically work against newer versions of sql server.  In a release of the EF after v1, we might add support for this with sql 2005 and later.

     

    - Danny

     

    Thursday, February 7, 2008 5:14 PM
  •  

    Could we use stored procedure to get the id and return to the entity modal to solve this issue?

    Tuesday, April 22, 2008 10:17 AM
  • Well, there are things that you can do, but they won't be great.  Probably the best suggestion I would have is the following:

     

    Use system.transactions to create your own explicit transaction.  Within that transaction scope call SaveChanges(false) which will save changes to the database but not mark all the local state entries as "accepted".  Then call your stored procedure to get back the ID.  Set the value you get back onto the entity's property.  Then commit the transaction.  Assuming the commit succeeds, you can then call AcceptAllChanges to update the state manager to reflect that the changes have been committed to the DB and to propagate the new ID to any dependent entities which have referential integrity constraints, etc.

     

    This is roughly what happens under the covers when you use an identity column except that in that case the system doesn't need multiple roundtrips to the DB.

     

    - Danny

     

    Monday, April 28, 2008 5:03 PM
  •  

    For GUIDs/DateTime and other non-Identity computed fields you need to change your SSDL to use  -StoreGeneratedPattern ="Computed"

     that will allow the server to generate the value. For a GUID it's OK to generate on the client, but timeStamps should only be generated on the server.

    Monday, October 20, 2008 5:59 PM
  • Danny,

    Is there anything done in the next version to support returing guids after inserting for the supported databases such as sql server 2005 or 2008?

    Zeeshan Hirani
    Friday, May 8, 2009 5:34 AM
  • yes, the issue has been fixed in .Net 4.0 so that server generated GUID values are propogated back for Sql 2005 and up. The fix should be available in Beta 2( and RC).

    Thanks
    Srikanth
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, February 16, 2010 8:13 AM