LuisLlamas Logo
EN
entity-framework-consultas-sql-directas

Direct SQL Queries in Entity Framework with FromSqlRaw and ExecuteSqlRaw

  • 3 min
  • Advanced

Entity Framework is a great tool (an ORM) that allows us to work with databases using an object-oriented approach.

However, sometimes we still need to execute direct SQL queries (not very often, but sometimes we will need it).

This allows us to maximize the capabilities of the database, for example, to perform complex operations that are not easy to express with LINQ.

Why use direct SQL queries?

  • Very complex queries: When a query is too complex to express with LINQ.
  • Performance optimization: Taking advantage of specific features of the database.
  • Specific operations: When we need to execute SQL commands that don’t have a direct equivalent in LINQ.

Entity Framework provides two main methods to execute direct SQL queries: FromSqlRaw and ExecuteSqlRaw. Let’s see how to use them 👇

SQL Queries that return data FromSqlRaw

The FromSqlRaw method is used to execute SQL queries that return data. This data is automatically mapped to the entities of our model.

Let’s look at a basic example:

var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 50)
    .ToList();

In this example,

  • We are executing a SQL query that selects all products with a price greater than 50.
  • The result is automatically mapped to the Product entity.

SQL Commands that do not return data with ExecuteSqlRaw

On the other hand, the ExecuteSqlRaw method is used to execute SQL commands that do not return data (such as INSERT, UPDATE, DELETE, or TRUNCATE TABLE).

This method returns the number of affected rows by the command.

var affectedRows = context.Database.ExecuteSqlRaw("UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = {0}", 1);
Console.WriteLine($"Affected rows: {affectedRows}");

In this example, we are updating the price of all products in the category with CategoryId = 1, increasing it by 10%.

Security Considerations

It is important to avoid string concatenation to include parameters in SQL queries, as this can lead to SQL injection vulnerabilities.

Instead, we should use parameters with placeholders ({0}, {1}, etc.) or named parameters. For example,

var minimumPrice = 50;
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", minimumPrice)
    .ToList();

We can also use named parameters:

var minimumPrice = 50;
var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > @minimumPrice", new SqlParameter("@minimumPrice", minimumPrice))
    .ToList();
  1. Use parameters: Always use parameters instead of concatenating strings.
  2. Validate inputs: Make sure to validate and sanitize any user input before using it in an SQL query.

Practical Examples

SQL Query with FromSqlRaw

Suppose we have a product database and we want to get all products from a specific category that have a price above a given value.

var categoryId = 1;
var minimumPrice = 100;

var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0} AND Price > {1}", categoryId, minimumPrice)
    .ToList();

Combination with LINQ

One of the advantages of FromSqlRaw is that we can combine SQL queries with LINQ to perform additional operations, such as filtering, sorting, or projecting.

var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 50)
    .OrderBy(p => p.Name)
    .ToList();

In this example, we are selecting products with a price greater than 50 and then sorting them by name using LINQ.

Bulk Update with ExecuteSqlRaw

Imagine we want to apply a 20% discount to all products in a specific category.

var categoryId = 2;
var affectedRows = context.Database.ExecuteSqlRaw("UPDATE Products SET Price = Price * 0.8 WHERE CategoryId = {0}", categoryId);
Console.WriteLine($"Affected rows: {affectedRows}");

Deleting Records with ExecuteSqlRaw

If we need to delete all products that are out of stock, we can do it as follows:

var affectedRows = context.Database.ExecuteSqlRaw("DELETE FROM Products WHERE Stock = 0");
Console.WriteLine($"Affected rows: {affectedRows}");

On this page