Welcome to today’s blog.
In today’s post I will be discussing how to use raw queries in Entity Framework Core and how to use them safely.
In previous posts I showed how to use Entity Framework Core in .NET Core applications. I then showed how to use SQL stored procedures with Entity Framework Core.
I will be showing how to use both SQL queries and SQL stored procedures with Entity Framework Core within a .NET Core application context. In addition will be demonstrating the recommended way in which to execute SQL stored procedures with direct SQL commands using a useful extension method.
What is Entity Framework Core?
Entity Framework Core is the ORM middleware that is used within .NET Core to allow access to a backend database such as SQL server. The purpose behind using am ORM tool is to provide a layer of abstraction for the developer to be able to access backend data through data context DTOs without using SQL directly.
There will however be systems and projects written in legacy .NET Framework 4.x and ADO.NET libraries that access data directly using raw SQL. In this case, converting the application to use an Entity Framework Core backend can be done in two stages:
- Convert the backend from ADO.NET to Entity Framework Core and use raw queries.
- Convert the raw queries and stored procedures to logic within backend .NET Core Web service APIs using Entity Framework Core DTO classes.
Bearing this in mind, it would be beneficial to port the raw SQL to using EF Core raw queries until there is an opportunity to upgrade the hard-coded queries to ORM models.
Making Use of Entity Framework Core Extension Methods
There are some key extension methods that are provided in .NET Core 3.1 which allow use to use raw SQL in our EF Core data context.
The previous version of .NET Core up to 2.2 had a single extension method FromSQL(), which allowed both parametrized and un-parametrized queries.
OWASP Recommendation: Always use parametrization with raw SQL queries.
We should avoid passing a concatenated or interpolated invalid strings values into a FromSqlRaw or ExecuteSqlRaw method.
The following implementation of a FromSqlRaw call is bad and is open to potential injection attacks:
public async Task<List<BookViewModel>> GetNLatestBooks(int numberBooks)
{
var latestNBooks = await _db.Books
.FromSqlRaw("SELECT TOP " + numberBooks.ToString() + " * " + " FROM Books ")
.OrderByDescending(b => b.DateCreated)
.ToListAsync();
return latestNBooks;
}
Making use of the FromSqlInterpolated and ExecuteSqlInterpolated methods using string interpolation protects against SQL injection attacks.
The following table filter call is safe as it uses an interpolated parameter:
public async Task<List<BookViewModel>> SearchBooks(string query)
{
var latestNBooks = await _db.Books
.FromSqlInterpolated($"SELECT * FROM SearchedBooks({query})")
.OrderByDescending(b => b.DateCreated)
.Take(numberBooks)
.ToListAsync();
return latestNBooks;
}
In the next section I will show how to call raw SQL commands using an Entity Framework Core extension method.
Using Raw Stored Procedure Calls
Suppose we have a stored procedure in our backend SQL data base:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetNLatestBooks
@NumberOfLatest int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (@NumberOfLatest) *
FROM Books
ORDER BY DateCreated DESC
END
GO
Calling the SP directly from the SSMS query command is as follows:
EXEC GetNLatestBooks @NumberOfLatest = 5
Within our API service class, calling to the above stored procedure is safe as it uses positional parameters:
public async Task<List<BookViewModel>> GetNLatestBooks(int numberBooks)
{
var latestNBooks = await _db.Books
.FromSqlRaw("EXECUTE GetNLatestBooks {0}", numberBooks)
.ToListAsync();
return latestNBooks;
}
The following stored procedure call uses named parameters:
public async Task<List<BookViewModel>> GetNLatestBooksV4(int numberBooks)
{
var latestNBooks = await _db.Books
.FromSqlInterpolated($"EXECUTE GetNLatestBooks {numberBooks}")
.ToListAsync();
return latestNBooks;
}
The FromRawSql extension method also allows an SqlParameter parameter to be included, so the with following we can setup our parameter before applying it in a stored procedure call:
public async Task<List<BookViewModel>> GetNLatestBooksV5(int numberBooks)
{
SqlParameter nthLatestParam = new SqlParameter("latestNBooks", numberBooks);
var latestNBooks = await _db.Books
.FromSqlRaw("EXECUTE GetNLatestBooks @latestNBooks", nthLatestParam)
.ToListAsync();
return latestNBooks;
}
Using LINQ Extension Methods with Raw SQL
One of the rules we need to bear in mind when using raw SQL within an Entity Framework context is that only queries that begin with a SELECT can be composed with LINQ extension methods.
In this case a stored procedure call cannot be composed with LINQ extension methods.
The following SQL SELECT call is valid:
var latestNBooks = await _db.Books
.FromSqlInterpolated($"SELECT * FROM SearchedBooks({query})")
.OrderByDescending(b => b.DateCreated)
.Take(numberBooks)
.ToListAsync();
We have used additional LINQ extension methods Take() and OrderByDescending().
The following SQL SP call can only return a list after the call:
var latestNBooks = await _db.Books
.FromSqlRaw("EXECUTE GetNLatestBooks @latestNBooks", nthLatestParam)
.ToListAsync();
As we can see, it is possible to use raw SQL queries including stored procedure calls with Entity Framework Core DbSet collections. This makes it ideal when transitioning from an Entity Framework to Entity Framework Core ORM even though most of our application logic might still be embedded within SQL commands.
That is all for today’s post.
I hope you found this post useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
