DEV Community

Cristian Sifuentes
Cristian Sifuentes

Posted on

Entity Framework Core Raw SQL — From FromSql() to Production‑Grade Safety

#csharp #entityframework #sql #dotnet

Entity Framework Core Raw SQL — From FromSql() to Production‑Grade Safety

Entity Framework Core Raw SQL — From FromSql() to Production‑Grade Safety

Most .NET developers eventually hit the same moment with Entity Framework Core:

“LINQ is great… but I need raw SQL here.”

That’s when APIs like FromSqlRaw, ExecuteSqlRaw, and FromSqlInterpolated enter the picture — and that’s also where security, performance, and correctness can silently go wrong.

This article goes deeper than the docs:

  • Why EF Core deprecated some APIs
  • What really happens inside EF Core, the JIT, and the database engine
  • How to use raw SQL safely without losing performance
  • Mental models used by senior .NET engineers in production systems

If you’ve ever wondered “what’s the real difference?”, this post is for you.

Table of Contents

  1. The Mental Model: Why Raw SQL Exists in EF Core
  2. FromSql vs FromSqlRaw vs FromSqlInterpolated
  3. Deprecated APIs — What Changed and Why
  4. What Happens Under the Hood (EF Core + ADO.NET + SQL Server)
  5. Security Deep Dive: SQL Injection at the Instruction Level
  6. Performance Reality: Query Plans, Parameterization, and Caching
  7. ExecuteSqlRaw for Commands (UPDATE / DELETE)
  8. Advanced Patterns Used in Production
  9. Common Pitfalls (and How Senior Devs Avoid Them)
  10. Production Checklist

1. Mental Model: Why Raw SQL Exists in EF Core

EF Core was designed around LINQ-to-Entities, but no ORM can fully replace SQL.

Raw SQL exists for three main reasons:

  1. Expressiveness

    Some queries (CTEs, window functions, hints) are awkward or impossible in LINQ.

  2. Performance

    Hand‑tuned SQL can outperform generated queries in hot paths.

  3. Integration

    Legacy databases, views, stored procedures, and reporting queries.

Key idea:

EF Core does not bypass ADO.NET.

Raw SQL still flows through parameterization, command preparation, and execution plans.

2. FromSql vs FromSqlRaw vs FromSqlInterpolated

❌ FromSql() (Deprecated)

Historically:

context.Users.FromSql("SELECT * FROM Users WHERE Name = {0}", name);
Enter fullscreen mode Exit fullscreen mode
  • Automatically parameterized
  • Safer by default
  • Removed to reduce ambiguity and API surface

✅ FromSqlRaw() 

context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Name = @name",
        new SqlParameter("@name", name));
Enter fullscreen mode Exit fullscreen mode

Characteristics:

  • Maximum control
  • Zero magic
  • You are responsible for parameter safety

✅ FromSqlInterpolated() (Recommended) 

context.Users
    .FromSqlInterpolated($"SELECT * FROM Users WHERE Name = {name}");
Enter fullscreen mode Exit fullscreen mode

EF Core converts interpolation into parameters automatically.

3. Deprecated APIs — What Changed and Why

EF Core removed:

  • FromSql
  • ExecuteSql
  • SqlQuery

Reason:

  • Too easy to introduce SQL injection
  • Ambiguous safety guarantees
  • Harder to audit at scale

4. What Happens Under the Hood

Pipeline:

  1. EF Core builds a relational command
  2. Parameters become DbParameter
  3. ADO.NET executes the command
  4. Database parses, optimizes, executes
  5. Results stream back

Parameterized SQL enables plan reuse.

5. SQL Injection — Real Mechanics

Injection changes the SQL parse tree.

Bad:

FromSqlRaw($"SELECT * FROM Users WHERE Name = '{input}'");
Enter fullscreen mode Exit fullscreen mode

Good:

FromSqlInterpolated($"SELECT * FROM Users WHERE Name = {input}");
Enter fullscreen mode Exit fullscreen mode

6. Performance Reality

  • Parameterized SQL = cached plans
  • String SQL = recompilation
  • Compilation cost adds latency

Secure SQL is almost always faster SQL.

7. ExecuteSqlRaw for Commands 

context.Database.ExecuteSqlRaw(
    "UPDATE Users SET IsActive = 0 WHERE LastLogin < @date",
    new SqlParameter("@date", cutoff));
Enter fullscreen mode Exit fullscreen mode

8. Advanced Production Patterns

Hybrid LINQ + SQL 

var query =
    context.Users
        .FromSqlInterpolated($"SELECT * FROM Users WHERE IsActive = {true}")
        .AsNoTracking()
        .Where(u => u.Role == "Admin");
Enter fullscreen mode Exit fullscreen mode

9. Common Pitfalls

  • Concatenating user input
  • Forgetting AsNoTracking()
  • Assuming raw SQL bypasses EF Core
  • Ignoring execution plans

10. Production Checklist

  • [ ] Parameterized queries only
  • [ ] No string concatenation
  • [ ] Execution plans reviewed
  • [ ] Load tested hot paths
  • [ ] Logging enabled

Final Thought

Raw SQL is not an escape hatch.

It’s a precision tool — powerful, fast, and dangerous if misunderstood.

Use it like a scientist, not like a cowboy.

Top comments (0)