Bits of .NET

⚡ Key Takeaways: Update or delete thousands of records in a single database command without loading them into memory. Performance gains of 100x!

You’ve been loading entire tables just to update a few fields? EF Core 7+ has a better way: ExecuteUpdate and ExecuteDelete.

❌ The Old Way – Inefficient

// ❌ Loads ALL products into memory first!
var products = await context.Products
    .Where(p => p.Price < 100)
    .ToListAsync();  // ❌ 10,000 products loaded!

foreach (var product in products)
{
    product.Price = product.Price * 1.1m;  // 10% increase
    product.UpdatedAt = DateTime.UtcNow;
}

await context.SaveChangesAsync();  // ❌ 10,000 separate UPDATE statements!
// Memory: 50MB | Time: 15 seconds | DB Calls: 10,000

✅ The New Way – ExecuteUpdate

// ✅ Single database round-trip!
await context.Products
    .Where(p => p.Price < 100)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(p => p.Price, p => p.Price * 1.1m)
        .SetProperty(p => p.UpdatedAt, DateTime.UtcNow)
    );

// Memory: 0MB | Time: 150ms | DB Calls: 1
// 100x faster and 99% less memory!

💥 ExecuteDelete – Mass Deletion

// ❌ Load and delete one by one
var oldOrders = await context.Orders
    .Where(o => o.CreatedAt < DateTime.UtcNow.AddYears(-1))
    .ToListAsync();  // ❌ 50,000 orders loaded!

foreach (var order in oldOrders)
{
    context.Orders.Remove(order);
}
await context.SaveChangesAsync();  // ❌ 50,000 DELETEs!

// ✅ One DELETE statement!
await context.Orders
    .Where(o => o.CreatedAt < DateTime.UtcNow.AddYears(-1))
    .ExecuteDeleteAsync();

// Single DELETE FROM Orders WHERE CreatedAt < '2023-01-01'

🚀 Advanced Scenarios

// 1. Conditional updates with joins
await context.Products
    .Where(p => p.Category.Name == "Electronics")
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(p => p.Discount, 0.15m)
        .SetProperty(p => p.UpdatedAt, DateTime.UtcNow)
    );

// 2. Use SQL functions
await context.Products
    .Where(p => p.Description != null)
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(p => p.Name, p => p.Name.ToUpper())
        .SetProperty(p => p.Description, p => p.Description.Substring(0, 100))
    );

// 3. Based on related entities
await context.Orders
    .Where(o => o.Customer.Status == "Inactive")
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(o => o.Status, "Cancelled")
        .SetProperty(o => o.CancelledAt, DateTime.UtcNow)
    );

⚠️ Important Limitations

// ❌ No navigation property updates
// Can't update related entities in one operation

// ❌ No tracking
// Entities are not tracked after update

// ❌ No validation
// EF Core doesn't validate before executing

// ❌ No interceptor support
// ChangeTracker is bypassed

📊 Performance Comparison

Operation	Records	Traditional	ExecuteX	Speedup
Update	10,000	12s	80ms	150x
Delete	50,000	45s	120ms	375x
Memory (Update)	10,000	45MB	0MB	∞