CODEWITHSUNDEEP
c#.netentity-frameworkentity-framework-core
Adrian Brand
Adrian Brand

Reputation: 21638

Bulk Update in Entity Framework Core

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);
}

Upvotes: 16

Views: 60019

Answers (8)

Majid Shahabfar
Majid Shahabfar

Reputation: 4829

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));

Upvotes: 19

Rm558
Rm558

Reputation: 4992

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

Upvotes: 1

Saad Qais
Saad Qais

Reputation: 622

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.

Upvotes: 4

chrisg
chrisg

Reputation: 1117

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.

Upvotes: 4

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

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

Upvotes: 18

nzrytmn
nzrytmn

Reputation: 6941

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();
  }

Upvotes: 2

Dhanuka777
Dhanuka777

Reputation: 8616

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.

Upvotes: 8

DesertFox
DesertFox

Reputation: 808

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();

Upvotes: 6

Related Questions