Raw SQL Queries and Views (FromSqlRaw)

LINQ is incredibly powerful, but it cannot express every possible SQL feature. If you have an ultra-complex SQL Server Stored Procedure written by a 30-year DBA veteran, or a heavily optimized Common Table Expression (CTE), you don't rewrite it in C#. You drop down into Raw SQL using EF Core.

1. FromSqlRaw and FromSqlInterpolated

EF Core allows you to execute SQL string literals directly, mapping the results back to your strongly-typed C# Entities.

// Danger: FromSqlRaw is vulnerable to SQL Injection if you manually concatenate strings!
var rawQuery = await _context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0}", categoryId)
    .ToListAsync();

// Safer: FromSqlInterpolated uses C# string interpolation syntax ($"...") 
// but parses it securely into SQL Parameters automatically!
var safeQuery = await _context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE CategoryId = {categoryId}")
    .ToListAsync();

2. Mixing Raw SQL with LINQ

This is the most powerful feature of FromSqlRaw. If you execute a Stored Procedure or complex SQL view, EF Core treats the result as an IQueryable. That means you can dynamically append C# LINQ commands on top of the raw SQL!

// 1. Execute a massive SQL View
var query = _context.Products
    .FromSqlRaw("SELECT * FROM vw_ComplexProductReporting");

// 2. Append standard LINQ paging dynamically!
// EF Core wraps your raw query dynamically:
// SELECT * FROM (SELECT * FROM vw...) ORDER BY Name OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY
var pagedResults = await query
    .OrderBy(p => p.Name)
    .Skip(50)
    .Take(10)
    .ToListAsync();

3. The Non-Entity Mapping Problem (Dapper)

EF Core's FromSqlRaw has a strict limitation: The SQL you execute MUST return columns that map perfectly to an existing DbSet<Entity> that EF Core is tracking. You cannot use EF Core to execute a random SQL query like SELECT AVG(Price) as Average From Products if you don't have a C# class with those exact properties mapped in the DbContext.

The Industry Standard: When Architects need to query random anonymous datasets or highly customized DTOs that don't belong in the EF Core Model, we bypass EF Core entirely and inject Dapper. We instantiate a raw SqlConnection, use Dapper's .QueryAsync<T>(), and benefit from nanosecond response times.

4. Interview Mastery

Q: "We used `.FromSqlInterpolated` to execute a stored procedure that returns our `User` objects. Later in the method, we modified the `user.Name` and called `SaveChanges()`. Will the database update?"

Architect Answer: "Yes! This is the fundamental difference between EF Core Raw SQL and a micro-ORM like Dapper. Even though you used a raw SQL string to retrieve the data from the database, because the result was mapped to a tracked `DbSet`, EF Core intercepts the incoming objects and registers them into the Change Tracker normally. As long as you did not use `.AsNoTracking()`, modifying the C# object and calling `SaveChanges()` will generate the exact same `UPDATE` statement as if you had queried the data using standard LINQ."

Interview Q&A for this course