skip to content

In EF Core 10, how does FromSql run a raw SQL query with a user-supplied value, and what can you compose on top of it?

level: juniorimportance: must knowfreq 58%

answer

  1. starts from a DbSet<TEntity>
  2. takes a FormattableString, not a string
  3. each hole becomes a DbParameter (@p0)
  4. your SQL becomes a subquery for LINQ
  5. must return every mapped column

basics

~20 s

Call db.Readings.FromSql($"SELECT ... WHERE "DeviceId" = {deviceId}"): FromSql takes a FormattableString and sends each interpolated value as a DbParameter, never as SQL text. LINQ after it wraps your SQL as a subquery, and results are tracked entities.

solid answer

~40 s

`FromSql` is an extension on `DbSet<TEntity>` that takes a `FormattableString`. Although it looks like string interpolation, EF never splices the values into the text: each `{value}` hole becomes a `DbParameter` named `@p0`, `@p1`, and so on, so the value cannot change the statement. The SQL must return **every column** the entity maps, with the mapped column names, and cannot return related data itself. If the SQL is composable — a plain `SELECT`, or a PostgreSQL function in the `FROM` clause — I can add `Where`, `OrderBy`, `Take` or `Include` after it, and EF wraps my SQL as a subquery. Results follow normal tracking rules, so entities are tracked unless I add `AsNoTracking()`. `FromSql` can only start from a `DbSet`, not from an arbitrary LINQ query.

code

csharp · 9 lines
csharp
var since = DateTime.UtcNow.AddHours(-1);
var hot = await db.SensorReadings
    .FromSql($"""
        SELECT * FROM "SensorReadings"
        WHERE "RecordedAt" >= {since}
        """)
    .AsNoTracking()
    .Where(r => r.Value > 80)
    .ToListAsync(ct);

go deeper

for a junior

Remember that FromSql on a DbSet takes an interpolated string whose values EF turns into SQL parameters, so user input stays out of the SQL text.

for a middle

Explain how EF wraps the SQL as a subquery to compose LINQ, which SQL can be composed, and the column-name and all-columns rules.

for a senior

Decide when raw SQL earns its place over LINQ, keep composable statements, and check that tracking and query filters still behave as callers expect.

for a principal

Set rules for where raw SQL may live in the codebase and how it is reviewed and tested against provider changes.

## The API An IoT platform stores sensor readings in PostgreSQL. Most queries are LINQ, but a hand-tuned statement with a PostgreSQL-specific clause is easier to write in SQL. EF Core's entry point is `FromSql`, an extension method on `DbSet<TEntity>` in `RelationalQueryableExtensions`: ```csharp var deviceId = request.DeviceId; // comes from the caller var readings = await db.SensorReadings .FromSql($"SELECT * FROM \"SensorReadings\" WHERE \"DeviceId\" = {deviceId}") .Where(r => r.Value > 80) .OrderByDescending(r => r.RecordedAt) .Take(100) .ToListAsync(); ``` The parameter type is `FormattableString`, not `string`. The C# compiler hands EF the format text and the argument values separately, so EF can: 1. replace each `{deviceId}` hole with a parameter placeholder (`@p0`, `@p1`, ...); 2. wrap each value in a `DbParameter` sent beside the command; 3. never place the value inside the SQL text. A value like `1; DROP TABLE ...` therefore arrives as a parameter value the database compares against `DeviceId`, not as SQL. If you need control over the parameter's database type or size, you can interpolate a provider `DbParameter` object (for example an `NpgsqlParameter`) instead of a raw value. ## Composing LINQ over it EF treats your SQL as a **subquery** and composes the rest of the LINQ in the database. The query above becomes, roughly: ```sql SELECT s."Id", s."DeviceId", s."RecordedAt", s."Value" FROM ( SELECT * FROM "SensorReadings" WHERE "DeviceId" = @p0 ) AS s WHERE s."Value" > 80 ORDER BY s."RecordedAt" DESC LIMIT @p ``` For that to work the SQL must be valid as a subquery. In practice: - it starts with `SELECT` (or selects from a set-returning function); - it has no trailing semicolon; - it does not rely on features the database forbids in a subquery. A stored-procedure call on SQL Server (`EXECUTE dbo.Something`) cannot be composed; putting `AsAsyncEnumerable()` right after `FromSql` stops EF from trying. ## Rules the SQL must follow | Rule | Why | |---|---| | Return a column for **every** mapped property | EF materializes full entities | | Use the **mapped column names** | Matching is by column name, not by property name | | No related data in the SQL | Add `Include` after `FromSql` instead | | Start from a `DbSet` | `FromSql` is defined on `DbSet<TEntity>` only | ## Tracking Rows returned as entity types follow the same rules as any EF query: they are **tracked by default**, so you can modify a returned reading and call `SaveChangesAsync`. Add `AsNoTracking()` for read-only use. Any global query filter on the entity type is applied on top of your SQL too. ## Mistakes interviewers listen for - **Building the string first.** `var sql = $"... {deviceId}"; FromSql(sql)` does not compile, because `sql` is a `string`. Switching to `FromSqlRaw` to make it compile reintroduces injection; pass the interpolated literal directly instead. - **Quoting the hole.** Writing `'{name}'` inside the SQL wraps the parameter placeholder in quotes, so the database compares against the literal text of the placeholder. Leave holes unquoted; the parameter carries its own type. - **Selecting a subset of columns.** `SELECT "Id", "Value"` fails to materialize a full `SensorReading`. Return every mapped column, or switch to `Database.SqlQuery<T>` with a smaller result type. - **Forgetting that LINQ composes on the server.** A `Where` after `FromSql` is not a client-side filter; it becomes part of the SQL around yours, which is usually what you want for performance. ## FromSql and its relatives - `FromSql` is the current, recommended name. `FromSqlInterpolated` does the same thing and remains from earlier releases. - `FromSqlRaw` takes a plain `string` and an optional parameter array; it exists for SQL you must build dynamically and is where injection risk comes back. - For results that are not entities, `Database.SqlQuery<T>` plays the same role.

  • Why does FromSql fail when the SQL selects only the Id and Value columns of SensorReadings?
    `FromSql` materializes full entities, so the result set must contain a column for every mapped property, named as the property is mapped. Missing columns make materialization fail. To return a subset, use `Database.SqlQuery<T>` with an unmapped type, or project with `Select` after a full `FromSql`.
  • Can you call FromSql on db.SensorReadings.Where(r => r.DeviceId == 3)?
    No. `FromSql` is defined on `DbSet<TEntity>` only; the raw SQL must be the root of the query. Put the filter in the SQL, or start with `FromSql` on the `DbSet` and add `Where` afterwards, which EF composes over your SQL as a subquery.

  • FromSql pastes the interpolated value into the SQL text.
  • FromSql results are never tracked by the context.
  • Columns are matched to properties by C# property name.
  • FromSql can be appended to any IQueryable, like Where.