C# Academy · Lesson

Raw SQL, Stored Procedures & Interpolation

Execute raw SQL safely with FromSqlRaw, FromSqlInterpolated, and stored procedures while avoiding injection.

Lesson 2 of 413 steps

Raw SQL, Stored Procedures & Interpolation is a free C# Academy lesson on CoddyKit. This is lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the C# Academy learning path, and your progress syncs across the web and the CoddyKit app. The C# Academy course includes 4 lessons in total.

When to Use Raw SQL in EF Core

Most queries work fine with LINQ, but some scenarios need raw SQL: complex analytics, vendor-specific functions, stored procedures, or performance-critical queries that LINQ translates poorly.

EF Core provides safe ways to use raw SQL without sacrificing security.

FromSqlRaw: Safe Parameterized Queries

FromSqlRaw executes raw SQL and maps results back to entities. Always use {0} placeholders — never string concatenation — to prevent SQL injection.

// SAFE: parameterized
var products = await _db.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 100m)
    .ToListAsync();

// DANGEROUS: never do this
// FromSqlRaw("SELECT * FROM Products WHERE Price > " + price)

FromSqlInterpolated: Interpolated Safety

FromSqlInterpolated accepts a C# interpolated string. EF Core automatically converts the interpolated values to SQL parameters — safe and readable.

decimal minPrice = 100m;
string category = "Electronics";

var products = await _db.Products
    .FromSqlInterpolated(
        $"SELECT * FROM Products WHERE Price > {minPrice} AND Category = {category}")
    .OrderBy(p => p.Name)
    .ToListAsync();
// Values become @p0, @p1 parameters automatically

Composing LINQ After FromSql

You can chain LINQ operators after FromSql. EF Core wraps your raw SQL as a subquery and applies LINQ on top, which is useful for adding filtering or pagination.

decimal min = 50m;
var page = await _db.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Price > {min}")
    .Where(p => p.IsActive)        // additional LINQ filter
    .OrderBy(p => p.Price)
    .Skip(0).Take(20)              // pagination
    .ToListAsync();
// Generates: SELECT ... FROM (raw SQL) AS p WHERE ...

Executing Stored Procedures

Call stored procedures returning entity rows via FromSqlRaw. For procedures that don't return rows (e.g., INSERT/UPDATE), use ExecuteSqlRawAsync.

// Stored proc returning Products
var products = await _db.Products
    .FromSqlRaw("EXEC GetProductsByCategory {0}", "Books")
    .ToListAsync();

// Stored proc with no return rows
await _db.Database.ExecuteSqlRawAsync(
    "EXEC ArchiveOldOrders {0}",
    DateTime.UtcNow.AddYears(-1));

ExecuteSqlInterpolated for DML

ExecuteSqlInterpolated runs DML statements (INSERT, UPDATE, DELETE) with interpolated-string safety. It returns the number of affected rows.

int days = 30;
int affected = await _db.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Orders SET Status = 'Expired' WHERE PlacedAt < {DateTime.UtcNow.AddDays(-days)}");

Console.WriteLine($"{affected} orders expired");

Querying Non-Entity Types

Use SqlQuery<T> (.NET 7+) to query arbitrary scalar or DTO types that aren't EF Core entities. No DbSet required.

// Query a DTO directly — no entity needed
var stats = await _db.Database
    .SqlQuery<ProductStats>(
        $"SELECT Category, COUNT(*) AS Count, AVG(Price) AS AvgPrice FROM Products GROUP BY Category")
    .ToListAsync();

public record ProductStats(string Category, int Count, decimal AvgPrice);

Keyless Entity Types for Views

Map a database view or raw query result to a keyless entity type using HasNoKey() and ToView(). Query it like a DbSet without needing a primary key.

public class ProductSalesSummary
{
    public string Category { get; set; } = "";
    public int TotalSold { get; set; }
    public decimal Revenue { get; set; }
}

// In OnModelCreating:
modelBuilder.Entity<ProductSalesSummary>()
    .HasNoKey()
    .ToView("vw_ProductSalesSummary");

// Query:
var summary = await _db.Set<ProductSalesSummary>().ToListAsync();

Avoiding SQL Injection Pitfalls

The golden rule: never concatenate user input into SQL strings. Always use parameterized queries via FromSqlRaw placeholders, FromSqlInterpolated, or EF Core's LINQ.

// SAFE
var name = userInput;
var result = await _db.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Name = {name}")
    .ToListAsync();

// INJECTION RISK (never do this)
// FromSqlRaw("SELECT * FROM Products WHERE Name = '" + name + "'");

Output Parameters with ADO.NET Fallback

EF Core doesn't natively support stored procedure output parameters. For those cases, fall through to ADO.NET using _db.Database.GetDbConnection().

await _db.Database.OpenConnectionAsync();
var conn = _db.Database.GetDbConnection();
using var cmd = conn.CreateCommand();
cmd.CommandText = "EXEC GetOrderCount @CustomerId, @Count OUTPUT";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@CustomerId", 5));
var outParam = new SqlParameter("@Count", SqlDbType.Int) { Direction = ParameterDirection.Output };
cmd.Parameters.Add(outParam);
await cmd.ExecuteNonQueryAsync();
int count = (int)outParam.Value;

Real-World: Full-Text Search Fallback

LINQ can't express database-specific full-text search predicates. Use FromSqlInterpolated to leverage the native FREETEXT or tsvector capabilities.

string query = "keyboard mechanical";
var results = await _db.Products
    .FromSqlInterpolated(
        $"SELECT * FROM Products WHERE FREETEXT(Name, {query})")
    .AsNoTracking()
    .Take(20)
    .ToListAsync();

Quick Check

Why is FromSqlInterpolated safer than string concatenation for parameterized queries?

Answer it in the interactive lesson

Recap: Raw SQL in EF Core

Key takeaways:

FromSqlRaw: use {0} placeholders; never concatenate user input
FromSqlInterpolated: safe interpolated strings → ADO.NET parameters
Chain LINQ operators after FromSql to add filtering, ordering, pagination
ExecuteSqlInterpolatedAsync: safe DML execution
SqlQuery<T>: query non-entity DTOs (.NET 7+)
Use ADO.NET directly for output parameters or complex stored procs

Free to start

Learn C# with an AI tutor — free

Write and run real code in your browser, get instant help from a 24/7 AI tutor, and pick up where you left off on the web or in the app.

Courses: 93
Lessons: 346

Start learning free View the full course

Frequently Asked Questions

Is the “Raw SQL, Stored Procedures & Interpolation” lesson free?

Yes — the full text of “Raw SQL, Stored Procedures & Interpolation” is free to read here on the web. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the C# Academy course, upgrade to CoddyKit PRO. The C# Academy course includes 4 lessons in total.

What will I learn in “Raw SQL, Stored Procedures & Interpolation”?

Execute raw SQL safely with FromSqlRaw, FromSqlInterpolated, and stored procedures while avoiding injection. You practise C# Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start C# Academy?

No prior experience is required. C# Academy on CoddyKit is structured for beginners through advanced learners, so you can start here or from the beginning and move at your own pace. This is lesson 2 of 4.

How long does the “Raw SQL, Stored Procedures & Interpolation” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this C# Academy lesson?

Yes. Every C# Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Eager, Lazy & Explicit Loading
Raw SQL, Stored Procedures & Interpolation
Compiled Queries & Performance
Global Query Filters & Owned Entities

← Back to C# Academy