How to write raw SQL queries in EF Core?

This tutorial demonstrates how to execute raw SQL queries within Entity Framework Core (EF Core). While LINQ provides a powerful and convenient way to interact with your database, there are scenarios where writing raw SQL queries becomes necessary or advantageous. This tutorial explores different methods for executing raw SQL and their respective use cases.

Executing Raw SQL Queries with `FromSqlRaw`

The `FromSqlRaw` method allows you to execute a raw SQL query directly against the database. The result is then mapped to your entity. In this example, we are retrieving all blogs from the `Blogs` table where the `Url` contains 'dotnet'. Make sure to replace `YourConnectionString` with your actual database connection string. The `ToList()` method executes the query and materializes the results. This method is suitable for simple queries where you don't need to pass parameters.

using Microsoft.EntityFrameworkCore;
using System.Linq;

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString"); // Replace with your actual connection string
    }
}

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }
}

public class Example
{
    public static void ExecuteRawSql()
    {
        using (var context = new BloggingContext())
        {
            var blogs = context.Blogs
                .FromSqlRaw("SELECT * FROM Blogs WHERE Url LIKE '%dotnet%' ")
                .ToList();

            foreach (var blog in blogs)
            {
                Console.WriteLine($"Blog ID: {blog.BlogId}, URL: {blog.Url}");
            }
        }
    }
}

Executing Raw SQL Queries with Parameters using `FromSqlRaw`

To prevent SQL injection vulnerabilities, it's crucial to use parameterized queries when incorporating user input. `FromSqlRaw` supports parameterization using positional placeholders. In this example, `{0}` is replaced with the value of the `searchTerm` variable. The arguments are passed as additional parameters to the `FromSqlRaw` method. Note that while `FromSqlRaw` supports parameterization, it is still vulnerable to SQL injection if the parameters themselves are not properly sanitized before being passed to the method. Using `FromSqlInterpolated` will prevent SQL Injection, but it is only available in later .Net versions. Always validate and sanitize input to prevent potential security risks.

using Microsoft.EntityFrameworkCore;
using System.Linq;

public class Example
{
    public static void ExecuteRawSqlWithParameters(string searchTerm)
    {
        using (var context = new BloggingContext())
        {
            var blogs = context.Blogs
                .FromSqlRaw("SELECT * FROM Blogs WHERE Url LIKE {0}", searchTerm)
                .ToList();

            foreach (var blog in blogs)
            {
                Console.WriteLine($"Blog ID: {blog.BlogId}, URL: {blog.Url}");
            }
        }
    }
}

Executing Raw SQL Queries with `FromSqlInterpolated`

`FromSqlInterpolated` (introduced in EF Core 3.0) offers a safer and more readable way to incorporate parameters into raw SQL queries using string interpolation. EF Core automatically handles the parameterization, preventing SQL injection vulnerabilities. Note that while `FromSqlInterpolated` is generally safer than `FromSqlRaw`, it's still important to understand the underlying mechanisms and validate user input where appropriate. In this example, the `searchTerm` variable is directly embedded into the SQL string using the `$` prefix. The `FromSqlInterpolated` will then properly escape the parameter before sending it to the database.

// Requires C# 8.0 or later
using Microsoft.EntityFrameworkCore;
using System.Linq;

public class Example
{
    public static void ExecuteRawSqlInterpolated(string searchTerm)
    {
        using (var context = new BloggingContext())
        {
            var blogs = context.Blogs
                .FromSqlInterpolated($"SELECT * FROM Blogs WHERE Url LIKE {searchTerm}")
                .ToList();

            foreach (var blog in blogs)
            {
                Console.WriteLine($"Blog ID: {blog.BlogId}, URL: {blog.Url}");
            }
        }
    }
}

Executing Raw SQL for Non-Query Operations (e.g., INSERT, UPDATE, DELETE)

For executing SQL statements that don't return entity results (e.g., INSERT, UPDATE, DELETE), use `context.Database.ExecuteSqlRaw` or `context.Database.ExecuteSqlInterpolated`. These methods return the number of rows affected by the query. Again, use interpolated for easier parameterization.

using Microsoft.EntityFrameworkCore;

public class Example
{
    public static void ExecuteRawSqlNonQuery()
    {
        using (var context = new BloggingContext())
        {
            int rowsAffected = context.Database.ExecuteSqlRaw("UPDATE Blogs SET Url = 'https://example.com/newurl' WHERE BlogId = 1");

            Console.WriteLine($"Rows affected: {rowsAffected}");
        }
    }
}

public class Example
{
    public static void ExecuteRawSqlNonQueryInterpolated(int blogId)
    {
        using (var context = new BloggingContext())
        {
            int rowsAffected = context.Database.ExecuteSqlInterpolated($"DELETE FROM Blogs WHERE BlogId = {blogId}");

            Console.WriteLine($"Rows affected: {rowsAffected}");
        }
    }
}

Concepts Behind the Snippets

The core idea behind executing raw SQL in EF Core is to provide flexibility when LINQ queries become too complex or inefficient. `FromSqlRaw` and `FromSqlInterpolated` map the result set of the raw SQL query to entities managed by the DbContext. `ExecuteSqlRaw` and `ExecuteSqlInterpolated` execute commands that do not expect results, returning the affected number of rows. Choosing the right method depends on whether you need to retrieve entities or simply execute a command.

Real-Life Use Case

Consider a scenario where you need to perform a complex data migration or execute a stored procedure that's not easily representable in LINQ. Raw SQL queries provide the necessary control and expressiveness to accomplish such tasks. Another use case is optimizing performance by leveraging database-specific features or indexes that EF Core might not automatically utilize.

Best Practices

• Parameterization: Always use parameterized queries to prevent SQL injection vulnerabilities, even with `FromSqlInterpolated`.
• Validation: Validate and sanitize user input before incorporating it into SQL queries.
• Abstraction: Encapsulate raw SQL queries within repository methods or services to maintain a clean architecture and avoid scattering SQL statements throughout your application.
• Testing: Thoroughly test your raw SQL queries to ensure they produce the expected results and handle edge cases correctly.
• Understand the Risks: Be aware of the potential risks associated with raw SQL, such as SQL injection and database-specific syntax differences.

Interview Tip

When asked about using raw SQL in EF Core, emphasize your understanding of the trade-offs between LINQ and raw SQL. Explain the importance of parameterization and the scenarios where raw SQL becomes necessary for performance optimization or complex operations. Be prepared to discuss potential security risks and mitigation strategies.

When to Use Them

Use raw SQL queries in EF Core when:

• LINQ queries become excessively complex or inefficient.
• You need to leverage database-specific features or optimizations.
• You're performing data migrations or executing stored procedures.
• You require fine-grained control over the generated SQL.

Memory Footprint

Executing raw SQL queries generally doesn't inherently impact memory footprint compared to LINQ queries. The primary memory consumption comes from materializing the results into entities. However, inefficient SQL queries (regardless of whether they're raw or generated by LINQ) can lead to large result sets and increased memory usage. Optimize your SQL queries to retrieve only the necessary data to minimize memory consumption.

Alternatives

• LINQ: The primary alternative is to refactor the LINQ query to achieve the desired result.
• Stored Procedures: For complex operations, consider encapsulating the logic within a stored procedure and calling it from EF Core.
• Database Views: Create a database view to simplify complex queries and map it to an entity in EF Core.

Pros

• Flexibility: Provides maximum control over the generated SQL.
• Performance Optimization: Allows for fine-tuning queries for specific database implementations.
• Access to Database-Specific Features: Enables leveraging features not directly supported by EF Core.

Cons

• SQL Injection Risks: Requires careful parameterization to prevent vulnerabilities (mitigated by `FromSqlInterpolated`).
• Maintenance Overhead: Raw SQL queries can be harder to maintain and debug than LINQ queries.
• Database Dependency: Raw SQL queries are often database-specific, reducing portability.
• Type Safety: Reduced type safety compared to LINQ, as the compiler cannot verify the correctness of the SQL.