c# entity framework loop through records savechanges() error

Question

I am doing an C# Entity Framework Application.

I am migrating data from a couple of tables from one database to another database. Those tables are called with the same name.

Here is my code

string ConnexionOrigen = ReadConnectionBiz.GetOrigen();
string ConnexionDestino = ReadConnectionBiz.GetDestino();
using (var db = new MigrateModel(ConnexionOrigen))
{
  db.Configuration.LazyLoadingEnabled = false;
  db.Configuration.ProxyCreationEnabled = false;
  db.Configuration.AutoDetectChangesEnabled = false;
  List<Promocion> promo = await (from p in db.Promocions
                               .Include(a =>a.PromocionAddresIds)
                         where (p.CAM_ID == 107936 || p.CAM_ID == 107937)
                        select p)
                        .AsNoTracking()
                         .ToListAsync();

  var dbDestino = new MigrateModel(ConnexionDestino);
  foreach (Promocion pp in promo)
  {
    try
    {
      dbDestino.Promocions.Add(pp);
      await dbDestino.SaveChangesAsync();
    }
    catch (Exception ex)
    {
    string err =  ex.InnerException.InnerException.Message.ToString();
    }
 }

What it does here is:

I search in Origen Database from table Promocions and insert those records in Promotions table in Destino Database .

I include a child table called PromocionAddresIds that is migrated too.

Everythig works fine...

The table PromocionAddresIds has a constraint with a table that it is not been migrated.

When I insert the Promotions with cam_id=107936 it thrown an exception of contraint violation... Witch is correct...

But when it read the next Promotions record with Cam_id =107937. That record has no record in table PromocionAddresIds, so it must be inserted, but it thrown the same error exception...

It appears that await dbDestino.SaveChangesAsync(); still have the all record or it persist somewhere...

I have tried adding

dbDestino.Promocions.Remove(pp); inside the catch exception, but it did not work.

I do not have any clue how to solve it.

Any ideas?

Thanks

Answer 1

You created a single instance of data context for all iterations of the loop. All objects added to the context will live there until the context is disposed or cleared. The situation you observe is:

1. First entity is added
2. An attempt to save changes, but it is failed due to constraint violation
3. Second entity is added
4. Another attempt to save changes. And here both first and second entity will be attempted to be inserted, because they still belong to the data context.

Please also note that calling "save changes" after each entity usually is not efficient. It is better to insert a portion of entities (let's say 100) and persist it in one shot.