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

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 2

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 3

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

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