CODEWITHSUNDEEP
.netsql-serverentity-framework.net-4.0
mare
mare

Reputation: 13083

Using uniqueidentifier SQL column type with Entity framework

For my primary key and identity in our Clients table I use uniqueidentifier column with a default value set to newsequentialid(). Inserting new rows through the management tool creates the values for the ID fine.

Inserting from .NET 4 code via EF results into zero GUIDs (00000-0000....) being inserted. I create the entity with new, set some values and perform EF AddToClients (for instance). If debugged, value for the id property shows zero GUID. I do not explicitly set the id via the Guid.NewGuid() in my code because I want to leave it off to the newsequentialid() in SQL Server. The problem is, it doesn't work. The first time the code is executed row gets inserted with zero GUID. The next time it obviously fails and results in primary key violation exception.

How can I get this to work without having to set the GUID in my client code?

Upvotes: 5

Views: 12522

Answers (3)

Vinicius Sin
Vinicius Sin

Reputation: 1571

Right click in you .edmx. Open with XML text editor with encoding or another. Find the id name and add the attribute beside it in same tag: StoreGeneratedPattern="Identity".

Example:

< Property Name="WorkerId" Type="uniqueidentifier" Nullable="false" StoreGeneratedPattern="Identity" />

Upvotes: 0

Donnie Hale
Donnie Hale

Reputation: 411

I ran into this same issue. It's a real pain. Here's one page about it, w/ comments from Microsoft - http://social.msdn.microsoft.com/Forums/en/adonetefx/thread/aecf14e3-e4c9-439b-88af-23d4353afb9d.

My workaround was to create a stored procedure to use for the INSERT. This page has some info on how to do that - http://thedatafarm.com/LearnEntityFramework/tutorials/using-stored-procedures-for-insert-update-amp-delete-in-an-entity-data-model/. So far I only have an insert stored proc - nothing for update or delete; and contrary to the article, I'm not getting a model validation error.

CORRECTION EF let me get as far as retrieving a list of entities, but when I went to get an instance of a specific one, I got the error about missing a mapping function for updates. Ugh!

To retrieve the inserted UUID that was generated with newsequentialid(), I used the "OUTPUT INSERTED.[column]" technique that was introduced with SQL Server 2005. An example is at Retrieve last row inserted with Uniqueidentifier that it is not IDENTITY. I used the same technique in the INSERT stored procedure to retrieve datetime columns that were set with a default getdate().

Hope this helps someone,

Donnie

Upvotes: 0

Adam Robinson
Adam Robinson

Reputation: 185593

You need to change the StoreGeneratedPattern value for the property in the EF designer. Set it to Identity. This will cause EF to avoid setting the value on an insert, then capture it after the insert is complete.

Note that if you're using EF 4, you may have trouble with this using the designer only (see this link). You may have to edit the .edmx manually and set the StoreGeneratedPattern in the storage model itself.

Upvotes: 12

Related Questions