In Entity Framework, we typically use LINQ for database queries. However, in situations that demand greater control or when we need to use specific database functionalities, we can resort to “Raw SQL Queries.” These queries allow us to write pure SQL, using specific syntaxes, optimizing the performance of complex queries and taking advantage of functionalities that are not easily replicated by conventional Entity Framework methods.
Executing queries with mapped types
One of the main applications of raw SQL queries is to execute SELECT commands directly, map the results to entities or custom types, and integrate them into the EF context.
To execute a SQL query that returns results and map these results to an EF entity:
var products = dbContext.Products
.FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 100)
.ToList();
Here, “FromSqlRaw” allows you to insert a SQL query directly. The {0} is a placeholder that will be replaced by the value passed as an argument.
When using raw SQL queries to map results to Entity Framework entities, it is essential to ensure that the structure of the query result is equivalent to the structure of the entity. This means that the columns returned by the query must match the names and types of the entity’s properties. Otherwise, you may encounter errors or unexpected results when mapping the returned data to the entity.
Executing commands
In addition to SELECT queries, you can use raw SQL to execute commands that do not return results, such as INSERT, UPDATE, or DELETE:
dbContext.Database
.ExecuteSqlRaw("DELETE FROM Usuarios WHERE Id = {0}", "3");
The “ExecuteSqlRaw” method is used to execute SQL commands that modify data in the database without returning a set of results.
Parameters in SQL queries
To avoid security problems, such as SQL injection, it is crucial to use named parameters when constructing SQL queries. Entity Framework provides support for passing parameters safely.
var categoryIdParam = new SqlParameter("@CategoryId", 1);
var products = dbContext.Products
.FromSqlRaw("SELECT * FROM Products WHERE CategoryId = @CategoryId", categoryIdParam)
.ToList();
Here, “SqlParameter” is used to pass a parameter to the query, ensuring that the value is correctly escaped and preventing SQL injections.
Passing multiple parameters
If you need to pass more than one parameter, you can create multiple “SqlParameter” objects and pass them as additional arguments.
For example:
var categoryIdParam = new SqlParameter("@CategoryId", 1);
var minPriceParam = new SqlParameter("@MinPrice", 50);
var products = dbContext.Products
.FromSqlRaw("SELECT * FROM Products WHERE CategoryId = @CategoryId AND Price > @MinPrice", categoryIdParam, minPriceParam)
.ToList();
In this example, two parameters, @CategoryId and @MinPrice, are passed to the query. Each parameter is created as a separate “SqlParameter” object, and then both are included in the call to “FromSqlRaw”. This ensures that the values are properly passed, preventing possible SQL injections and maintaining application security.
Returning non-mapped types
Another powerful feature of Entity Framework 8 is the ability to return non-mapped types directly from raw SQL queries. If you want to return a specific type, such as a DTO (ProductSummary), instead of complete entities, use the “SqlQueryRaw” method in the database context.
var productSummaries = await dbContext.Database
.SqlQueryRaw(
SELECT Id, Name, Price FROM Products WHERE Price > @MinPrice,
new SqlParameter("@MinPrice", 50)
)
.ToListAsync();
Here, “ProductSummary” is a non-mapped type that represents a specific set of columns from the “Products” table. By using “SqlQueryRaw”, you return only the necessary data and optimize performance and avoid loading the complete entity.
Conclusion
Raw SQL Queries in Entity Framework offer significant flexibility to handle complex queries and specific database operations that are not easily expressed through the LINQ API. By using raw SQL queries, developers can gain more granular and optimized control over database interactions.