Raw SQL in EF Core: FromSqlInterpolated and Beyond

EF Core's LINQ provider handles most queries well, but sometimes you need raw SQL — for complex queries the translator can't handle, database-specific features, or performance-critical paths. EF Core provides several safe ways to drop into SQL without abandoning the framework entirely.

FromSqlInterpolated

The safest way to use raw SQL with parameters. C# string interpolation is automatically converted to parameterised SQL:

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

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

EF Core translates this to:

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

The interpolated values become parameters — not string concatenation. This is safe from SQL injection by design.

FromSqlRaw

For queries where you need explicit control over parameter placement, use FromSqlRaw:

var products = await context.Products
    .FromSqlRaw(
        "SELECT * FROM Products WHERE Price > {0} AND Category = {1}",
        minPrice, category)
    .ToListAsync();

The {0} and {1} placeholders are not string interpolation — they're parameter indices. EF Core parameterises them properly.

Warning: Never use string concatenation with FromSqlRaw:

// DANGEROUS — SQL injection vulnerability!
var products = await context.Products
    .FromSqlRaw($"SELECT * FROM Products WHERE Category = '{userInput}'")
    .ToListAsync();

// SAFE — use FromSqlInterpolated instead
var products = await context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Category = {userInput}")
    .ToListAsync();

Composing LINQ on Top of Raw SQL

One of EF Core's most powerful features: you can compose LINQ operators on top of raw SQL queries:

var products = await context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Category = {category}")
    .Where(p => p.IsActive)
    .OrderBy(p => p.Name)
    .Take(20)
    .ToListAsync();

EF Core wraps your SQL as a subquery and applies the LINQ operators:

SELECT TOP(20) [p].[Id], [p].[Name], [p].[Price], ...
FROM (
    SELECT * FROM Products WHERE Category = @p0
) AS [p]
WHERE [p].[IsActive] = 1
ORDER BY [p].[Name]

This lets you use database-specific SQL where needed while still leveraging LINQ for filtering, sorting, and paging.

SqlQuery for Scalar and Non-Entity Results

EF Core 8 introduced SqlQuery<T> for queries that return non-entity types:

var categoryCounts = await context.Database
    .SqlQuery<CategoryCount>(
        $"SELECT Category AS Name, COUNT(*) AS Count FROM Products GROUP BY Category")
    .ToListAsync();

public record CategoryCount(string Name, int Count);

For scalar values:

var count = await context.Database
    .SqlQuery<int>($"SELECT COUNT(*) AS [Value] FROM Products WHERE Price > {minPrice}")
    .SingleAsync();

Note: scalar queries require the column to be aliased as Value.

ExecuteSqlInterpolated for Non-Query Commands

For INSERT, UPDATE, and DELETE statements that don't return entities:

var rowsAffected = await context.Database
    .ExecuteSqlInterpolatedAsync(
        $"UPDATE Products SET Price = Price * {multiplier} WHERE Category = {category}");

This bypasses the change tracker entirely. Any tracked entities won't reflect the changes.

Calling Stored Procedures

Raw SQL works well with stored procedures:

var products = await context.Products
    .FromSqlInterpolated($"EXEC GetProductsByCategory @Category = {category}")
    .ToListAsync();

For procedures that don't return entity types:

await context.Database
    .ExecuteSqlInterpolatedAsync($"EXEC ArchiveOldOrders @CutoffDate = {cutoffDate}");

Calling Table-Valued Functions

Map a TVF in your model and query it naturally:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<ProductSearchResult>()
        .HasNoKey()
        .ToFunction("SearchProducts");
}

// Usage
var results = await context.Set<ProductSearchResult>()
    .FromSqlInterpolated($"SELECT * FROM dbo.SearchProducts({searchTerm})")
    .ToListAsync();

Rules for Raw SQL Queries

1. The query must return all columns that map to the entity type. You can't SELECT Id, Name when the entity has more properties.
2. Column names must match the property names (or their configured column mappings).
3. You can't include related data directly — but you can compose .Include() on top of FromSqlInterpolated if the query returns a tracked entity type.
4. Always use parameterised queries. Never concatenate user input into SQL strings.

// You CAN compose Include on raw SQL
var orders = await context.Orders
    .FromSqlInterpolated($"SELECT * FROM Orders WHERE Status = {status}")
    .Include(o => o.Items)
    .ToListAsync();

When to Use Raw SQL

Use raw SQL when LINQ can't express the query you need — window functions, recursive CTEs, database-specific hints, or heavily optimised queries. For everything else, prefer LINQ. The EF Core query translator is good and improving with every release, and LINQ queries benefit from compile-time type checking and refactoring support that raw SQL strings lack.