Question
I'm adding multiple new rows in a loop to the database and then saving changes. But I have a unique index accross 3 columns, so I need to check that they don't exist first to prevent exception.
The problem is, rows I add to the context are not found until after SaveChanges(), and so the duplicate check doesn't work. And so after adding all the rows, when I do SaveChanges(), I get a duplicate exception.
foreach (var row in json["data"]["animals"])
{
var newRow = new Animal
{
Prop1 = row["prop1"].ToString(),
Prop2 = row["prop2"].ToString(),
Prop3 = row["prop3"].ToString(),
}
if (db.Animals.Where(p => p.Prop1 == newRow.Prop1 && p.Prop2 == newRow.Prop2 && p.Prop3 == newRow.Prop3).FirstOrDefault() == null)
{
db.Animals.Add(newRow);
// db.SaveChanges(); // If I put this here instead, it works (no duplicate exception)
}
}
db.SaveChanges(); // This throws the duplicate exception
Do I have to save the changes after adding every row? Or am I missing a better way to do this, because this seems inefficient/not right.
Accepted Answer
One option without pre-filtering the json would be to add the new rows to a new collection, and check for duplicates in that at the same time as in the db, like this:
var added = new List<Animal>();
foreach (var row in json["data"]["animals"])
{
var newRow = new Animal
{
Prop1 = row["prop1"].ToString(),
Prop2 = row["prop2"].ToString(),
Prop3 = row["prop3"].ToString(),
}
if (db.Animals.Where(p => p.Prop1 == newRow.Prop1 && p.Prop2 == newRow.Prop2 && p.Prop3 == newRow.Prop3).FirstOrDefault() != null)
{
continue;
}
if (added.Where(p => p.Prop1 == newRow.Prop1 && p.Prop2 == newRow.Prop2 && p.Prop3 == newRow.Prop3).FirstOrDefault() != null)
{
continue;
}
added.Add(newRow);
db.Animals.Add(newRow);
}
db.SaveChanges();
Popular Answer
That is because yo have duplicate values in your json data animals, first clean duplicate rows in your list:
// Linq is required
// using System.Linq;
// Map your json to a animal queryable;
var animals = json["data"]["animals"].Select(row => new Animal {
Prop1 = row["prop1"].ToString(),
Prop2 = row["prop2"].ToString(),
Prop3 = row["prop3"].ToString()
});
// Select only unique values
animals = animals.GroupBy(a=>new { a.Prop1, a.Prop2, a.Prop3 }).Select(a => a.First());
// Your logic to insert
foreach (var newRow in animals)
{
if (!db.Animals.Any(p => p.Prop1 == newRow.Prop1 && p.Prop2 == newRow.Prop2 && p.Prop3 == newRow.Prop3))
{
db.Animals.Add(newRow);
// db.SaveChanges(); // If I put this here instead, it works (no duplicate exception)
}
}
db.SaveChanges(); // This throws the duplicate exception
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow