CODEWITHSUNDEEP
c#entity-framework
mpen
mpen

Reputation: 282875

How to update on duplicate key in Entity Framework?

I've essentially got a loop that looks like this:

foreach(var file in files)
{
    // ...
    db.Images.Add(new ImageFingerPrint { FileName = file, FingerPrint = fingerprint });
}

int recordsAffected = db.SaveChanges();

FileName is the PK. If it already exists in the DB, I just want it to update its FingerPrint instead. Essentially doing a REPLACE I suppose.

And if that's not easy/possible, how do I simply truncate the entire table?

Upvotes: 0

Views: 5282

Answers (2)

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28718

The usual pattern that I use is

foreach(var file in files)
{
    var image = db.Images.SingleOrDefault(i => i.FileName == file);
    if (item == null)
    {
        image = new ImageFingerPrint() { FileName = file };
        db.Images.Add(image);
    }

    image.FingerPrint = fingerprint;
}    
db.SaveChanges();

This is made easier when the object has some database-generated field, like an IDENTITY primary key, a CreatedDate column, or a Timestamp. It is then simple to check whether these fields are default, and if they are the object is new and it should be "added".

Depending on the complexity of your loop you may want to determine the 'existing' and the 'new' filenames upfront -

var existing = files.Where(f => db.Images.Contains(f)); 
// & use .ToList() if you're going to be iterating the collection multiple times

foreach (file in existing)
{
    // updates
}

foreach (var file in files.Except(existing))
{
    // inserts
}

I use this approach when there are performance benefits - e.g. one of the operations can be done as a bulk operation and the other can't. Otherwise it can make code clearer if the insert and update operations are significantly different.

Upvotes: 2

Nico Schertler
Nico Schertler

Reputation: 32597

You can truncate the table with 

db.Images.Clear();
db.SaveChanges();

Assuming that there are no foreign key constraints.

If you want to get an existing item, try a LINQ query:

var existing = (from im in db.Images
               where im.FileName.Equals(file)
               select im).SingleOrDefault();
if(existing != null)
    existing.FingerPrint = fingerprint;
else
    db.Images.Add(...)
db.SaveChanges();

Upvotes: 1

Related Questions