CODEWITHSUNDEEP
linqentity-frameworkinsertunique-constraint
jwize
jwize

Reputation: 4165

Best Practice Checking for duplicate rows before inserting list of items

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
  1. So, how could I handle this more gracefully?
  2. Is it even worth trying to accomplish this using linq?
  3. Should I write some stored procedures for performance?

Upvotes: 1

Views: 1812

Answers (3)

rretzbach
rretzbach

Reputation: 744

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

Upvotes: 1

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364359

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.

Upvotes: 0

devuxer
devuxer

Reputation: 42374

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

Upvotes: 0

Related Questions