Raw SQL queries in Entity Framework Core (EF Core) allow you to execute plain SQL commands directly against the database. This approach is useful for scenarios where you need to leverage complex SQL queries, stored procedures, or perform operations that are not easily achieved using LINQ. This tutorial covers various techniques for using raw SQL in EF Core, including new features introduced in EF Core 8 and best practices.
Raw SQL allows you to execute SQL commands directly in EF Core, providing more control over the database interactions. It's particularly useful for complex queries or when leveraging existing SQL scripts and stored procedures.
public void ExecuteRawSqlExample()
{
var products = _context.Products
.FromSqlRaw("SELECT * FROM Products")
.ToList();
}
This example introduces the concept of raw SQL queries and their benefits in EF Core.
The FromSqlRaw method allows you to execute a raw SQL query that returns entity types. It supports parameterized queries to prevent SQL injection.
public List<Product> GetProductsByCategory(string category)
{
return _context.Products
.FromSqlRaw("SELECT * FROM Products WHERE Category = {0}", category)
.ToList();
}
This example demonstrates how to use the FromSqlRaw method to execute a query and map the results to a DbSet.
Use ExecuteSqlRaw for executing SQL commands that do not return data, such as INSERT, UPDATE, or DELETE statements. This method also supports parameterized queries.
public void UpdateProductPrices()
{
_context.Database.ExecuteSqlRaw(
"UPDATE Products SET Price = Price * 1.1 WHERE Discontinued = 0");
}
This example shows how to use ExecuteSqlRaw to perform a bulk update operation.
You can call stored procedures using the FromSqlRaw method by providing the procedure name and parameters. This is useful for leveraging existing database logic.
public List<Product> GetProductsByStoredProcedure(int categoryId)
{
return _context.Products
.FromSqlRaw("EXEC GetProductsByCategory @CategoryId={0}", categoryId)
.ToList();
}
This example illustrates how to call a stored procedure and map the results to a DbSet.
When the result of a SQL query does not match an entity type, you can map the results to anonymous types using LINQ projection.
public List<object> GetProductSummaries()
{
return _context.Products
.FromSqlRaw("SELECT Name, Price FROM Products")
.Select(p => new { p.Name, p.Price })
.ToList();
}
This example shows how to execute a raw SQL query and project the results into an anonymous type.
Parameterizing raw SQL queries is crucial for preventing SQL injection attacks. Use FormattableString with FromSqlInterpolated or ExecuteSqlInterpolated for safe parameterized queries.
public List<Product> GetProductsSecurely(string category)
{
return _context.Products
.FromSqlInterpolated($"SELECT * FROM Products WHERE Category = {category}")
.ToList();
}
This example demonstrates how to parameterize a raw SQL query to ensure security.
Combine raw SQL queries with transactions to ensure atomic operations, particularly when executing multiple related commands.
public void ExecuteTransactionWithRawSql()
{
using (var transaction = _context.Database.BeginTransaction())
{
try
{
_context.Database.ExecuteSqlRaw("DELETE FROM Orders WHERE OrderDate < '2023-01-01'");
_context.Database.ExecuteSqlRaw("UPDATE Customers SET Status = 'Inactive' WHERE LastOrderDate < '2023-01-01'");
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
This example illustrates how to execute raw SQL commands within a transaction.
Sometimes, the result of a SQL query might not map directly to an entity type. You can project results into DTOs or other custom types.
public List<OrderSummaryDto> GetOrderSummaries()
{
return _context.Orders
.FromSqlRaw("SELECT OrderId, OrderDate, TotalAmount FROM Orders")
.Select(o => new OrderSummaryDto
{
OrderId = o.OrderId,
OrderDate = o.OrderDate,
TotalAmount = o.TotalAmount
})
.ToList();
}
This example shows how to map SQL query results to a non-entity type using LINQ.
Complex joins involving multiple tables can be executed using raw SQL, providing flexibility and control over the query structure.
public List<ProductCategoryDto> GetProductsWithCategories()
{
return _context.Products
.FromSqlRaw(@"SELECT p.Name, c.Name AS CategoryName
FROM Products p
JOIN Categories c ON p.CategoryId = c.Id")
.Select(p => new ProductCategoryDto
{
ProductName = p.Name,
CategoryName = p.CategoryName
})
.ToList();
}
This example demonstrates how to perform complex joins using raw SQL queries.
Proper error handling is essential when executing raw SQL queries to ensure stability and reliability of your application.
public async Task<List<Product>> ExecuteRawSqlWithErrorHandlingAsync(string query)
{
try
{
return await _context.Products.FromSqlRaw(query).ToListAsync();
}
catch (SqlException ex)
{
Console.WriteLine($"SQL error occurred: {ex.Message}");
return new List<Product>();
}
}
This example shows how to implement error handling for raw SQL queries.
Raw SQL queries can be optimized for performance by leveraging database-specific features such as indexes, hints, and execution plans.
public async Task<List<Product>> GetHighPriceProductsAsync()
{
return await _context.Products
.FromSqlRaw("SELECT * FROM Products WHERE Price > 100 WITH (NOLOCK)")
.ToListAsync();
}
This example provides tips for optimizing raw SQL queries for better performance.
EF Core 8 introduces several enhancements for working with raw SQL, including better integration with LINQ and support for complex queries and transformations.
public async Task<List<Product>> GetEnhancedQueryProductsAsync()
{
// Example of EF Core 8 enhanced raw SQL usage
return await _context.Products
.FromSqlRaw("SELECT * FROM Products WHERE Price > 200")
.ToListAsync();
}
This example highlights some of the new features in EF Core 8 that enhance raw SQL usage.
EF Core 8 improves support for JSON data types, allowing you to work with JSON directly in raw SQL queries for databases that support it.
public async Task<List<Product>> GetProductsWithJsonDataAsync()
{
return await _context.Products
.FromSqlRaw("SELECT * FROM Products WHERE JsonData->>'$.isAvailable' = 'true'")
.ToListAsync();
}
This example shows how to query and manipulate JSON data using raw SQL in EF Core 8.
EF Core 8 allows for seamless integration of raw SQL with LINQ, enabling more complex queries and transformations within your application logic.
public List<Product> GetProductsLinqAndSql(string category)
{
var sql = _context.Products.FromSqlRaw("SELECT * FROM Products WHERE Category = {0}", category);
return sql.Where(p => p.Price > 50).ToList();
}
This example demonstrates how to combine raw SQL with LINQ to create powerful queries.
Perform bulk operations using raw SQL to efficiently update or delete large sets of data, taking advantage of EF Core 8's enhanced support for these operations.
public void PerformBulkDelete()
{
_context.Database.ExecuteSqlRaw("DELETE FROM Products WHERE Stock = 0");
}
This example illustrates how to use raw SQL for efficient bulk operations.
EF Core 8 introduces better support for temporal tables, allowing you to use raw SQL to query historical data and changes over time.
public async Task<List<HistoricalOrder>> GetHistoricalOrdersAsync(DateTime startDate)
{
return await _context.Orders
.FromSqlRaw("SELECT * FROM Orders FOR SYSTEM_TIME AS OF {0}", startDate)
.ToListAsync();
}
This example shows how to use raw SQL to interact with temporal tables.
Implement soft deletes using raw SQL by marking records as inactive rather than deleting them, providing a more flexible approach to data management.
public void MarkProductAsDeleted(int productId)
{
_context.Database.ExecuteSqlRaw(
"UPDATE Products SET IsDeleted = 1 WHERE Id = {0}", productId);
}
This example demonstrates how to use raw SQL for soft delete functionality.
Create custom mappings between raw SQL query results and your domain models to handle non-standard data structures or complex queries.
public List<CustomMappedProduct> GetCustomMappedProducts()
{
return _context.CustomMappedProducts
.FromSqlRaw("SELECT Name, Price, CustomField FROM Products")
.ToList();
}
This example illustrates how to implement custom mappings with raw SQL.
Leverage database-specific functions in your raw SQL queries to perform calculations or transformations directly on the database server.
public List<Product> GetProductsWithCustomFunction()
{
return _context.Products
.FromSqlRaw("SELECT * FROM Products WHERE dbo.IsAvailable(Price) = 1")
.ToList();
}
This example shows how to use raw SQL with database functions for advanced operations.
EF Core 8 allows mapping raw SQL query results to custom models, not just entities defined in the DbContext. This feature is useful for complex queries that return a subset of data or require transformations.
public List<CustomModel> GetCustomModelData()
{
return _context.CustomModels
.FromSqlRaw("SELECT CustomField1, CustomField2 FROM CustomTable")
.ToList();
}
This example demonstrates how to map raw SQL results to custom models using EF Core 8.
Here are some best practices for using raw SQL in EF Core:
AsNoTracking to improve performance for read-only queries.Raw SQL in EF Core provides flexibility and power for handling complex queries and advanced database interactions. By understanding and implementing best practices for raw SQL, you can optimize your application's performance and maintain security while leveraging the full capabilities of your database.