Raw SQL in EF Core: FromSqlRaw, FromSqlInterpolated, Keyless Entities and SQL Injection

Version note: The code in this post is written for EF Core 6 and runs unchanged on EF Core 10.

Guess First: How Does This SQL Reach the Database?

We start with hand-written SQL and add LINQ on top of it:

var query = db.Books
    .FromSqlRaw("SELECT * FROM Books")
    .Where(b => b.Stock > 2)
    .OrderBy(b => b.Title);

Console.WriteLine(query.ToQueryString());

Consider three possibilities. EF Core sends your SQL as is and does the filtering and sorting in memory. Or it ignores your SQL and generates its own query. Or it combines the two somehow. Which one is it? The answer comes shortly, but first the question of this post: when LINQ is not enough, or you already have SQL in hand, how do you give that SQL to EF Core, and how do you avoid getting burned while doing it?

For the examples we use two familiar entities from the Bookstore model:

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public List<Book> Books { get; set; } = new();
}

public class Book
{
    public int Id { get; set; }
    public string Title { get; set; } = "";
    public decimal Price { get; set; }
    public int Stock { get; set; }
    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

FromSqlRaw and LINQ on Top

The ToQueryString() output of the opening query is:

SELECT "b"."Id", "b"."AuthorId", "b"."Price", "b"."Stock", "b"."Title"
FROM (
    SELECT * FROM Books
) AS "b"
WHERE "b"."Stock" > 2
ORDER BY "b"."Title"

The right answer is the third. EF Core wraps your SQL as a subquery and adds the Where and OrderBy to the outer SELECT. The filter runs in the database, not in memory. This output is identical on EF Core 6 and EF Core 10.

This composition has a price: EF Core must be able to read the result of your SQL as a Book. In other words, the query has to return every column of the entity, with the same names as in the model. If a column is missing, the query runs but fails while reading the result:

var titles = await db.Books
    .FromSqlRaw("SELECT Id, Title FROM Books")
    .ToListAsync();

InvalidOperationException: The required column 'AuthorId' was not present in the results of a 'FromSql' operation.

If you only need a few columns, either return all of them in the query and project with Select in LINQ, or map the result to a separate keyless entity. The nice side of composition is that you can bring in related data too. Include is added the same way, as a JOIN on top of the subquery:

var withAuthors = db.Books
    .FromSqlRaw("SELECT * FROM Books")
    .Include(b => b.Author);

Console.WriteLine(withAuthors.ToQueryString());

SELECT "b"."Id", "b"."AuthorId", "b"."Price", "b"."Stock", "b"."Title", "a"."Id", "a"."Name"
FROM (
    SELECT * FROM Books
) AS "b"
INNER JOIN "Authors" AS "a" ON "b"."AuthorId" = "a"."Id"

Parameters: FromSqlRaw or FromSqlInterpolated?

The moment you pass a value into the query from outside, things get serious. There are two ways:

var authorId = 1;

var raw = db.Books.FromSqlRaw("SELECT * FROM Books WHERE AuthorId = {0}", authorId);
var interpolated = db.Books.FromSqlInterpolated($"SELECT * FROM Books WHERE AuthorId = {authorId}");

Console.WriteLine(raw.ToQueryString());
Console.WriteLine();
Console.WriteLine(interpolated.ToQueryString());

Both queries produce the same SQL:

.param set p0 1

SELECT * FROM Books WHERE AuthorId = @p0

With the SQLite provider, ToQueryString() shows parameters as .param set lines; on SQL Server they become DECLARE lines. What matters is that the value 1 is not written into the SQL text but travels as a separate @p0 parameter.

In FromSqlRaw the placeholders look like {0}, {1}. This resembles string.Format, but it is not; EF Core does not write the value into those spots, it puts a parameter there. FromSqlInterpolated takes a FormattableString instead. It captures the dollar-sign string's fragments and values separately, without turning it into a plain string, and creates a parameter for each {authorId}. Both are safe. If you want to pass your own parameter object, for example for a named parameter or to set its type, you can also use the provider's DbParameter:

var parameter = new SqliteParameter("authorId", authorId);
var books = await db.Books
    .FromSqlRaw("SELECT * FROM Books WHERE AuthorId = @authorId", parameter)
    .ToListAsync();

EF Core 7 brought a third method called FromSql. It does the same job as FromSqlInterpolated with a shorter name. It does not exist in EF Core 6; we will see it in the version posts.

SQL Injection: Same Line, Two Different Results

The danger lies in how similar the two methods look and sound. Look at this code:

// title comes from the user: "x' OR '1'='1"
var books = await db.Books
    .FromSqlRaw($"SELECT * FROM Books WHERE Title = '{title}'")
    .ToListAsync();

Console.WriteLine(books.Count);

3

There is no book with that title, yet the query returned all three books. Here is why: FromSqlRaw expects a string parameter. Because of the $ in front, C# concatenated the string before it ever entered the method, and EF Core received ready-made SQL text: ... WHERE Title = 'x' OR '1'='1'. EF Core had no chance to create a parameter, because it never saw the value. The text the user typed is now part of the SQL. This time we broke the condition; next time it could be '; DROP TABLE Books; --.

Writing the same line with FromSqlInterpolated:

var safe = await db.Books
    .FromSqlInterpolated($"SELECT * FROM Books WHERE Title = {title}")
    .ToListAsync();

Console.WriteLine(safe.Count);

0

The value went as a parameter, the database looked for a book titled x' OR '1'='1 and found none. The correct result.

EF Core 6 gives no warning at all for the dangerous line. When the same code is compiled with EF Core 10, this warning appears:

warning EF1002: Method 'FromSqlRaw' inserts interpolated strings directly into the SQL, without any protection against SQL injection. Consider using 'FromSql' instead, which protects against SQL injection, or make sure that the value is sanitized and suppress the warning.

This analyzer arrived in EF Core 8; EF Core 10 goes one step further and also reports EF1003 for string concatenation such as "..." + title + "...". The warnings help, but the rule is simple: never write a value that comes from the user into the SQL text yourself. Use FromSqlRaw only when you need to build the SQL itself dynamically, for example to choose a column name, and pick that fragment from your own allow list.

ExecuteSqlRaw and ExecuteSqlInterpolated

For commands that return no rows you go through Database, not a DbSet. Let's add stock to all books of an author:

var dune = await db.Books.SingleAsync(b => b.Title == "Dune"); // Stock = 5, now tracked

var authorId = 1;
var amount = 10;
var affected = await db.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Books SET Stock = Stock + {amount} WHERE AuthorId = {authorId}");

Console.WriteLine($"Affected rows: {affected}");
Console.WriteLine($"Tracked stock: {dune.Stock}");

await db.Entry(dune).ReloadAsync();
Console.WriteLine($"After reload: {dune.Stock}");

Affected rows: 2
Tracked stock: 5
After reload: 15

The method returns the number of affected rows: two books were updated. But notice that the dune we tracked earlier still says 5. The ExecuteSql family bypasses the change tracker entirely; the context has no idea what changed in the database. If you keep using this entity in the same context, refresh it with Reload or do not track it at all. Remember this too: these methods do not open a transaction on their own. If you need to keep several commands together, use the BeginTransaction we saw in earlier posts. Concurrency tokens are not updated by these commands either.

For bulk updates like this, EF Core 7 introduced ExecuteUpdate and ExecuteDelete; they do the same job with LINQ, without writing SQL. They do not exist in EF Core 6; we will see them in the version posts.

Keyless Entities and View Mapping

Reporting queries often do not correspond to an entity. Let's keep a result like book count and total stock per author in a view:

await db.Database.ExecuteSqlRawAsync(@"
    CREATE VIEW AuthorStats AS
    SELECT a.Name AS AuthorName, COUNT(b.Id) AS BookCount, COALESCE(SUM(b.Stock), 0) AS TotalStock
    FROM Authors AS a
    LEFT JOIN Books AS b ON b.AuthorId = a.Id
    GROUP BY a.Id, a.Name");

Here we created the view by hand after EnsureCreated. In a real project you put this SQL into a migration's Up method with migrationBuilder.Sql(...). The class that carries the result has no key:

public class AuthorStats
{
    public string AuthorName { get; set; } = "";
    public int BookCount { get; set; }
    public int TotalStock { get; set; }
}

In the context we register it as a keyless entity and map it to the view:

public DbSet<Author> Authors => Set<Author>();
public DbSet<Book> Books => Set<Book>();
public DbSet<AuthorStats> AuthorStats => Set<AuthorStats>();

protected override void OnConfiguring(DbContextOptionsBuilder options)
    => options.UseSqlite("Data Source=bookstore.db");

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<AuthorStats>()
        .HasNoKey()
        .ToView("AuthorStats");
}

HasNoKey() tells EF Core "this type has no primary key, do not try to tell rows apart"; the data annotation equivalent is [Keyless]. ToView("AuthorStats") does two things: it uses this view in queries, and it tells EnsureCreated and migrations not to create a table for this type. Indeed, the GenerateCreateScript() output only has the Authors and Books tables. Now you can write LINQ against the view like any other DbSet:

var busyAuthors = db.AuthorStats
    .Where(s => s.BookCount > 1)
    .OrderBy(s => s.AuthorName);

Console.WriteLine(busyAuthors.ToQueryString());

foreach (var stats in await busyAuthors.ToListAsync())
    Console.WriteLine($"{stats.AuthorName}: {stats.BookCount} books, {stats.TotalStock} in stock");

SELECT "a"."AuthorName", "a"."BookCount", "a"."TotalStock"
FROM "AuthorStats" AS "a"
WHERE "a"."BookCount" > 1
ORDER BY "a"."AuthorName"
Frank Herbert: 2 books, 7 in stock

Keyless entities are read-only. The change tracker cannot track them, because there is no key to tell one row from another. If you try to add one:

db.AuthorStats.Add(new AuthorStats { AuthorName = "Grace" });

InvalidOperationException: Unable to track an instance of type 'AuthorStats' because it does not have a primary key. Only entity types with a primary key may be tracked.

You do not have to tie a keyless entity to a view. Without ToView, you can map the result of a one-off SQL query to the same type with db.Set<AuthorStats>().FromSqlRaw(...). Raw SQL queries for types that are not in the model at all (SqlQuery<T>) arrived with EF Core 8.

Calling Stored Procedures (SQL Server)

SQLite has no stored procedures; this section is for SQL Server, and to actually run it you need a SQL Server, for example the mcr.microsoft.com/mssql/server image in Docker. But you do not need a database to see how the SQL is generated. ToQueryString() does not open a connection:

var authorId = 1;

var books = db.Books
    .FromSqlInterpolated($"EXECUTE dbo.GetBooksByAuthor {authorId}");

Console.WriteLine(books.ToQueryString());

DECLARE p0 int = 1;

EXECUTE dbo.GetBooksByAuthor @p0

If the procedure returns all columns of the entity, this query gives you a list of Book directly. However, an EXECUTE command cannot be wrapped as a subquery. If you try to add LINQ on top, EF Core catches it before running the query:

var cheapBooks = db.Books
    .FromSqlInterpolated($"EXECUTE dbo.GetBooksByAuthor {authorId}")
    .Where(b => b.Price < 10);

Console.WriteLine(cheapBooks.ToQueryString());

InvalidOperationException: 'FromSqlRaw' or 'FromSqlInterpolated' was called with non-composable SQL and with a query composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.

This message is from EF Core 6. On EF Core 10 the same message starts with 'FromSql' or 'SqlQuery'. EF Core refuses to wrap a command starting with EXECUTE in a subquery before it even goes to the database. The fix is in the message itself: call AsEnumerable() right after FromSqlInterpolated(...) and let the rest run in memory. For procedures that return no rows, ExecuteSqlInterpolated($"EXECUTE dbo.RestockBooks {authorId}, {amount}") is enough.

Common Pitfalls

FromSqlRaw with $. The most dangerous and the easiest to make; we saw it above. If you see FromSqlRaw($ or ExecuteSqlRaw($ in a code review, stop and ask.

Fragile queries with SELECT *. We used SELECT * in the examples for brevity. But think about what happens when a column outside the model is added to the table or the column order changes. In real code, list the columns explicitly.

Provider-specific SQL. The moment you write raw SQL, you lose database independence. A query that works on SQLite may not work on SQL Server. If you run your tests against a different provider, that is where you notice these differences; we will come back to this in the testing post.

Mixing tracked data with raw commands. After ExecuteSqlInterpolated, the entities in the context are stale, and a following SaveChanges can write the old values back. Either run the raw command in a separate context or reload the affected entities.

A Small Experiment

★☆☆ Write the query FromSqlRaw("SELECT * FROM Books WHERE Stock > {0}", 2), add Select(b => b.Title) on top and look at how the subquery is wrapped in the ToQueryString() output.

★★☆ Compile the injection example in the EF Core 10 project and see the EF1002 warning. Then change the line to concatenation, "... WHERE Title = '" + title + "'", and note the code of the warning you get. Finally, fix both with FromSqlInterpolated.

★★★ Write a view called OutOfStockBooks that returns books with a stock of 0 together with the author's name. Map it to a keyless entity and fetch the first 5 rows ordered by AuthorName with LINQ. Look at where the LIMIT ends up in the generated SQL.

Up to this post we have always created contexts ourselves with new BookstoreContext(). In a web application, who does this, when, and how long the context lives is a topic of its own. In the next post we connect EF Core to ASP.NET Core: AddDbContext, the scoped lifetime, AddDbContextFactory, AddDbContextPool and background services.