Entity Framework Core Advanced

Raw SQL with EF Core

LINQ covers most queries, but sometimes you need SQL: a complex report, a stored procedure or a bulk update. This lesson shows the safe ways to run raw SQL through EF Core and the bulk ExecuteUpdate and ExecuteDelete methods.

Raw SQL options in EF Core
  1. FromSqlReturns entities from your SQL; LINQ can be added on top
  2. Database.SqlQuery<T>Returns scalar values or unmapped types
  3. Database.ExecuteSqlAsyncRuns a command and returns the rows affected
  4. ExecuteUpdateAsync / ExecuteDeleteAsyncBulk changes written in LINQ, without loading entities

Interpolated values in FromSql and ExecuteSql are sent as parameters, never pasted into the SQL text.

The idea in plain words

EF Core lets you drop down to SQL when you need to while keeping safety. FromSql and ExecuteSql take an interpolated string, and every {value} becomes a database parameter, so user input cannot change the query. The Raw variants (FromSqlRaw, ExecuteSqlRaw) take plain strings and are only safe when you pass parameters yourself.

Example

C#
// Entities from SQL, then more LINQ composed on top
var minPrice = 500m;
var products = await db.Products
    .FromSql($"SELECT * FROM Products WHERE Price > {minPrice}")
    .OrderBy(p => p.Name)
    .ToListAsync();

// A stored procedure
var report = await db.Products
    .FromSql($"EXEC dbo.GetBestSellers {year}")
    .ToListAsync();

// A scalar value
var count = await db.Database
    .SqlQuery<int>($"SELECT COUNT(*) AS Value FROM Orders")
    .SingleAsync();

// A command
await db.Database.ExecuteSqlAsync($"UPDATE Products SET Stock = 0 WHERE Discontinued = 1");

// Bulk update and delete without loading anything
await db.Products
    .Where(p => p.Category == "Stationery")
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 1.1m));

await db.Orders.Where(o => o.Status == "Draft").ExecuteDeleteAsync();

Common mistakes

  • Building SQL with string concatenation and passing it to FromSqlRaw.
  • Adding LINQ on top of a stored procedure call; stored procedures cannot be composed, so call AsEnumerable or ToListAsync first.
  • Expecting ExecuteUpdate and ExecuteDelete to update tracked entities in memory; they go straight to the database and bypass the change tracker.

Interview question

How does EF Core protect against SQL injection when using FromSql with string interpolation?

FromSql accepts a FormattableString, so EF Core receives the SQL template and the interpolated values separately. It replaces each value with a DbParameter placeholder in the SQL sent to the database, and the values travel as parameters. User input is therefore never treated as SQL code. The danger is FromSqlRaw with concatenated strings, which bypasses this protection.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.