C# Entity Framework Bulk Update

Question

What is the best way to update multiple records in a list to speed up processing?

Currently, I'm updating about 15000 products, each with 3 different price sets and it takes the whole day to complete.

I need to update the prices all at once in code side, then commit those changes to the database in 1 go, instead of fetching each inventory item, updating its values, then attaching it to the context. Every single fetch is causing the delays.

Code

public void UpdatePricesFromInventoryList(IList<Domain.Tables.Inventory> invList)
{
    var db = new UniStockContext();

    foreach (var inventory in invList)
    {
        Domain.Tables.Inventory _inventory = db.Inventories
                                            .Where(x => x.InventoryID == inventory.InventoryID)
                                            .FirstOrDefault();

        if (inventory.Cost.HasValue)
            _inventory.Cost = inventory.Cost.Value;
        else
            _inventory.Cost = 0;

        foreach (var inventoryPrices in inventory.AccInventoryPrices)
        {
            foreach (var _inventoryPrices in _inventory.AccInventoryPrices)
            {
                if (_inventoryPrices.AccInventoryPriceID == inventoryPrices.AccInventoryPriceID)
                {
                    _inventoryPrices.ApplyDiscount = inventoryPrices.ApplyDiscount;
                    _inventoryPrices.ApplyMarkup = inventoryPrices.ApplyMarkup;
                    if (inventoryPrices.Price.HasValue)
                        _inventoryPrices.Price = inventoryPrices.Price.Value;
                    else
                        _inventoryPrices.Price = _inventory.Cost;

                    if (inventoryPrices.OldPrice.HasValue)
                    {
                        _inventoryPrices.OldPrice = inventoryPrices.OldPrice;
                    }
                }
            }
        }

        db.Inventories.Attach(_inventory);
        db.Entry(_inventory).State = System.Data.Entity.EntityState.Modified;
    }

    db.SaveChanges();
    db.Dispose();
}

I've also tried working my code according to this SOQ Entity Framework update/insert multiple entities and it gave me and error. Here are the details:

Code:

    public void UpdatePricesFromInventoryListBulk(IList<Domain.Tables.Inventory> invList)
    {
        var accounts = new List<Domain.Tables.Inventory>();
        var db = new UniStockContext();
        db.Configuration.AutoDetectChangesEnabled = false;

        foreach (var inventory in invList)
        {
            accounts.Add(inventory);
            if (accounts.Count % 1000 == 0)
            {
                db.Set<Domain.Tables.Inventory>().AddRange(accounts);
                accounts = new List<Domain.Tables.Inventory>();
                db.ChangeTracker.DetectChanges();
                db.SaveChanges();
                db.Dispose();
                db = new UniStockContext();
            }
        }

        db.Set<Domain.Tables.Inventory>().AddRange(accounts);
        db.ChangeTracker.DetectChanges();
        db.SaveChanges();
        db.Dispose();
    }

Error:

An entity object cannot be referenced by multiple instances of IEntityChangeTracker.

Possible duplicate of [Entity Framework update/insert multiple entities](https://stackoverflow.com/questions/39656794/entity-framework-update-insert-multiple-entities) — CodeNotFound, Jun 24 '18 at 10:13
Thanks for pointing me in the right direction. I'm trying that code now. It did not come up in my searching before :/ — Orion, Jun 24 '18 at 10:28
@CodeNotFound that solution is not complete. I tried it now and it gave me an error. Will update my question with that code trial as well. — Orion, Jun 24 '18 at 10:32

score 1 · Accepted Answer · answered Jun 24 '18 at 10:44

I would suggest changing the following:

    Domain.Tables.Inventory _inventory = db.Inventories
                                    .Where(x => x.InventoryID == inventory.InventoryID)
                                    .FirstOrDefault();

To

Domain.Tables.Inventory _inventory = db.Inventories
                                    .Single(x => x.InventoryID == inventory.InventoryID);

I'd still add the db.Configuration.AutoDetectChangesEnabled = false; after getting the context, and also use AsNoTracking: Turn off EF change tracking for any instance of the context

This with worked for me. Save me a lot of processing time. Thanks. — Orion, Jun 24 '18 at 11:01

score -1 · Answer 2 · answered Jun 24 '18 at 11:05

that is because you are hit the database context at every loop to increase the performance you should get all the Inventories by one hit ,this is your problem try the below code and you will notice the performance :

    public void UpdatePricesFromInventoryList(IList<Domain.Tables.Inventory> invList)
{
    var db = new UniStockContext();
    invIdsArray = invList.select(x => x.InventoryID).ToArray();
    IList<Domain.Tables.Inventory>  invListFromDbByOneHit = db.Inventories.Where(x => invIdsArray.Contains(x.InventoryID)).Tolist();
    foreach (var inventory in invListFromDbByOneHit)
    {
        //Domain.Tables.Inventory _inventory = db.Inventories
                                            //.Where(x => x.InventoryID == inventory.InventoryID)
                                            //.FirstOrDefault();

        if (inventory.Cost.HasValue)
            _inventory.Cost = inventory.Cost.Value;
        else
            _inventory.Cost = 0;

        foreach (var inventoryPrices in inventory.AccInventoryPrices)
        {
            foreach (var _inventoryPrices in _inventory.AccInventoryPrices)
            {
                if (_inventoryPrices.AccInventoryPriceID == inventoryPrices.AccInventoryPriceID)
                {
                    _inventoryPrices.ApplyDiscount = inventoryPrices.ApplyDiscount;
                    _inventoryPrices.ApplyMarkup = inventoryPrices.ApplyMarkup;
                    if (inventoryPrices.Price.HasValue)
                        _inventoryPrices.Price = inventoryPrices.Price.Value;
                    else
                        _inventoryPrices.Price = _inventory.Cost;

                    if (inventoryPrices.OldPrice.HasValue)
                    {
                        _inventoryPrices.OldPrice = inventoryPrices.OldPrice;
                    }
                }
            }
        }

        db.Inventories.Attach(_inventory);
        db.Entry(_inventory).State = System.Data.Entity.EntityState.Modified;
    }

    db.SaveChanges();
    db.Dispose();
}

C# Entity Framework Bulk Update

2 Answers2