C# Academy · Lesson

Raw SQL, Stored Procedures & Interpolation

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

Raw SQL, Stored Procedures & Interpolation is a free C# Academy lesson on CoddyKit — 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, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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?

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

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, and the C# Academy course includes 4 lessons in total. 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.

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; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

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