EF Core ExecuteSql — Complete Guide

In this tutorial, you'll learn about EF Core ExecuteSql. We cover key concepts, practical examples, and best practices.

You need to execute a bulk update, a stored procedure, or a DDL command that does not return rows. Loading entities into the change tracker and calling SaveChanges is inefficient for bulk operations. ExecuteSql runs commands directly against the database.

Wrong

// Loading all entities to update one column:
var products = await db.Products.Where(p => p.Category == "Old").ToListAsync();
foreach (var p in products)
{
    p.Category = "New";
}
await db.SaveChangesAsync();

Output: Loads all matching entities into memory, tracks changes, sends individual UPDATE statements. Inefficient for bulk operations.

Right

await db.Database.ExecuteSqlRawAsync(
    "UPDATE Products SET Category = 'New' WHERE Category = 'Old'");

Output: Single SQL UPDATE statement. No entities loaded. No tracking. Maximum performance.

Parameterized version:

await db.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Products SET Price = Price * {multiplier} WHERE Category = {category}");

For stored procedures:

await db.Database.ExecuteSqlRawAsync(
    "EXEC ArchiveOrders @cutoffDate", new SqlParameter("@cutoffDate", cutoff));

Prevention

• Use ExecuteSqlRaw / ExecuteSqlInterpolated for bulk UPDATE, DELETE, and INSERT.
• Use ExecuteSql for stored procedure calls that do not return results.
• Use ExecuteSql for DDL commands (CREATE, ALTER, DROP).
• Always use parameters to prevent SQL injection.
• Use ExecuteSqlInterpolated for safer parameterized syntax.
• Combine with transactions for atomic bulk operations.
• Do not use ExecuteSql for queries that return data — use FromSql or SqlQuery.

Common Mistakes with core execute sql

1. Non-exhaustive pattern matches that compile with warnings then crash at runtime
2. Misunderstanding that String is [Char] with poor performance for large text operations
3. Using foldl instead of foldl' causing stack overflow on large lists

These mistakes appear frequently in real-world EF code. DodaTech's contributors have identified these patterns through analysis of open-source projects and production systems.

Practice Exercise

Write a pure function that safely divides two integers using Maybe, then test it with edge cases like division by zero and negative numbers.

This exercise reinforces the concepts covered in this guide. Try implementing it before checking online solutions.

FAQ

ExecuteSql is used in DodaTech's data maintenance jobs for efficient bulk operations. For more EF Core, visit DodaTech.