EF Core has always provided support for inline SQL queries. This means that you could pass a T-SQL query to be executed through the current DbContext. A typical example would look like this:
var term = "some search term");
var blogs = db.Blogs.FromSql($"SELECT * FROM dbo.Blogs WHERE Title = {term}")
.OrderBy(b => b.Url)
.Select(b => b.Url);
This feature is great if you need to call a table function etc. I would urge that this feature is used in moderation and with careful consideration. Calling raw T-SQL requires that developers understand the potential security or performance implications associated with this.
So, while executing T-SQL is a versatile EF Core feature when you need to use it, I wanted to make sure that you understand how it works and how best to use it without opening up your code to SQLi attacks. So, let's break it down and see what happens behind the scenes.
Using T-SQL queries in EF Core - conventional way
First, the query:
var blogs = await db.Blogs.FromSql("Select * from Blogs WHERE Author = {0}", author).ToListAsync();
Running this command would yield the following SQL Query
I've highlighted the WHERE clause in the generated query. Notice how the passed variable is turned into a parameter and the query executes as expected
In most cases, this is how you want to write this query.
Using a string variable to pass the T-SQL query
Where things can go horribly wrong is when you want to pass a string variable that contains the query to the EF DbContext to be executed with FromSql(). Let's take the following query for example:
using (var db = new BloggingContext())
{
var author = "John Smith";
string query = $"Select * from Blogs WHERE Author={author}";
var blogs = await db.Blogs.FromSql(query).ToListAsync();
}
In this instance, EF Core will execute the literal string and the following error will be generated:
Checking the highlighted sections we can see that the string was executed as is with no parameterization ever taking place. If the author variable contained a malicious payload (although by this point our validation and input checks should have caught this and never had allowed the query to execute), then EF would have been unable to help protect against it. Consequently, if you need to execute raw T-SQL through EF Core, you should either use method 1 or take advantage of FormatttableString as per the next section below
Using FormattableString with T-SQL commands
[FormattableString](https://apisof.net/catalog/System.FormattableString" target="_blank) is a new type in .NET that allows the parameterization of T-SQL commands in strings. EF Core's FromSql" also exposes an overload that accepts a FormattableString` in effect sparing you the trouble to manually escape your queries. Neat, ha?
To see this in action jump to the gist below and focus on lines 29-34. The resultant query is going to be exactly the same as in the first example:
As the meme goes, I don't always use FromSql() with raw T-SQL queries, but when I do, I use FormattableString
I hope you found this new feature of EF Core useful and don't forget that with great power comes great responsibility. Don't let your code make the news due to a SQL injection attack.
A gist containing a fully runnable example with the code used in this post is attached below for your perusal: