How to Bulk Update records in Entity Framework?

Question

I am trying to bulk update records using Entity Framework. I have tried Entity Framework.Extensions Update method.

The Update method is able to bulk update for a set of records with same set of update values.

Example:

           Id -  Quantity
Record 1 - A  -  10
Record 2 - B  -  20
Record 3 - C  -  30

We can bulk update all the above records by simple calling

Records.Update(new => Record { Quantity = 100 });

How can I bulk update each record with different quantity using Entityframework.Extensions or in any other approach, which completes the bulk update faster?

Wriest

Update each record with individual data means one statement for each record. The fastest way is to execute all that statements within a single transaction, but it will take some time. How do you do the update process for now? – Noam

currently we are using entity framework , looping through each records update the db entity properties and then save changes, this process will be executed for all the 50,000 records which is taking lot more time. – Wriest

The question then is where are the values coming from for the update? Are they from another table? Or are they a function of the original value (e.g. 2x)? Can every row have a different value or can you update groups of rows using EF-extended? – Jellyfish

The records are coming after the UI has has processed data and sent to service for an update, yes every row can have different value. tried EF-Extended but i was able to update group of records with same value but not each records with different values in Bulk format. – Wriest

If the values are coming from the UI then your entities are unattached. So essentially every record will get selected and then updated which will definitely slow things down considerably. The answer from @GrégoryBourgin might be able to solve this without bringing in another library. Since each row needs a different value you will need individual update statements - but if you call SaveChanges after modifying each manually attached record then you should get a batch update and no roundtripping of the data. – Epigraph

Thanku @RobertPetz the answer from GrégoryBourgin helped me solve the problem 50%. – Wriest

With unique values per record, real bulk update, i.e. updating by one statement can only be done by generating a SQL UPDATE statement that joins with a (temp)table containing key-value pairs that is populated from client-side values. – Coward

Answer 1

If you don't want to use an SQL statement, you can use the Attach method in order to update an entity without having to load it first :

using (myDbEntities db = new myDbEntities())
{
    try
    {
      //disable detection of changes to improve performance
      db.Configuration.AutoDetectChangesEnabled = false;

      //for all the entities to update...
      MyObjectEntity entityToUpdate = new MyObjectEntity() {Id=123, Quantity=100};
      db.MyObjectEntity.Attach(entityToUpdate);

      //then perform the update
      db.SaveChanges();
    }
    finally
    {
      //re-enable detection of changes
      db.Configuration.AutoDetectChangesEnabled = true;
    }
}

Catto

Attaching the entity is not enough to trigger the update, as it's attached as Unchanged by default. You need to call db.Entry(entityToUpdate).Property(e => e.Quantity).IsModified = true, after the call to Attach. And in EF Core you can omit the Attach, as the call to Entry will attach it. – Myrwyn

hi @DavidBrowne-Microsoft , can you answer this question? Bulk update with Join and where, #63479057 – Airframe

also @DavidBrowne-Microsoft also, I placed your important comments into the answer – Airframe

So using this .Attach() or .Entry() for EF Core for multiple records and doing SaveChanges() results in one Update command to the database? – Impart

@ΕГИІИО I'm guessing that's not correct, that it still does a statement per entitiy. Would be nice for someone who knows to give confirmation though. – Museum

This generates one update statement per record. That's not bulk update. – Coward

Answer 2

Use ExecuteSqlCommand:

using (yourDbEntities db = new yourDbEntities())
{
    db.Database.ExecuteSqlCommand("UPDATE YourTABLE SET Quantity = {0} WHERE Id = {1}", quantity, id);
}

Or ExecuteStoreCommand:

yourDbContext.ExecuteStoreCommand("UPDATE YourTABLE SET Quantity = {0} WHERE Id = {1}", quantity, id);

Terpstra

This is the best answer. The only reason it's not the most highly voted is because it doesn't "feel" right for the true believers who see this as a "hack". EF is supposed to make life easier; when it doesn't then ditch it and do what works. – Museum

Answer 3

There are the built-in ExecuteUpdate and ExecuteDelete methods in EFCore which have been delivered by EFCore 7.0

context.Customers.Where(...).ExecuteDeleteAsync();
context.Customers.Where(...).ExecuteUpdateAsync(c => new Customer { Age = c.Age + 1 });
context.Customers.Where(...).ExecuteUpdateAsync(c => new { Age = c.Age + 1 });

Windsor

Not exacly as described but something is on the topic learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/… ExecuteUpdateAsync – Bord

Majid - can you run ExecuteUpdateAsync 500+ times without a severe degradation in performance of you DB? – Saltire

Answer 4

Use this way if you just want to modify few properties:

foreach (var vSelectedDok in doks)
{
    //disable detection of changes to improve performance
    vDal.Configuration.AutoDetectChangesEnabled = false;
    
    vDal.Dokumente.Attach(vSelectedDok);

    vDal.Entry(vSelectedDok).Property(x=>x.Status).IsModified=true;
    vDal.Entry(vSelectedDok).Property(x => x.LastDateChanged).IsModified = true;
}
vDal.SaveChanges();

Tippet

This generates one update statement per record. That's not bulk update. – Coward

Answer 5

a) EFCore.BulkExtensions - BatchUpdateAsync

_dbContext.Set<MyObjectEntity>().BatchUpdateAsync( x => new MyObjectEntity{ Id=123, Quantity=100 });

https://github.com/borisdj/EFCore.BulkExtensions

"EntityFrameworkCore extensions: Bulk operations (Insert, Update, Delete, Read, Upsert, Sync) and Batch (Delete, Update). Library is Lightweight and very Efficient, having all mostly used CRUD operation. Was selected in top 20 EF Core Extensions recommended by Microsoft."

b) Or EF Extensions - UpdateFromQuery

_dbContext.Set<MyObjectEntity>().UpdateFromQuery( x => new MyObjectEntity{ Id=123, Quantity=100 });

Resource:

https://entityframework-extensions.net/update-from-query

https://mcmap.net/q/428959/-c-entity-framework-bulk-extensions-input-memory-issue

Why UpdateFromQuery is faster than SaveChanges, BulkSaveChanges, and BulkUpdate?

UpdateFromQuery executes a statement directly in SQL such as UPDATE [TableName] SET [SetColumnsAndValues] WHERE [Key].

Other operations normally require one or multiple database round-trips which makes the performance slower.

Airframe

Answer 6

I found an easy way to do that without any 3rd party packages:
By adding one generic extension method SetValue you can simply write:

Example:

void Main()
{
    
    var dc = this; // context
    var p = dc.Purchases.Where(x=>x.Description.ToLower()=="bike")
                        .SetValue(w => w.Description = "Bicycle");
    p.Dump();
    dc.SubmitChanges();
}

As you can see, any value matching the Where condition can be set explicitly to a new value, so here Bike will be replaced by Bicycle. You can query the table afterwards to see the changes really persisted.

Of course, you could also omit the Where statement, if you want to change all records like:

dc.Records.SetValue(x => x.Quantity = 100);
dc.SubmitChanges();

Entity framework (EF) / LINQ tracks those changes and when you call .SubmitChanges() - as you can see in the SQL tab if you're using LinqPad - it will create SQL code as follows:

-- Region Parameters
DECLARE @p0 Int = 3
DECLARE @p1 VarChar(1000) = 'Bicycle'
-- EndRegion
UPDATE [Purchase]
SET [Description] = @p1
WHERE [ID] = @p0

For small changes, this is ok, but for large tables it is becoming inefficient, because it uses the ID column to identify and change a record, and not the Description column as defined by .SetValue.

Theoretically EF could optimize this, but as you can see, it doesn't do it. So if you want true bulk operations you need to run a SQL command instead or create a stored procedure (for complex queries) which you're calling via EF.

---

Extension method SetValue

This extension method does the trick (no other 3rd party packages required):

// see: https://visualstudiomagazine.com/articles/2019/07/01/updating-linq.aspx
public static class Extensions
{
    public static IEnumerable<T> SetValue<T>(this IEnumerable<T> items, 
                                                  Action<T> updateMethod)
    {
        foreach (T item in items)
        {
            updateMethod(item);
        }
        return items;
    }
}

---

Note: The example above uses the Nutshell example database, which you can easily create by following this link and the code is written for LinqPad 6 but can be adapted easily (LinqPad 6 uses .NET Core, but you can try it with LinqPad 5 as well for the .NET Framework).

Barbabra

This is a batch update, not a bulk update, right? Meaning it loads every record, alters it, and then saves? – Splenetic

@Splenetic - Strictly speaking, yes. In many cases, this will do its job fine. But if you have extensively large tables to update (as described here), think about creating a stored procedure in SQL and call that from entity framework instead - or, if it is just a single UPDATE statement, execute a SQL command instead (ExecuteSqlCommand). – Barbabra

Answer 7

In EF 6 we have AddRange method in each table. Documents suggest this method is much faster than using many add methods. So, one can insert all updatable records in a temp table and batch update main table using a single sql statement.

EDIT: This Document suggests that AddRange only optimizes change detection. It does not change how the changes are applied to the database.

Tananarive

AddRange only optimizes change detection. It does not change how the changes are applied to the database. learn.microsoft.com/en-us/dotnet/api/… – Myrwyn

Thanks, shall I delete this answer? – Tananarive

Answer 8

Bulk Update can be done in three steps with simple EF instead of separate extension methods :-

• Load all the entities first.
• Foreach on each entity and change its field values.
• After Foreach save the context changes once.

This will send multiple Update queries in single batch.

Amplifier

You are describing a batch update, not a bulk update, which is slow for 50k rows. – Jellyfish

Plus for posterity to anyone reading this in the future - this would select every record from the database into memory then operate on each one individually in C# before sending all of them back as sequential update calls in one batch SQL command. EDIT: For this question, this is probably the only way you can do this using Entity Framework, but considering each record has to roundtrip instead of just get updated there are better solutions than using EF here – Epigraph

Answer 9

Posible by using UpdateRange([NotNullAttribute] params TEntity[] entities)

       private void bulkTagUpdate()
        {
            RfidTag tag = new RfidTag
            {
                Id = 1,
                Status ="tight",
                TagId = "234353444",
                LocationId = "1",
                CreatedAt = DateTime.Now,
                UpdatedAt = DateTime.Now,
            };
            RfidTag tag2 = new RfidTag
            {
                Id = 2,
                Status = "tight",
                TagId = "3454544",
                LocationId = "1",
                CreatedAt = DateTime.Now,
                UpdatedAt = DateTime.Now,
            };

            List<RfidTag> tagList = new List<RfidTag>();
            tagList.Add(tag);
            tagList.Add(tag2);

            using (rfid_statusContext context = new rfid_statusContext())
            {
                context.RfidTags.UpdateRange(tagList);
                context.SaveChanges();
                MessageBox.Show("Update successfull !");
            }
        }

Larch

This generates one update statement per record. That's not bulk update. – Coward