Entity Framework Core - FromSqlRaw Vs FromSqlInterpolated

Entity Framework core includes two extension methods to execute raw sql queries - FromSqlRaw Vs FromSqlInterpolated. The post is intended to highlight the difference between two.

When it comes to executing raw SQL queries, Entity Framework Core provides us with two extension methods

• FromSqlRaw
• FromSqlInterpolated

There is a slight difference between these options, if not understood, can expose an application to SQL injection attack.

Let's understand the difference with some examples. The requirement is to execute a SQL query with one parameter - album title.

Approach #1

Pass query and parameters to FromSqlRaw method

[HttpGet]
[Route("albums/{title}")]
public Album Get(string title) {
    var albums = _dbContext.Album.FromSqlRaw < Album > ("SELECT AlbumId, Title, ArtistId FROM Album WHERE Title = {0}", title);
    return albums.SingleOrDefault();
}

And here is the generated SQL query. It's parameterized and safe.

SELECT "a"."AlbumId", "a"."ArtistId", "a"."Title"
FROM (
    SELECT AlbumId, Title, ArtistId FROM Album WHERE Title = @p0
) AS "a"

Approach #2

Use FromSqlRaw with interpolated syntax (to make the query more readable) -  vulnerable for SQL injection attack

[HttpGet]
[Route("albums/{title}")]
public Album Get(string title) {
    var albums = _dbContext.Album.FromSqlRaw < Album > ($ "SELECT AlbumId, Title, ArtistId FROM Album WHERE Title = '{title}'");
    return albums.SingleOrDefault();
}

And here is the generated SQL query. It's NOT parameterized and hence UNSAFE.

SELECT "a"."AlbumId", "a"."ArtistId", "a"."Title"
FROM (
    SELECT AlbumId, Title, ArtistId FROM Album WHERE Title = 'Facelift'
) AS "a"

Approach #3

Use FromSqlInterpolated method to execute the query. As the method name indicates, the method supports interpolated syntax by default. And the best thing - It parameterizes the query automatically.

[HttpGet]
[Route("albums/{title}")]
public Album Get(string title) {
    var albums = _dbContext.Album.FromSqlInterpolated < Album > ($ "SELECT AlbumId, Title, ArtistId FROM Album WHERE Title = {title}");
    return albums.SingleOrDefault();
}

And the generated SQL query is parameterized.

SELECT "a"."AlbumId", "a"."ArtistId", "a"."Title"
FROM (
    SELECT AlbumId, Title, ArtistId FROM Album WHERE Title = @p0
) AS "a"

Hope this makes sense.

The bottom line is this -  use FromSqlInterpolated over FromSqlRaw in case when you prefer interpolated syntax of SQL query

Note
There is one more method available in few .net core versions - FromSql, which I haven't covered in the post because the method is deprecated and removed in latest versions of .Net core

Your comments are always welcome :)