C# Entity Framework: Bulk Update with Where and Join

Question

How do I update Certain records in with Join and Where clause in Entity Framework Core? Using the template answer below (Attach/isModified)? https://stackoverflow.com/a/44247720/12425844

"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. As it's attached as Unchanged by default. You need to call make isModified = true"

Current requirement:

var data = _dbContext.Set<Product>()
    .Include(c => c.ProductType)
    .Where(x => x.Manufacturer == "ABC Company" &&
                x.StartYear == 2019 &&
                x.ProductType.ProductTypeDescription == "Electronics")

data.Manufacturer = "XYZ Company";
data.StartYear = 2020;
_dbContext.SaveChangesAsync();

Goal Code: Have to add where and join to find particular records:

https://stackoverflow.com/a/44247720/12425844

using (myDbEntities db = new myDbEntities())
{
    try
    {
      db.Configuration.AutoDetectChangesEnabled = false;

      MyObjectEntity entityToUpdate = new MyObjectEntity() {Manufacturer = "XYZ Company", StartYear =2020};
      db.Entry(entityToUpdate).Property(e => e.Manufacturer).IsModified = true;
      db.Entry(entityToUpdate).Property(e => e.StartYear).IsModified = true;

      db.SaveChanges();
    }
    finally
    {
      db.Configuration.AutoDetectChangesEnabled = true;
    }
}

Using EF Net Core 3.1 ,

Will not accept answers using Raw sql or EF Extensions for now, just Native EF Core

EF Core only tracks changes to individual items, and has no method to generate bulk updates. So you're going to have to allow some other answer. — Jeremy Lakeman, Aug 19 '20 at 02:02
hi @JeremyLakeman so I can only update All the records in the database, no where clause? — , Aug 19 '20 at 02:04
Fundamentally, SaveChanges generates update statements based on the primary keys of individual objects that were attached to the change tracker. So you'd need to load all the objects, change the values, then save. Which is clearly inefficient. — Jeremy Lakeman, Aug 19 '20 at 02:07
@JeremyLakeman ok, I thought this answer would avoid all this? it avoids change tracking and prevents loading all the entities https://stackoverflow.com/a/44247720/12425844 — , Aug 19 '20 at 02:07
If you know the primary keys of all the rows you want to update, yes you can attach them to the context without loading them. If you don't, well EF Core doesn't support generating sql for "bulk updates". — Jeremy Lakeman, Aug 19 '20 at 02:09
ok @JeremyLakeman seems contrary to the resource, which does not mention that, and is updating by nonprimary key values — , Aug 19 '20 at 02:10
Without changing the property `db.Configuration.AutoDetectChangesEnabled` just get the records and update the values for it and directly call `SaveChanges()` method, that should update the records in the db. — Sowmyadhar Gourishetty, Aug 19 '20 at 07:40

score 3 · Accepted Answer · answered Aug 19 '20 at 03:02

3

If EF Extensions is out, what about EFCore.BulkExtensions?

await _dbContext.Set<Product>()
    .Where(x => x.Manufacturer == "ABC Company" &&
                x.StartYear == 2019 &&
                x.ProductType.ProductTypeDescription == "Electronics")
    .BatchUpdateAsync(x => new Product(){
                Manufacturer = "XYZ Company",
                StartYear = 2020 });

Note update is performed as raw sql, objects loaded into the change tracker are not updated. You may wish to surround the bulk operations and SaveChangesAsync in a transaction.

YMMV, I haven't used this library myself.

answered Aug 19 '20 at 03:02

Jeremy Lakeman

9,515
25
29

I am only allowing Native EF core solutions and said in question, no extensions, thanks – Aug 19 '20 at 07:12
The license is MIT, so you are free to copy-paste the source code. – Jeremy Lakeman Aug 20 '20 at 00:57
While I could read the `BulkExtensions` source code and explain how it works in an SO answer, the problem you want to solve is complicated. Someone else has already solved it. They have allowed you to use and distribute their solution for free. So, I'd do that. – Jeremy Lakeman Aug 20 '20 at 01:07
hi, I have follow up question here, https://stackoverflow.com/questions/63514362/c-sharp-entity-framework-update-only-first-batch-records-and-stop thanks for all the help – Aug 20 '20 at 23:17

Arad Alvand · Answer 2 · 2023-07-26T17:59:01.050

Native .EF Core 7.0+ solution:

For those using EF Core 7.0 or higher, you can now use the new ExecuteUpdate method to achieve exactly this — to use OP's example:

_dbContext.Set<Product>()
    .Include(c => c.ProductType)
    .Where(x => x.Manufacturer == "ABC Company" &&
                x.StartYear == 2019 &&
                x.ProductType.ProductTypeDescription == "Electronics")
    .ExecuteUpdate(x => x
        .SetProperty(p => p.Manufacturer, "XYZ Company")
        .SetProperty(p => p.StartYear, "2020"));

This will generate the right UPDATE SQL statement. For more details see this answer, and the official blog post "What's new in EF Core 7.0?"

C# Entity Framework: Bulk Update with Where and Join

2 Answers2

Native .EF Core 7.0+ solution:

Linked