CODEWITHSUNDEEP
c#linq-to-sqlexception
bflemi3
bflemi3

Reputation: 6790

How to ignore violation of primary key constraint in linq to sql

I have a table where both fields (idLocation, zipcode) are used as the primary key:

CREATE TABLE [dbo].[locationZipCode](
    [idLocation] [int] NOT NULL,
    [zipcode] [char](5) NOT NULL,
    [state] [char](2) NULL,
    CONSTRAINT [locationZipCode_pk_idLocation_zipcode] PRIMARY KEY CLUSTERED ([idLocation], [zipcode])
 )

In my app I'm calling:

context.InsertAllOnSubmit(entities);
context.SubmitChanges(ConflictMode.ContinueOnConflict);

And getting a duplicate key violation when duplicate records are attempted to be inserted. This is expected. If any records are duplicate then no records are inserted. Is there anyway to ignore duplicate key exceptions and continue inserting any records that don't violate primary key constraints?

I realize I can validate duplication before calling InsertAllOnSubmit() in my app but it would be ideal if I could just catch the error and just continue with the inserts.

Upvotes: 3

Views: 3254

Answers (1)

Anri
Anri

Reputation: 6265

For SQL Server IGNORE_DUP_KEY flag makes database engine ignore key violations. Nothing changes in DB if key exists.

 CREATE UNIQUE CLUSTERED INDEX [locationZipCode_idx] ON locationZipCode
 (
    [idLocation] ASC, [zipcode] ASC
 ) WITH ( IGNORE_DUP_KEY = ON )

Don't forget to remove current PK first.

Upvotes: 6

Related Questions