CODEWITHSUNDEEP
c#.netentity-frameworkentity-framework-core
Naresh Babu Garlapati
Naresh Babu Garlapati

Reputation: 21

How to do bulk update in EF Core?

I am trying to bulk update using EF Core.

How to write code in EF Core for the SQL statement shown here?

Update Students 
Set RollNum = 1, 
    Email = '[email protected]', 
    FirstName = 'Ramamkagja'
Where Id in (428, 442, 444, 445, 448, 450, 458, 460)

Upvotes: 2

Views: 5317

Answers (5)

Northern25
Northern25

Reputation: 81

I would suggest you take a look at N.EntityFrameworkCore.Extension. It is a basic bulk extension framework for EFCore 6.0.8+ that is available on nuget.org and the source code is available on Github under MIT license.

Install-Package N.EntityFrameworkCore.Extensions

https://www.nuget.org/packages/N.EntityFrameworkCore.Extensions

Once you install it you can simply use UpdateFromQuery() method. It also supports BulkDelete, BulkInsert, BulkMerge, DeleteFromQuery, InsertFromQuery, and more.

UpdateFromQuery()

    var studentIds = new List<int>() { 428, 442, 444, 445, 448, 450, 458, 460 };
    int rowUpdated = dbContext.Students.Where(o => studentIds.Contains(o.Id).UpdateFromQuery(o => new Student { 
        RollNum = 1, 
        Email = "[email protected]", 
        FirstName = "Ramamkagja"
    });

Upvotes: 0

chrisg
chrisg

Reputation: 1117

If you have an IQueryable then the IQueryable.ToQueryString method introduced in Entity Framework Core 5.0 may help. 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:

using var context = new DbContext();

var ids = new List<int>() { 428, 442, 444, 445, 448, 450, 458, 460 };

var query = context.Students.Where(_ => ids.Contains(_.Id)).Select(_ => _.Id);

var sql = $@"
UPDATE Students
SET RollNum = {{0}}, Email = {{1}}, FirstName = {{2}}
WHERE Id IN ({query.ToQueryString()})
";

context.Database.ExecuteSqlRaw(sql, 1, "[email protected]", "Ramamkagja");

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.
  • Using DbContext.SaveChanges() which will update records one at a time rather than doing a bulk update.

Upvotes: 0

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

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

You are looking for the "Batch Update" feature: https://entityframework-plus.net/ef-core-batch-update

Example:

var ids = new List<int>() { 428, 442, 444, 445, 448, 450, 458, 460 };
ctx.Students.Where(x => ids.Contains(x.Id))
         .Update(x => new Student() { Email = "[email protected]", FirstName = "Ramamkagja" });

Everything will be executed on the database side, so you don't have to load the existing students in your context.

Upvotes: 2

Nadeem Khoury
Nadeem Khoury

Reputation: 937

You can have multiple options to update bulk in entity framework core. The UpdateRange is available since version 1. So you can use that method to update all your entries as following:

using (dbContext context = new dbContext())
{
    var entities = new List<Student>()
        {
        new Student(){Id=8, Email="[email protected]"},
        new Student(){Id=2, Email="[email protected]"},
        new Student(){Id=4, Email="[email protected]"}        
        };
                                
    context.Students.UpdateRange(students);
    context.SaveChanges();
}

Second Approch which I prefer for better perforamnce is to use a stored procedure/or raw SQL query and call it from your code using efcore. Like follwoing:

string query = "Set RollNum = 1, Email = '[email protected]', FirstName = 'Ramamkagja' Where Id in (428, 442, 444, 445, 448, 450, 458, 460);    
 context.Database.ExecuteSqlCommand(query);

References:

Execute Sql Command

Update Range

Upvotes: 0

Mohamed Adel
Mohamed Adel

Reputation: 490

You can use DbContext.UpdateRange Method

Example

using (YourDbContext context = new YourDbContext())
{
    List<Student> students = new List<Student>()
        {
        new Student(){Id=428, Email="[email protected]"},
        new Student(){Id=442, Email="[email protected]"},
        new Student(){Id=444, Email="[email protected]"}
        // Add Full Properties and remaining students here
        };
                                
    context.Students.UpdateRange(students);
    context.SaveChanges();
}

Make sure that student Ids exists in DB

Upvotes: 2

Related Questions