Case 14: The Inefficient Loop - Batch Updates and Deletes

The Story

Imagine your application has a notification system. The notifications table has columns like id, user_id, is_read (true/false), and message.

There is a "Mark all as read" button. When a user clicks it, all of their unread notifications should be updated to is_read = true. A user might have hundreds of unread notifications.

The Problem Code (Fetch, then Update)

The most natural way to do this with older versions of EF Core is to load the data, change it, and then save it back.

public async Task MarkAllAsReadAsync(int userId)
{
    // Step 1: Load ALL unread notifications into memory
    var unreadNotifications = await _context.Notifications
                                            .Where(n => n.UserId == userId && !n.IsRead)
                                            .ToListAsync();

    // Step 2: Loop through each one and change its state
    foreach (var notification in unreadNotifications)
    {
        notification.IsRead = true;
    }

    // Step 3: Send ALL the changes back to the database
    await _context.SaveChangesAsync();
}

Why is this a problem?

1. Over-fetching: The first query loads the entire content of hundreds of notifications (including the potentially large message column) into the application's memory, just to change a single true/false flag.
2. Change Tracking Overhead: EF Core has to "track" hundreds of these entities, which uses up memory and CPU.
3. Many UPDATE Queries: When you call SaveChangesAsync(), EF Core sends a separate UPDATE command for each notification to the database. If there are 200 notifications, it makes 200 small round-trips to the database inside one transaction. This is a very "chatty" and inefficient way to communicate.

The Solution: Use Batch Updates with ExecuteUpdateAsync (EF Core 7+)

Instead of pulling data to the application to process it, tell the database to perform the entire update operation with a single command.

ExecuteUpdateAsync and ExecuteDeleteAsync are methods introduced in EF Core 7 to do exactly this.

Here is the better code:

public async Task<int> MarkAllAsReadAsync(int userId)
{
    // OPTIMIZED: Turn the entire logic into a single UPDATE command
    // and execute it directly on the database.
    return await _context.Notifications
                         .Where(n => n.UserId == userId && !n.IsRead)
                         .ExecuteUpdateAsync(updates =>
                             updates.SetProperty(n => n.IsRead, true));
}

What We Gained

1. The Generated SQL:

   • Before:
     1. One SELECT * FROM "notifications" WHERE ...
     2. Hundreds of UPDATE "notifications" SET "is_read" = TRUE WHERE "id" = @p0;
   • After:
     1. Just one UPDATE "notifications" SET "is_read" = TRUE WHERE "UserId" = @p0 AND "is_read" = FALSE;

2. Superior Performance:

   • No Over-fetching: No data is downloaded to the application.
   • Minimal Network Traffic: Only one small command is sent to the database.
   • No Application Overhead: No RAM or CPU is wasted on change tracking.
   • Database Efficiency: The database performs a single set-based update, which is the most efficient way for it to work.

The Golden Rule

"For batch operations, avoid loading data into memory."

The "load, loop, and save" pattern is a serious anti-pattern for batch tasks.

Use ExecuteUpdateAsync and ExecuteDeleteAsync in EF Core 7+ to perform batch updates and deletes in the most efficient way possible.