.NET 4 EF4 insertint Unique Identifier into SQL Server table gives duplicates

Question

Hi I have an SQL Server table as below

CREATE TABLE [dbo].[Location](
    [LocationUID] [uniqueidentifier] NOT NULL,
    [Code] [char](10) NULL,
    [Type] [char](1) NOT NULL,
    [FullName] [nvarchar](150) NULL,
 CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED 
(
    [LocationUID] ASC
)
ALTER TABLE [dbo].[Location] ADD  CONSTRAINT [DF_Location_LocationUID]  DEFAULT (newid()) FOR [LocationUID]

I have a C# WCF service in .NET4 using EF4 to insert into the Table location. This is the code that does it

  //create country
                country = new Location()
                {
                    FullName = CountryName,
                    Type = "C",
                    Diaretics = CountryName,
                    Code = CountryCode
                };
                dbContext.AddToLocations(country);
                dbContext.SaveChanges();

But when I run the service the code falls over at dbContext.SaveChanges() with Error

Violation of PRIMARY KEY constraint 'PK_Location'. Cannot insert duplicate key in object 'dbo.Location'. The statement has been terminated.

I thought this is impossible to happen with the way the default value is set on LocationUID field with NewUID() and the configuration of primary key.

Any help would be appreciated

score 1 · Accepted Answer · edited May 23 '17 at 11:55

1

Set the StoreGeneratedPattern to Identity on the GUID PK property.

Ref:

edited May 23 '17 at 11:55

Community

1
1

answered Jul 31 '11 at 05:30

Mitch Wheat

295,962
43
465
541

Looks like it is supported now, but reading it, seems like a hacky work-around, rather than proper support. (in my opinion) – Phill Jul 31 '11 at 05:42
Hi Changing StoreGeneratedpattern to Identity in EF designer does not fix the problem.So the Edmx file needs to be edited as an XML file and making change in section as fixes the problem it seems – josephj1989 Jul 31 '11 at 06:07

score 0 · Answer 2 · answered Jul 31 '11 at 05:27

I'm going to assume that it's similar to the issues with Guid's as identities in L2S and EF1, in that, it just doesn't work well at all. Mainly in the way it has to select the identity back after insert.

There's no way for EF to do a SCOPE_IDENTITY() to get the GUID back from the db, it has to select based on all fields.

The solution in L2S was to create the GUID's in code.

I think if you check the DB it will be trying to insert an empty GUID. And since an empty one already exists you are getting that exception.

(I don't know if this has all changed in EF4)

.NET 4 EF4 insertint Unique Identifier into SQL Server table gives duplicates

2 Answers2