Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 41 ยท Change Tracking and Saving

Bulk Operations and Raw SQL

Learn set-based EF Core operations with ExecuteUpdate and ExecuteDelete, batching with AddRange, and raw SQL with FromSql and ExecuteSql, including parameterization against SQL injection, with stand-ins.

Tracked entities are perfect for editing one thing at a time, but they are a poor fit for "give every product a 10% discount" or "delete all expired sessions". Loading a hundred thousand rows into memory just to change one column and send them back is slow and wasteful. For those jobs EF Core offers set-based operations that run a single SQL statement, and an escape hatch for writing raw SQL safely.

EF Core snippets are read-only. The runnable blocks are stand-ins that count commands and demonstrate parameterization.

ExecuteUpdate and ExecuteDelete

Since EF Core 7, you can update or delete rows with one statement and no tracking at all:

C#
int changed = await db.Products
    .Where(p => p.Category == "Books")
    .ExecuteUpdateAsync(s => s
        .SetProperty(p => p.Price, p => p.Price * 0.9m)
        .SetProperty(p => p.UpdatedAt, DateTime.UtcNow));
// UPDATE Products SET Price = Price * 0.9, UpdatedAt = @p WHERE Category = 'Books'

int removed = await db.Sessions
    .Where(s => s.ExpiresAt < DateTime.UtcNow)
    .ExecuteDeleteAsync();
// DELETE FROM Sessions WHERE ExpiresAt < @now

The return value is the number of affected rows. The Where is translated to SQL like any query, so the work happens in the database.

Things to remember:

  • Immediate: these methods execute right away; they are not queued until SaveChanges.
  • Tracker unaware: entities already tracked in the context are not updated. Reload them or clear the tracker if you need the new values.
  • No SaveChanges transaction: each call is its own statement. Wrap several in an explicit transaction (see transactions).
  • No change interceptors/value generation: audit columns set in SaveChanges overrides will not run; set them yourself in the SetProperty calls (as UpdatedAt above).
  • EF Core 10 lets you pass a normal lambda (with if statements) to build the setters conditionally.

Let us compare the number of operations the two styles need. This stand-in counts "round trips" and "rows moved over the network":

C# Stand-in: load-modify-save versus set-based

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

AddRange and batching

For inserts, AddRange plus a single SaveChanges is already efficient: providers send many INSERTs in one round trip (batching).

C#
db.Products.AddRange(newProducts);      // 5,000 entities
await db.SaveChangesAsync();            // a handful of batched round trips

Tips for large imports:

  • Disable automatic change detection if you only add: db.ChangeTracker.AutoDetectChangesEnabled = false;
  • Save in chunks (1,000-5,000 entities), then db.ChangeTracker.Clear() so memory does not grow.
  • For millions of rows use the database's bulk loader (SQL Server SqlBulkCopy, PostgreSQL COPY) or a library such as EFCore.BulkExtensions; plain EF Core is not a bulk loader.
C#
foreach (var chunk in rows.Chunk(2_000))
{
    db.Products.AddRange(chunk.Select(ToEntity));
    await db.SaveChangesAsync();
    db.ChangeTracker.Clear();
}

Raw SQL queries: FromSql

When LINQ cannot express something (a vendor function, a hand-tuned query, a stored procedure), write SQL and still get entities back:

C#
var minPrice = 20m;
var products = await db.Products
    .FromSql($"SELECT * FROM Products WHERE Price > {minPrice}")
    .OrderBy(p => p.Name)               // LINQ can compose on top
    .ToListAsync();

FromSql takes an interpolated string and turns each into a database parameter, not into text pasted into the SQL. That is what makes it safe. The query must return all columns of the entity type (or use SqlQuery<T> for arbitrary shapes):

C#
var names = await db.Database
    .SqlQuery<string>($"SELECT Name FROM Products WHERE Price > {minPrice}")
    .ToListAsync();

ExecuteSql for commands

C#
int rows = await db.Database.ExecuteSqlAsync(
    $"UPDATE Products SET Stock = Stock - {qty} WHERE Id = {id} AND Stock >= {qty}");

Returns the affected rows. Note this guard (AND Stock >= qty): the check and the change happen in one atomic statement, which is safer for counters than reading, subtracting and writing back.

SQL injection: the one rule

Never build SQL by concatenating user input:

C#
// DANGEROUS: user input becomes part of the SQL text
db.Products.FromSqlRaw("SELECT * FROM Products WHERE Name = '" + name + "'");
// with name = "x' OR '1'='1" this returns every row, and worse is possible

The safe forms are FromSql/ExecuteSql with interpolated strings (parameters are created for you), or FromSqlRaw with explicit placeholders and parameter objects:

C#
db.Products.FromSqlRaw("SELECT * FROM Products WHERE Name = {0}", name);

Interpolation inside FromSqlRaw is not safe because it is the plain C# interpolation that pastes the value in. Table and column names cannot be parameters; if they come from a user, validate against an allow-list.

The stand-in below shows what "pasting" versus "parameter" means, using a simple matcher:

C# Stand-in: concatenation versus parameters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Stored procedures and views

Call a stored procedure with FromSql($"EXEC GetTopProducts {count}") (the result must be non-composable, so you cannot add Where in LINQ on top). Map a database view to a keyless entity with ToView("v_ProductSales") and HasNoKey(), then query it with LINQ as any other set.

Choosing the right tool

Job Tool
Edit one or a few entities Tracking + SaveChanges
Change many rows with a simple rule ExecuteUpdate / ExecuteDelete
Insert thousands AddRange in chunks
Insert millions Provider bulk loader
Query LINQ cannot express FromSql / SqlQuery<T>
Atomic counters ExecuteSql with a guard

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which statement about ExecuteUpdate is true?

    ExecuteUpdate is set-based and immediate; the tracker is bypassed.
  2. 2. Which call is safe against SQL injection?

    FromSql with an interpolated string turns each value into a parameter.
  3. 3. Why use "AND Stock >= {qty}" in a stock update statement?

    The condition is evaluated by the database inside the same statement, so concurrent updates cannot push stock below zero.
  4. 4. What should you do after saving a big chunk of entities in an import loop?

    Clearing the tracker releases references and snapshots.

Summary

  • ExecuteUpdate and ExecuteDelete run one set-based statement, skip tracking and ignore tracked instances.
  • AddRange in chunks plus ChangeTracker.Clear() handles big inserts; use bulk loaders for millions.
  • FromSql, SqlQuery<T> and ExecuteSql take interpolated strings and make parameters; never concatenate user input.
  • Put guards in the SQL itself for atomic counters.

Next: Query performance and the N+1 problem