Best Practice Checking for duplicate rows before inserting list of items

Question

I have a an array of objects that I want to enter into the database. My method call looks like this.

public void Add(CardElement[] cardElements){
    foreach (var cardElement in cardElements)
    {
        Data.Entry(cardElement).State = System.Data.EntityState.Added;
    }
    Data.SaveChanges();
}

The database table resembles this

MS SQL = Table mytable Columns a,b,c,d,e,f
Unique Constraint a,b,c

The data I want to insert resembles this.

var obj []  = new [] { 
   new MyObject () { a = 1, b =1, c = 1 },
   new MyObject () { a = 1, b =1, c = 2 }
   new MyObject () { a = 1, b =1, c = 3 }
 };

So, I want to check the database for these three rows before I add them to the database.

I could do something like but I assume this should cause some extra trips to the database.

private bool checkExists()...

foreach (var cardElement in cardElements)
{
    var exists = (from ce in Data.CardElements
                    where ce.CardId == cardElement.CardId
                    where ce.Area == cardElement.Area
                    where ce.ElementName == cardElement.ElementName
                    select ce).Any();
    if(exists return true)
}
return false

So, how could I handle this more gracefully?
Is it even worth trying to accomplish this using linq?
Should I write some stored procedures for performance?

score 1 · Accepted Answer · edited May 23 '17 at 12:04

1

I agree that you should let the db make the decision. Please have a look at using UPSERT as stated in this post

edited May 23 '17 at 12:04

Community

1
1

answered Jun 01 '12 at 09:36

rretzbach

744
1
6
16

score 0 · Answer 2 · answered Jun 01 '12 at 07:42

0

Why not just attempt the insert and let the database tell you if any unique constraint violations have occurred (using try/catch)?

answered Jun 01 '12 at 07:42

devuxer

41,681
47
180
292

That is what I am doing. It just didn't seem right to rely on exception handling. – jwize Jun 01 '12 at 09:41

score 0 · Answer 3 · answered Jun 01 '12 at 08:38

The problem is that even if you query data somebody else can insert the record between your query and saving changes. You will still have to handle exception for violating unique constraint despite your additional queries - and yes, every check will do additional trip to database.

If your main concern is performance use stored procedure where you can additionally use table hint to lock table for inserts during initial check for existence.

Best Practice Checking for duplicate rows before inserting list of items

3 Answers3