How to update on duplicate key in Entity Framework?

Question

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?

score 2 · Answer 1 · answered Apr 22 '12 at 19:06

2

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();

answered Apr 22 '12 at 19:06

Nico Schertler

32,049
4
39
70

1

`DbSet` [has no method called Clear](http://msdn.microsoft.com/en-us/library/gg679592%28v=vs.103%29.aspx). Also, isn't there a way to update it without querying for it first? MySQL has "on duplicate update"; not sure if MS SQL has something similar. – mpen Apr 22 '12 at 19:19
1

@Mark - this isn't MS SQL, this is Entity Framework. It's designed to work with many different Database providers, and as such often has to eschew useful features because they are not supported by all providers. There is no on duplicate update feature in EF. – Erik Funkenbusch Apr 22 '12 at 19:31

score 2 · Answer 2 · answered Apr 22 '12 at 22:59

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.

How to update on duplicate key in Entity Framework?

2 Answers2