Bulk Update in Entity Framework Core

Question

I pull a bunch of timesheet entries out of the database and use them to create an invoice. Once I save the invoice and have an Id I want to update the timesheet entries with the invoice Id. Is there a way to bulk update the entities without loading them one at a time?

void SaveInvoice(Invoice invoice, int[] timeEntryIds) {
    context.Invoices.Add(invoice);
    context.SaveChanges();

    // Is there anything like?
    context.TimeEntries
        .Where(te => timeEntryIds.Contains(te.Id))
        .Update(te => te.InvoiceId = invoice.Id);
}

Answer 1

Disclaimer: I'm the owner of the project Entity Framework Plus

Our library has a Batch Update feature which I believe is what you are looking for

This feature supports EF Core

// Is there anything like? YES!!!
context.TimeEntries
    .Where(te => timeEntryIds.Contains(te.Id))
    .Update(te => new TimeEntry() { InvoiceId = invoice.Id });

Wiki: EF Batch Update

EDIT: Answer comment

does it supports contains as in your example? I think this is coming from EF Core which is not supported feature in 3.1 version even

EF Core 3.x support contains: https://dotnetfiddle.net/DAdIO2

EDIT: Answer comment

this is great but this requires to have zero parameter public constructors for classes. which is not a great. Any way to get around this issue?

Anonymous type is supported starting from EF Core 3.x

context.TimeEntries
    .Where(te => timeEntryIds.Contains(te.Id))
    .Update(te => new { InvoiceId = invoice.Id });

Online example: https://dotnetfiddle.net/MAnPvw

Answer 2

As of EFCore 7.0 you will see the built-in BulkUpdate() and BulkDelete methods:

   context.Customers.Where(...).ExecuteDelete();
   context.Customers.Where(...).ExecuteUpdate(c => new Customer { Age = c.Age + 1 });
   context.Customers.Where(...).ExecuteUpdate(c => new { Age = c.Age + 1 });
   context.Customers.Where(...).ExecuteUpdate(c => c.SetProperty(b => b.Age, b => b.Age + 1));

Answer 3

Are you after the performance of simplified syntax?

I would suggest to use direct SQL query,

 string query = "Update TimeEntries Set InvoiceId = <invoiceId> Where Id in (comma separated ids)";    
 context.Database.ExecuteSqlCommandAsync(query);

For comma separated ids you can do string.Join(',', timeEntryIds)

It depends on what you actually need. If you want to go with Linq, then you need to iterate through each object.

Answer 4

If TimeEntry has an association to Invoice (check the navigation properties), you can probably do something like this:

var timeEntries = context.TimeEntries.Where(t => timeEntryIds.Contains(te.Id)).ToArray();

foreach(var timeEntry in timeEntries)
    invoice.TimeEntries.Add(timeEntry);

context.Invoices.Add(invoice);

//save the entire context and takes care of the ids
context.SaveChanges();

Answer 5

The IQueryable.ToQueryString method introduced in Entity Framework Core 5.0 may help with this scenario. This method will generate SQL that can be included in a raw SQL query to perform a bulk update of records identified by that query.

For example:

void SaveInvoice(Invoice invoice, int[] timeEntryIds) {
    context.Invoices.Add(invoice);
    context.SaveChanges();

    var query = context.TimeEntries
        .Where(te => timeEntryIds.Contains(te.Id))
        .Select(te => te.Id);

    var sql = $"UPDATE TimeEntries SET InvoiceId = {{0}} WHERE Id IN ({query.ToQueryString()})";

    context.Database.ExecuteSqlRaw(sql, invoice.Id);
}

The major drawback of this approach is that you end up with raw SQL appearing in your code. However I don't know of any reasonable way to avoid that with current Entity Framework Core capabilities - you're stuck with this caveat, or the caveats of other answers posted here such as:

• Introducing a dependency on another library such as Entity Framework Plus or ELinq.
• Using DbContext.SaveChanges() which will involve the execution of multiple SQL queries to retrieve and update records one at a time rather than doing a bulk update.

Answer 6

In entity framework core , you can do with update range method. you can see some samples usage here .

using (var context = new YourContext())
{
     context.UpdateRange(yourModifiedEntities);

     // or the followings are also valid
     //context.UpdateRange(yourModifiedEntity1, yourModifiedEntity2, yourModifiedEntity3);
    //context.YourEntity.UpdateRange(yourModifiedEntities);
    //context.YourEntity.UpdateRange(yourModifiedEntity1, yourModifiedEntity2,yourModifiedEntity3);

    context.SaveChanges();
  }

Answer 7

Bulk update supported with EF 7:

context
.TimeEntries
.Where(te => timeEntryIds.Contains(te.Id))
.ExecuteUpdate(s => s.SetProperty(
    i => te.InvoiceId,
    i => invoice.Id));

Also there is async version for this method ExecuteUpdateAsync.

Answer 8

in EF Core 7, use ExecuteUpdate(), what's new

var multipleRows = TableA.Where(t=>t.Id < 99);

multipleRows.ExecuteUpdate(t=> 
    t.SetProperty(
        r => r.Salary,
        r => r.Salary * 2));
    
//SQL already sent to database, do not run below
//SaveChanges(); 

SQL being generated by EF

UPDATE [t]
SET [t].[Salary] = [t].[Salary] * 2
FROM [TableA] AS [t]
WHERE [t].[ID] < 99