CODEWITHSUNDEEP
c#entity-frameworksqlite
RvdK
RvdK

Reputation: 19800

Insert data into DbContext saveChanges exception

I'm trying to add a bulk of data in my DbContext (SQLite DB). What I want is to add x amount of data, and if some of them already exist, ignore those. So for example: add 100, 2 already exist, 98 items are added.

First I did a insert of all of them and then call saveChanges(), but that reverted the insert of all of them. Therefore I changed it to saveChanges() in the for-loop but I'm experiencing the same problem.

Do I need to clear something after an exception?

public int Insert(List<Car> Cars)
{            
    int addedCars = 0;
    foreach (Car t in Cars)
    {
        _db.Cars.Add(t);
        try
        {
            _db.SaveChanges();
            addedCars++;
        }
        catch (DbUpdateException ex)
        {
            Console.WriteLine("Whups duplicate entry");
        }
    }
    return addedCars;
}

Upvotes: 0

Views: 8051

Answers (2)

Michal Klouda
Michal Klouda

Reputation: 14521

Can't you just test if the item already exists before adding it?

if(!_db.Cars.Any(c => c == t))
{
   _db.Cars.Add(t);
}

 

Upvotes: 3

htxryan
htxryan

Reputation: 2961

You would need to remove it from the context. You're receiving an exception, but the entity is still "added" to the context. So the next time you SaveChanges(), you'll get the same exception.

Option 1:

public int Insert(List<Car> Cars)
{            
    int addedCars = 0;
    foreach (Car t in Cars)
    {
        _db.Cars.Add(t);
        try
        {
            _db.SaveChanges();
            addedCars++;
        }
        catch (DbUpdateException ex)
        {
            _db.cars.Remove(t); // Remove it from the context collection
            Console.WriteLine("Whups duplicate entry");
        }
    }
    return addedCars;
}

Option 2 (preferred):

I really suggest you not even attempt to add the entities if they already exist.

You could do this before your add logic:

Cars = Cars.Where(x => !_db.Cars.Any(y => y.ID == x.ID)).ToList();

Note that even though this technically "checks" 100 records, it is doing so in memory with a single SQL statement. This is much faster than inserting even a few records erroneously.

Upvotes: 4

Related Questions