Why is LINQ to SQL inserting duplicates into my table?

Question

I have a web service that retrieves reviews from my database and if not enough reviews are returned, it goes to a secondary source and gets them there and caches them in my database. I have recently noticed that it has been creating duplicates in my database despite me having primary keys to prevent it. I was thinking that maybe when I made the table I did something wrong? Here is the SQL create table snippet:

create table Review(  
ReviewID int IDENTITY,  
CacheDate DateTime,  
UniqueID nvarchar(50),  
Type nvarchar(100),  
AverageRating decimal,  
TotalReviews decimal,  
Rating decimal,  
Content nvarchar(max),  
Summary varchar(100),  
HelpfulVotes int,  
TotalVotes int,  
ReviewDate nvarchar(100),  
ReviewerID nvarchar(100),  
ReviewerName nvarchar(100),  
ReviewerLocation nvarchar(100),  
primary key(ReviewID,UniqueID) 
)

I have stepped through my code and when it gets to this to the try catch section it still inserts the row, even if it already exists..

try { db.SubmitChanges(); }  
catch (Exception ex) {.....

Does anyone know what the problem could be?

Answer 1

Your primay key is based off of both the ReviewID and UniqueID columns. So the combination of both columns must be unique.

Do your duplicates have a matching ReviewID and UniqueID?

Answer 2

Any particular reason you put the PK over both the UniqueID and the Identity column (ReviewID)? Why not, as an alternative, put a unique constraint on UniqueID and the PK just on ReviewID?