How can you execute raw SQL queries in EF Core?

In Entity Framework Core (EF Core), you can execute raw SQL queries directly against the database using the DbSet<TEntity>.FromSqlRaw() or DbSet<TEntity>.FromSqlInterpolated() methods. This is useful when you need to execute SQL queries that are not easily expressed using LINQ, or when you want to leverage specific database features. Here’s how to execute raw SQL queries in EF Core:

1. Executing Raw SQL Queries

Using FromSqlRaw()

• FromSqlRaw() is used for executing raw SQL queries that return entity types. It allows you to pass raw SQL strings and bind parameters.

Example:

Assume you have a Product entity and you want to execute a raw SQL query to retrieve products with a specific price.

using (var dbContext = new AppDbContext())
{
    var price = 20.0;
    var products = dbContext.Products
                            .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", price)
                            .ToList();
}

Using FromSqlInterpolated()

• FromSqlInterpolated() is similar to FromSqlRaw() but uses string interpolation to safely embed parameters into the SQL string. This helps to prevent SQL injection attacks.

Example:

using (var dbContext = new AppDbContext())
{
    var price = 20.0;
    var products = dbContext.Products
                            .FromSqlInterpolated($"SELECT * FROM Products WHERE Price > {price}")
                            .ToList();
}

2. Executing SQL Commands

You can also execute SQL commands that do not return entities (such as INSERT, UPDATE, or DELETE statements) using the Database.ExecuteSqlRaw() or Database.ExecuteSqlInterpolated() methods.

Using ExecuteSqlRaw()

Example:

using (var dbContext = new AppDbContext())
{
    var productId = 1;
    var newPrice = 25.0;
    var affectedRows = dbContext.Database.ExecuteSqlRaw("UPDATE Products SET Price = {0} WHERE Id = {1}", newPrice, productId);
}

Using ExecuteSqlInterpolated()

Example:

using (var dbContext = new AppDbContext())
{
    var productId = 1;
    var newPrice = 25.0;
    var affectedRows = dbContext.Database.ExecuteSqlInterpolated($"UPDATE Products SET Price = {newPrice} WHERE Id = {productId}");
}

3. Important Considerations

• Entity Tracking: When you use FromSqlRaw() or FromSqlInterpolated(), EF Core tracks the returned entities, and you can continue to work with them as you would with any other entities in the context.
• No Change Tracking for Commands: When executing commands using ExecuteSqlRaw() or ExecuteSqlInterpolated(), the changes are applied directly to the database without EF Core tracking the affected entities.
• Parameterized Queries: Always prefer using parameterized queries to prevent SQL injection attacks.
• Raw SQL Queries and Migrations: Be cautious when using raw SQL in migrations, as it may not be portable across different database providers.

Summary

Executing raw SQL queries in EF Core allows you to harness the full power of SQL while still working within the context of your entity model. Using FromSqlRaw() or FromSqlInterpolated() for queries that return entities, and ExecuteSqlRaw() or ExecuteSqlInterpolated() for executing commands, gives you the flexibility to handle complex scenarios that might not be easily accomplished with LINQ. Always remember to use parameterized queries to enhance security and prevent SQL injection.