Bulk Update with Entity Framework Core (EF Core)

Season's Greetings! As we embrace this festive period, it’s also the perfect time to reflect on optimizing our development practices—like improving database operations. This article delves into strategies for performing bulk updates in EF Core, ensuring your applications remain efficient and performant.

When working with large datasets in Entity Framework Core (EF Core), you may encounter scenarios where updating multiple records in a database can be a performance bottleneck if done one record at a time. EF Core provides mechanisms to improve the efficiency of such operations. This article explores how to perform bulk updates efficiently using EF Core, complete with practical examples and code snippets.

Understanding the Problem

By default, EF Core handles database updates by:

• Tracking each entity in memory.

• Sending individual UPDATE statements for each modified entity.

This approach can be inefficient when dealing with a large number of records, leading to significant database overhead and longer execution times. To mitigate this, we can use techniques like batching, raw SQL queries, or third-party libraries like EFCore.BulkExtensions.

Scenario: Bulk Updating Employee Salaries

Imagine you are working on an HR application where you need to apply a 10% salary increase to all employees in a specific department.

Option 1: Using EF Core’s Change Tracking

This method leverages EF Core's built-in change tracking but can be inefficient for large datasets.

using (var context = new MyDbContext())
{
    var employees = context.Employees.Where(e => e.Department == "IT").ToList();

    foreach (var employee in employees)
    {
        employee.Salary *= 1.1m; // Increase salary by 10%
    }

    context.SaveChanges();
}

Pros:

• Simple to implement.

• Leverages EF Core’s tracking capabilities.

Cons:

• Loads all entities into memory.

• Executes multiple UPDATE statements, one per record.

Option 2: Using Raw SQL

Raw SQL queries provide direct control over the UPDATE statement, allowing for efficient bulk updates.

using (var context = new MyDbContext())
{
    var department = "IT";

    string sql = "UPDATE Employees SET Salary = Salary * 1.1 WHERE Department = @Department";

    context.Database.ExecuteSqlRaw(sql, new SqlParameter("@Department", department));
}

Pros:

• Executes a single UPDATE statement.

• Avoids loading entities into memory.

Cons:

• Bypasses EF Core’s tracking.

• Requires manually ensuring SQL syntax correctness.

Option 3: Using EFCore.BulkExtensions

The EFCore.BulkExtensions library simplifies bulk operations while maintaining EF Core integration.

Installation

Install the EFCore.BulkExtensions package:

Install-Package EFCore.BulkExtensions

- Implementation

using EFCore.BulkExtensions;

using (var context = new MyDbContext())
{
    var employees = context.Employees.Where(e => e.Department == "IT").ToList();

    foreach (var employee in employees)
    {
        employee.Salary *= 1.1m;
    }

    context.BulkUpdate(employees);
}

Pros:

• Maintains EF Core’s integration.

• Highly performant for large datasets.

• Reduces the number of database round-trips.

Cons:

• Requires an additional library.

Best Practices for Bulk Updates

1. Filter Early: Use WHERE clauses to narrow down the affected records.

2. Batch Updates: Split large datasets into smaller chunks if the database has transaction limits.

3. Monitor Performance: Use tools like SQL Profiler or Application Insights to track query performance.

4. Use Transactions: Wrap bulk updates in transactions to maintain data consistency.

Conclusion

Bulk updates are essential for efficiently modifying large datasets in EF Core. Depending on your project requirements, you can:

• Use EF Core’s default methods for simplicity.

• Leverage raw SQL for direct control.

• Integrate libraries like EFCore.BulkExtensions for optimal performance.

By choosing the right approach and adhering to best practices, you can ensure your application scales effectively while maintaining data integrity.

sing System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using EFCore.BulkExtensions;

namespace BulkUpdateExample
{
    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Department { get; set; }
        public decimal Salary { get; set; }
    }

    public class MyDbContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("YourConnectionStringHere");
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new MyDbContext())
            {
                var employees = context.Employees.Where(e => e.Department == "IT").ToList();

                foreach (var employee in employees)
                {
                    employee.Salary *= 1.1m;
                }

                context.BulkUpdate(employees);
            }

            Console.WriteLine("Bulk update completed.");
        }
    }
}

LinkedIn Account : LinkedIn
Twitter Account: Twitter
Credit: Graphics sourced from Milan Jovanovic