How would you execute raw SQL queries in EF Core? (Mid Level)

EF Core provides powerful mechanisms for interacting with your database, primarily through LINQ queries. However, there are scenarios where executing raw SQL queries directly is necessary or more efficient. EF Core allows this through three key methods: FromSQLRaw for queries that return entities, and ExecuteSQLRaw / ExecuteSQLInterpolated for non-query commands like updates, inserts, or deletes.

Introduction to Raw SQL in EF Core

While EF Core’s LINQ capabilities are robust, sometimes you encounter situations where a direct SQL query offers better performance, addresses complex database-specific features, or simplifies logic that would be overly convoluted in LINQ. Understanding how to execute raw SQL queries safely and effectively is a valuable skill for any EF Core developer.

Methods for Executing Raw SQL

1. FromSQLRaw: Retrieving Entities from Raw Queries

Use FromSQLRaw for SQL queries that are intended to return data and be materialized into your defined entity types. This method allows you to inject raw SQL directly into your LINQ queries, acting as the initial queryable. EF Core then materializes the results into instances of your specified entity.

When to use:

• When you need to retrieve data using a SQL query that’s difficult or inefficient to express with LINQ (e.g., complex joins, subqueries, specific database functions).
• To leverage database-specific features (like window functions, grouping sets, or geospatial calculations) that EF Core’s LINQ provider does not directly support.

Real-World Example: Complex Reporting System

In a project involving a complex reporting system, we faced challenges pulling data from multiple tables with specific aggregations that were difficult to express efficiently with LINQ. By using FromSQLRaw, we could directly write optimized SQL queries, including window functions and grouping sets, and map the results to a Data Transfer Object (DTO) specifically designed for the report. This approach significantly improved performance compared to a LINQ equivalent that generated less optimal SQL.

2. ExecuteSQLRaw: Executing Non-Query Commands

ExecuteSQLRaw is used for executing SQL commands that do not return a result set. This includes operations like UPDATE, INSERT, DELETE, or calling stored procedures that perform actions but don’t fetch data. It returns the number of rows affected by the command.

When to use:

• For bulk data modifications (updates, inserts, deletes) where processing each entity individually in memory would be inefficient.
• To call stored procedures that perform database actions without returning entities.

Real-World Example: Bulk Price Update

We had a scenario requiring a bulk update of product prices based on complex criteria involving several tables. Using ExecuteSQLRaw allowed us to write a stored procedure on the database server and call it directly from EF Core, passing in the necessary parameters. This approach was much faster than retrieving all the products, updating them individually in C#, and then saving the changes back to the database, which would have incurred significant round-trip overhead.

3. ExecuteSQLInterpolated: Secure Parameterized Commands

ExecuteSQLInterpolated offers a safer and more convenient way to execute raw SQL commands with parameters, specifically designed to prevent SQL injection vulnerabilities. It uses C# 6.0 string interpolation syntax, where interpolated parameters are automatically converted into proper database parameters by EF Core.

When to use:

• Whenever you need to execute a non-query command that involves user-provided or dynamic data.
• As a best practice for security when executing any raw SQL command with variable inputs.

Security Benefits:

The primary benefit of ExecuteSQLInterpolated is its built-in protection against SQL injection attacks. Instead of concatenating strings, which allows malicious input to alter your query, interpolated parameters are treated as data, ensuring that only the intended query logic is executed.

Real-World Example: Preventing SQL Injection

During a security audit, a potential SQL injection vulnerability was flagged in some of our older code that used string concatenation to build SQL queries. We refactored these queries to use ExecuteSQLInterpolated. This ensured that user-provided input was properly parameterized, effectively preventing malicious SQL from being injected and executed, thereby significantly enhancing the application’s security posture.

Key Considerations and Best Practices

Performance Advantages

Raw SQL can offer significant performance advantages in complex scenarios. By bypassing EF Core’s query translation layer and direct interaction with the database, you can write highly optimized queries, leverage database-specific indexes, and utilize functions that might not be accessible via LINQ. This is particularly useful for complex joins, aggregations, or bulk operations.

Example: Geospatial Calculations

In one project, we were using a specific database function for geospatial calculations that wasn’t directly supported by EF Core. Using FromSQLRaw, we seamlessly integrated this function into our application while maintaining excellent performance. We meticulously documented this dependency to ensure awareness if a database migration ever became necessary.

Trade-offs: Portability and Change Tracking

While raw SQL offers flexibility and performance, it comes with crucial trade-offs. When you write raw SQL, you are tying your code to the specific dialect of your database (e.g., T-SQL for SQL Server, PL/pgSQL for PostgreSQL). This means that if you ever need to switch to a different database system, you might have to rewrite those specific queries, impacting application portability.

Additionally, EF Core’s change tracking mechanism does not apply to entities retrieved using raw SQL methods like FromSQLRaw. If you modify these entities, you need to explicitly manage their persistence (e.g., by attaching them to the context and setting their state to Modified) rather than relying on automatic change detection.

Security Risks and SQL Injection Prevention

Security is paramount when working with raw SQL. One of the biggest risks is SQL injection. Imagine building a query by concatenating strings, for example: SELECT * FROM Users WHERE Username = '" + username + "'". If a malicious user enters something like ' OR '1'='1'; DROP TABLE Users; -- for the username, your entire user table could be wiped out.

Parameterized queries, like those created with ExecuteSQLInterpolated or by passing parameters to FromSQLRaw/ExecuteSQLRaw, completely mitigate this risk. They treat user input as data, not executable code, ensuring that only the intended query is executed. Always prioritize parameterized queries when dynamic values are involved.

Code Sample

Here are practical examples demonstrating the use of these methods:

using Microsoft.EntityFrameworkCore;
using System.LinQ; // For .ToList()

// Assume 'context' is an instance of your DbContext

// Example using FromSQLRaw to query entities
// Note: SELECT * is used here for simplicity. In production, specify columns.
var blogs = context.Blogs
    .FromSQLRaw("SELECT * FROM Blogs WHERE Name LIKE '{0}%'", "The")
    .ToList();

Console.WriteLine($"Found {blogs.Count} blogs starting with 'The'.");
foreach (var blog in blogs)
{
    Console.WriteLine($"- {blog.Name}");
}

// Example using ExecuteSQLRaw for a command (e.g., bulk update)
// Always use parameters to prevent SQL injection, even with ExecuteSQLRaw
var affectedRowsRaw = context.Database.ExecuteSQLRaw(
    "UPDATE Blogs SET Url = {0} WHERE Url LIKE {1}",
    "http://blogs.msdn.com/dotnet",
    "%aspnet%");

Console.WriteLine($"ExecuteSQLRaw: {affectedRowsRaw} rows affected.");

// Example using ExecuteSQLInterpolated for a command with parameters (safer)
string author = "Alice";
var affectedRowsInterpolated = context.Database.ExecuteSQLInterpolated(
    $"UPDATE Posts SET IsPublished = 1 WHERE Author = {author}");

Console.WriteLine($"ExecuteSQLInterpolated: {affectedRowsInterpolated} rows affected.");

// Note: FromSQLRaw/Interpolated can also be used with stored procedures returning entities
// Ensure the columns returned by the stored procedure match the entity properties.
// var blogsFromSP = context.Blogs
//     .FromSQLInterpolated($"EXEC GetBlogsByAuthor {author}")
//     .ToList();
// Console.WriteLine($"Blogs from SP: {blogsFromSP.Count}");

// Note: ExecuteSQLRaw/Interpolated can also be used to execute stored procedures not returning entities
// context.Database.ExecuteSQLRaw("EXEC UpdateBlogUrl @id = {0}, @newUrl = {1}", 1, "http://newurl.com");
// Console.WriteLine("Stored procedure executed.");