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

yurexus · Accepted Answer · 2019-07-24 17:33:52Z

2

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:

First entity is added
An attempt to save changes, but it is failed due to constraint violation
Second entity is added
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.

answered Jul 24, 2019 at 17:33

yurexus

3231 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael Freidgeim Over a year ago

Note that If persist multiple records in one shot, they will be in one transaction, and, if one record failed, all records will be rejected. You may want or don’t want it.

Collectives™ on Stack Overflow

c# entity framework loop through records savechanges() error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related