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?
answer
- starts from a DbSet<TEntity>
- takes a FormattableString, not a string
- each hole becomes a DbParameter (@p0)
- your SQL becomes a subquery for LINQ
- must return every mapped column
basics
~20 sCall 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 linesvar 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
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.
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.
Decide when raw SQL earns its place over LINQ, keep composable statements, and check that tracking and query filters still behave as callers expect.
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.