When LINQ Isn't Enough: Raw SQL in Entity Framework Core

Sometimes LINQ can't express what you need. A complex stored procedure. A database-specific function. A performance-critical query the ORM mangles. That's when you reach for raw SQL — but EF Core still wants to help.

Let's explore the escape hatches.

FromSqlRaw: Queries That Return Entities

When you need raw SQL but still want entity tracking and LINQ composition:

var products = dbContext.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > 100")
    .ToList();

The results are tracked entities. You can modify and save them.

Even better — you can chain LINQ operators:

var products = dbContext.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > 100")
    .OrderBy(p => p.Name)   // Added by EF
    .Take(10)                // Added by EF
    .ToList();

EF wraps your SQL as a subquery and adds the LINQ parts. The generated SQL looks like:

SELECT * FROM (
    SELECT * FROM Products WHERE Price > 100
) AS p
ORDER BY p.Name
LIMIT 10

FromSqlInterpolated: Safe Parameterization

Never concatenate user input into SQL. Use interpolation:

var category = "Electronics";
var minPrice = 50m;

var products = dbContext.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Category = {category} AND Price > {minPrice}")
    .ToList();

EF extracts the interpolated values as parameters. The actual SQL becomes:

SELECT * FROM Products WHERE Category = @p0 AND Price > @p1

SQL injection? Prevented. The interpolation syntax looks like string building, but EF converts it to parameters.

Fun fact: FromSqlInterpolated uses C#'s FormattableString feature under the hood. The compiler treats $"..." differently when the method expects FormattableString instead of string. This trick lets EF intercept the interpolation values before they become a string, extracting them as parameters.

ExecuteSqlRaw: Commands That Don't Return Entities

For UPDATE, DELETE, INSERT, or anything that doesn't return rows you want as entities:

// Simple execution
int rowsAffected = dbContext.Database.ExecuteSqlRaw(
    "UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = 5");

// With parameters
int rowsAffected = dbContext.Database.ExecuteSqlInterpolated(
    $"UPDATE Products SET Price = Price * {multiplier} WHERE CategoryId = {categoryId}");

Returns the number of affected rows. No entity tracking involved.

Stored Procedures

Procedures That Return Entities

var products = dbContext.Products
    .FromSqlRaw("EXEC GetProductsByCategory @p0", categoryId)
    .ToList();

// Or with named parameters
var products = dbContext.Products
    .FromSqlRaw("EXEC GetProductsByCategory @CategoryId = {0}", categoryId)
    .ToList();

The procedure must return columns matching the entity shape.

Procedures That Return Scalars or Non-Entities

// Using ADO.NET through EF
var connection = dbContext.Database.GetDbConnection();
await connection.OpenAsync();

using var command = connection.CreateCommand();
command.CommandText = "EXEC GetProductCount @CategoryId";
command.Parameters.Add(new SqlParameter("@CategoryId", categoryId));

var count = (int)await command.ExecuteScalarAsync();

For complex scenarios, you drop down to raw ADO.NET. EF manages the connection; you manage the command.

Keyless Entity Types for Custom Queries

When your SQL returns a shape that isn't a table:

// Define a keyless type
[Keyless]
public class ProductSummary
{
    public string Category { get; set; }
    public int Count { get; set; }
    public decimal TotalValue { get; set; }
}

// Register it
modelBuilder.Entity<ProductSummary>().HasNoKey();

// Query it
var summaries = dbContext.Set<ProductSummary>()
    .FromSqlRaw(@"
        SELECT Category, COUNT(*) as Count, SUM(Price) as TotalValue 
        FROM Products 
        GROUP BY Category")
    .ToList();

Keyless tells EF this type isn't tracked and has no primary key. Perfect for aggregated results, views, or stored procedure results.

Database Functions

EF Core can call database-specific functions:

// SQL Server specific
var products = dbContext.Products
    .Where(p => EF.Functions.Like(p.Name, "%phone%"))
    .ToList();

// Date functions
var recent = dbContext.Orders
    .Where(o => EF.Functions.DateDiffDay(o.OrderDate, DateTime.Now) < 30)
    .ToList();

EF.Functions provides database-specific operations that translate to native SQL functions.

When to Use Raw SQL

1. Complex stored procedures — especially legacy ones returning non-entity shapes
2. Database-specific features — window functions, CTEs, recursive queries
3. Performance-critical paths — when EF's generated SQL is inefficient
4. Bulk operations — UPDATE/DELETE affecting many rows without loading entities
5. Administrative queries — database maintenance, statistics, metadata

When NOT to Use Raw SQL

1. Simple CRUD — LINQ handles this better with compile-time checking
2. Dynamic filters — LINQ composes; SQL concatenation leads to injection
3. When you haven't measured — EF's SQL is usually fine. Profile first.

The Hybrid Approach

Combine raw SQL for the complex part, LINQ for the rest:

var baseQuery = dbContext.Products
    .FromSqlRaw(@"
        SELECT p.* 
        FROM Products p
        INNER JOIN ProductMetrics pm ON p.Id = pm.ProductId
        WHERE pm.ViewCount > 1000");

// Add LINQ filtering
if (minPrice.HasValue)
    baseQuery = baseQuery.Where(p => p.Price >= minPrice.Value);

// Add LINQ projection
var results = baseQuery
    .Select(p => new { p.Id, p.Name, p.Price })
    .ToList();

Raw SQL gets the complex join. LINQ handles dynamic filtering and projection safely.

The Rule

1. Returns entities? FromSqlRaw / FromSqlInterpolated + optional LINQ chain
2. Modifying data? ExecuteSqlRaw / ExecuteSqlInterpolated
3. Non-entity results? Keyless entity type or raw ADO.NET
4. User input? Always use Interpolated or parameters — never concatenate
5. Try LINQ first — raw SQL is an escape hatch, not the first tool

---

Next time, we'll wrap up with LINQ performance tips — the tricks and pitfalls that separate a query that screams from one that crawls. Hope to see you!