Raw SQL in EF Core, safely: .NET lessons from production

Raw SQL in EF Core, safely

You're three months into building a trading platform, and performance is starting to matter. LINQ queries that felt fine on a hundred records now crawl with a million. That's when the temptation hits: drop into raw SQL, bypass the ORM overhead, and ship. I get it. But I've also spent hours hunting SQL injection vulnerabilities in production systems where developers took shortcuts. The good news is that EF Core gives you a safe path to raw SQL—if you use the right APIs and understand what they actually do.

FromSql with interpolation parameterizes automatically

This is the golden rule: use FromSql with C# string interpolation, and EF Core handles parameterization for you. No manual placeholder management. No risk.

var email = userInput; // Untrusted
var users = db.Users
    .FromSql($"select * from users where email = {email}")
    .ToList();

Here's what happens behind the scenes: EF Core detects the interpolated expression {email}, extracts its value, and creates a parameterized query. The actual SQL sent to the database looks like this:

-- What EF Core sends to SQL Server
exec sp_executesql 
    N'select * from users where email = @p0',
    N'@p0 nvarchar(max)',
    @p0 = 'attacker@evil.com'

The input value never becomes part of the SQL text. A malicious actor could pass email = "'; DROP TABLE users; --" and it would simply become a literal search for that exact email address. The database treats it as data, not code.

This works with complex queries too. I used this pattern on a ride-hailing platform to bypass a slow LEFT JOIN:

var riderId = request.RiderId;
var searchRadius = request.RadiusKm;
var minimumRating = request.MinRating;

var nearbyDrivers = db.Drivers
    .FromSql($@"
        select d.* from drivers d
        where d.rating >= {minimumRating}
        and st_distance(d.location, {searchRadius}) < 50000
        and d.status = 'active'
        order by d.rating desc
    ")
    .Where(d => d.IsAvailable)
    .ToList();

Notice the multi-line string with multiple interpolations. Each {variable} becomes a separate parameter. EF Core tracks them all.

The constraint: your raw SQL must map to an entity type that exists in your DbContext. FromSql returns IQueryable<TEntity>, so you can chain LINQ operations afterward. That's powerful, but sometimes you need to run ad-hoc queries that don't fit any model.

FromSqlRaw with concatenated input is SQL injection

This is where I see mistakes in code reviews. FromSqlRaw exists for legitimate reasons, but it's the unsafe path. It takes a raw SQL string with no parameterization.

// NEVER DO THIS
var email = userInput;
var users = db.Users
    .FromSqlRaw($"select * from users where email = '{email}'")
    .ToList();

// Or worse, string concatenation
var query = "select * from users where email = '" + email + "'";
var users = db.Users.FromSqlRaw(query).ToList();

If email is admin' or '1'='1, the executed query becomes:

select * from users where email = 'admin' or '1'='1'

You just selected every user in the database. Worse attacks can modify data or delete tables.

FromSqlRaw does accept parameters via its optional second argument, and when used correctly, it's safe:

// Safe use of FromSqlRaw
var email = userInput;
var users = db.Users
    .FromSqlRaw("select * from users where email = {0}", email)
    .ToList();

But this is awkward. The positional placeholders are easy to miscount, and there's no compiler support. If you're going to use FromSqlRaw, you're already adding friction. Just reach for FromSql with interpolation instead. It's cleaner and harder to misuse.

The only legitimate reason I use FromSqlRaw is when the query is hardcoded and no user input touches it:

// Hardcoded query, no interpolation, no risk
var activeUsers = db.Users
    .FromSqlRaw("select * from users where status = 'active'")
    .ToList();

Even then, I'd argue FromSql is clearer because it's consistent with my parameterized approach everywhere else.

SqlQuery<T> for unmapped results (EF Core 8+)

EF Core 8 introduced SqlQuery<T> on DbContext. This is for arbitrary queries that don't map to an entity type. Think analytics queries, materialized views, or stored procedure results that have no corresponding table.

On a travel booking platform, I had a query that aggregated bookings across multiple tables and calculated seasonal pricing. The result shape didn't fit any entity:

public class PricingSnapshot
{
    public string Destination { get; set; }
    public DateTime WeekStart { get; set; }
    public decimal AveragePricePerNight { get; set; }
    public int AvailableListings { get; set; }
}

// In your repository or service
var destination = userInput; // e.g., "Bali"
var snapshots = db.Database.SqlQuery<PricingSnapshot>($@"
    select
        l.destination,
        datetrunc('week', b.checkin_date) as week_start,
        avg(b.total_price / b.nights) as average_price_per_night,
        count(distinct l.id) as available_listings
    from listings l
    left join bookings b on l.id = b.listing_id
    where l.destination = {destination}
    and b.checkin_date >= dateadd(month, -3, getdate())
    group by l.destination, datetrunc('week', b.checkin_date)
").ToList();

SqlQuery<T> parameterizes your interpolations the same way FromSql does. It's safe. And because the result type is just a POCO, EF Core doesn't try to track it or attach change detection. It's a read-only snapshot.

The trade-off: you can't chain LINQ to the results. The query runs immediately when you call .ToList(). But for analytics and reporting, that's rarely a problem. You want the full result set on the server anyway.

Common mistakes

• Mixing string.Format with FromSql. If you do .FromSql(string.Format("... {0} ...", userInput)), you've bypassed parameterization. EF Core sees a static string, not an interpolation. Don't format before passing to FromSql.
• Assuming FromSql prevents all SQL issues. It prevents injection, but you can still write N+1 queries or inefficient predicates. Use .AsNoTracking() if you're not modifying results, and profile the generated SQL.
• Returning raw SQL results from APIs without filtering. If your FromSql query selects sensitive columns (passwords, API keys), and you serialize that result to JSON, they leak. Use a DTO or .Select() to project only safe fields.
• Forgetting that FromSql requires a mapped entity. You can't use it for arbitrary shapes. That's why SqlQuery<T> exists in EF Core 8+. If you're on earlier versions, use FromSqlRaw with an unmapped class, or execute raw ADO.NET.
• Not testing raw SQL in code review. A colleague once passed a raw query that worked locally but crashed on production because the SQL Server version didn't support a syntax feature. Always test across your target environments.

Review every Raw call in code review

When I see FromSql, FromSqlRaw, or SqlQuery in a pull request, I pause. These are legitimate tools, but they're also where subtle bugs hide. Here's my checklist:

1. Is it FromSql with interpolation or FromSqlRaw with parameters? If I see string concatenation or string.Format, I ask the developer to refactor.
2. Does the query have a specific, documented reason? "Performance" isn't enough. What LINQ query did you try first? What was the bottleneck? Is the raw SQL actually faster, or are you guessing?
3. Are all user inputs interpolated? Trace every variable in the query string. If any come from a request body, query param, or header without being interpolated, flag it.
4. What fields are selected? If the query pulls sensitive data, does the response DTO filter it out?
5. Is there a comment explaining the non-standard approach? Future maintainers need to know why this query didn't use LINQ.
6. Are there integration tests? Not just unit tests. Run the query against a real database and verify the results and performance.

On a CRM platform, I found a raw query that looked safe at first glance:

var sortBy = request.SortField; // "name"
var query = db.Database.SqlQuery<CustomerDto>($@"
    select * from customers
    order by {sortBy}
").ToList();

The developer didn't interpolate sortBy because it's a column name, not a value. But column names can't be parameterized in most databases. However, they didn't validate that sortBy was in a whitelist either. An attacker could pass sortBy = "name; drop table customers; --". I pushed back and we added a guard:

var allowedFields = new[] { "name", "email", "created_at" };
var sortBy = request.SortField;

if (!allowedFields.Contains(sortBy))
    throw new ArgumentException($"Invalid sort field: {sortBy}");

var query = db.Database.SqlQuery<CustomerDto>($@"
    select * from customers
    order by {sortBy}
").ToList();

Now it's safe. The lesson: raw SQL review isn't just about parameterization. It's about understanding every variable and its source.

Key takeaways

• FromSql with C# string interpolation is parameterized automatically and safe from SQL injection.
• FromSqlRaw or string concatenation with user input is dangerous. Use parameters if you must use it, but prefer FromSql.
• SqlQuery<T> (EF Core 8+) lets you execute arbitrary SQL and map to unmapped types. It's parameterized like FromSql.
• Raw SQL should have a documented reason and performance validation. "It might be faster" isn't enough.
• Code review every raw SQL call. Check inputs, validate column/table names against whitelists, and test across environments.
• Consider the response shape. Filter sensitive fields before serializing to JSON.