Entity Framework Core add multiple rows with Unique check

Question

Entity Framework Core add multiple rows with Unique check

asp.net-core c# entity-framework-core

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.

Andrew

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

hiral2

View more on Stack Overflow

Andrew · Accepted Answer · 11/3/2019 6:02:19 PM

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

Entity Framework Core add multiple rows with Unique check

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related