Entity Framework Ignore Duplicate Primary Key Values while inserting Multiple Rows at once

Question

I am using LINQ along with Entity-Framework to insert some data in a SQL Server 2012 Database.

My Database Table in which the data is being inserted has a primary key and i am inserting about 1000 Records at once.

That is i retrieve data in a set of 1000 rows and save those 1000 rows at one time for performance reasons.

Now the problem is i may occasionally get a duplicate value for any of the row in those 1000 rows and when that happens none of the rows are saved in the database.

Is there any way i can just silently ignore that one row and not insert it while all the other non duplicate rows get inserted?

Also i did try querying the database before every insert but the performance cost for that is too high.

Can't have duplicate primary keys in the Sql Table itself so nothing Entity Framework can do about this, the constraint should not be skirted around but respected, queue up the user action into a job that can be done in parallel while the user moves onto something else. Many ways to handle this without ignoring data integrity — Brian Ogden, Dec 22 '13 at 22:48

score 0 · Answer 1 · answered Dec 22 '13 at 23:28

Is there any way i can just silently ignore that one row and not insert it while all the other non duplicate rows get inserted?

If you can recreate the index in SQL Server you can ignore duplicates. After the insert recreate the index without ignore_dups because it's faster without.

CREATE TABLE [dbo].[YourTable](
  [id] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
  [id] ASC
)WITH (IGNORE_DUP_KEY = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Ryan Holsman · Answer 2 · 2013-12-22T23:57:38.943

0

I would suggest that you do a bit of extra processing before the insert statement, without any code to go off of I will try and show you with pseudo code.

ICollection<record> myRecords = Service.GetMyRecords();
//your processing logic before the insert
ICollection<record> recordsToInsert =Service.BusinessLogic(myRecords);

foreach(var record in recordsToInsert)
{
    if(myRecords.Contains(record )
    {
        recordsToInsert.Remove(record);
    }
}

This should ensure you have no records in the recordsToInsert Collection that will trip your DB. This also saves you an attempted insert statement since it doesn't try and fail.

edited Dec 22 '13 at 23:57

answered Dec 22 '13 at 23:32

Ryan Holsman

361
1
2
10

2

The problem is the table have 150 million + records and checking to see if any record exists is a time consuming process. – FatalTouch Dec 23 '13 at 00:23
Then I would suggest changing the settings on the DB to handle this properly as that seems to be the lowest runtime cost solution; user3123649 suggested a method to do that. – Ryan Holsman Dec 23 '13 at 16:46

score 0 · Answer 3 · answered Dec 24 '13 at 08:04

Do one query first that checks whether any of the new records exist like so:

var checks = records.Select(r => r.Id).ToArray();
if (!context.Records.Any(r => check.Contains(r.Id))
{
    // do the insert
}

After the first check you could refine the check to find out which of the 1000 records is the culprit. So the happy scenario will always be pretty quick. Only when a duplicate is found the process will be slower.

You can't tell EF to silently ignore one database exception while running one transaction.

Entity Framework Ignore Duplicate Primary Key Values while inserting Multiple Rows at once

3 Answers3