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?
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.
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();
DbSet<T> has no method called Clear. 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.