In EF Core 10, what does Database.SqlQuery<T> return for scalars and for unmapped classes, and why must a composed scalar column be named Value?
answer
- on DatabaseFacade, not on a DbSet
- scalar or a class outside the model
- composition wraps SQL: column "Value"
- unmapped types since EF Core 8
- results never tracked
basics
~20 sDatabase.SqlQuery<T> runs parameterized SQL and maps each row to a scalar or to any class with a property per column, even one outside the model. It returns an untracked IQueryable<T>; composed scalar queries read a column named Value.
solid answer
~40 s`Database.SqlQuery<T>` is an extension on `DatabaseFacade` that takes a `FormattableString`, so values are parameters, and returns `IQueryable<T>`. For a scalar type like `int` or `DateTime` each row's single column becomes one element. For a class that is **not** in the model, supported since EF Core 8, every column must match a property; the class has no key and no relationships, and results are never tracked. Because the result is an `IQueryable`, I can compose LINQ: EF wraps my SQL as a subquery and refers to its output column, which for a scalar query must be named `Value` (`SELECT "DeviceId" AS "Value" ...`). `SqlQueryRaw<T>` is the string-based variant for dynamic SQL. It replaces the old habit of declaring a keyless entity type only to read a report.
go deeper
Know that Database.SqlQuery<T> returns scalars or simple result objects from SQL, with interpolated values sent as parameters.
Explain composition over SqlQuery, the Value column rule for scalars, and the requirements for unmapped result types.
Choose between FromSql, SqlQuery<T> and keyless entity types for reporting paths, keeping the model lean and results untracked.
Decide where reporting SQL lives, in the EF model, in ad hoc queries or in a separate read store, as the number of reports grows.
## What it is for `FromSql` returns entity types that are part of the model. Reports on the IoT platform often return something else: a list of device ids, a count, or a row combining columns from several tables. `Database.SqlQuery<T>`, an extension on `DatabaseFacade` in `RelationalDatabaseFacadeExtensions`, covers those cases without adding anything to the model. ## Scalars ```csharp var silent = await db.Database .SqlQuery<int>($""" SELECT d."Id" FROM "Devices" d WHERE NOT EXISTS (SELECT 1 FROM "SensorReadings" r WHERE r."DeviceId" = d."Id" AND r."RecordedAt" > {since}) """) .ToListAsync(ct); ``` - The argument is a `FormattableString`, so `since` becomes `@p0`. - Each row's single column becomes one `int`. - Any scalar type the provider supports works; a type it does not support can be added with a value converter in pre-convention configuration. ## Composing over a scalar query `SqlQuery<T>` returns `IQueryable<T>`, so further LINQ is translated into SQL around yours. EF wraps your statement as a subquery and refers to its output column by a fixed name, **`Value`**: ```csharp var busy = await db.Database .SqlQuery<int>($"""SELECT "DeviceId" AS "Value" FROM "SensorReadings" """) .Distinct() .Where(id => id > 100) .ToListAsync(ct); ``` Without the `AS "Value"` alias the composed SQL refers to a column that does not exist. If you only materialize the results without composing, the column name does not matter. ## Unmapped classes Since **EF Core 8**, `T` can be a class that is not in the model: ```csharp public sealed record DeviceDailyStat(int DeviceId, DateOnly Day, double AvgValue, int Samples); var stats = await db.Database .SqlQuery<DeviceDailyStat>($""" SELECT "DeviceId", date("RecordedAt") AS "Day", avg("Value") AS "AvgValue", count(*)::int AS "Samples" FROM "SensorReadings" WHERE "RecordedAt" >= {from} GROUP BY "DeviceId", date("RecordedAt") """) .ToListAsync(ct); ``` Rules: 1. The type needs a property, or constructor parameter, for **every column** in the result. 2. Common mapping constructs work, such as parameterized constructors and mapping attributes. 3. The type has **no key** and **no relationships**; types with relationships must be mapped in the model. 4. Results are **not tracked**, so they cannot be saved back through the context. ## Choosing between the raw-SQL readers | Need | API | |---|---| | Rows of a mapped entity, maybe to modify | `DbSet<T>.FromSql` | | Scalars or a DTO-shaped report | `Database.SqlQuery<T>` | | The same, with SQL built dynamically | `Database.SqlQueryRaw<T>` with a parameter array | | A read-only view used in many places | a keyless entity type mapped with `ToView` | ## Common mistakes - Expecting `SqlQuery<T>` results to be tracked and saved; they are plain objects. - Composing over a scalar query without the `Value` alias, which produces SQL referring to a column that does not exist. - Using `SqlQueryRaw<T>` with concatenated input; it has the same injection risk as `FromSqlRaw`, and the EF1002 and EF1003 analyzers flag it the same way. - Selecting more columns than the result type has properties for, or naming them differently; the mapping is by column name. ## Version note Returning unmapped classes from `SqlQuery<T>` arrived in EF Core 8. Before that, teams declared a keyless entity type in `OnModelCreating` just to read one report, which bloated the model. On EF Core 10 the unmapped type is the simpler choice unless the same shape is reused across many queries.
- Can you call SaveChanges to persist an edited DeviceDailyStat returned by SqlQuery<T>?No. An unmapped type has no key and is not part of the model, so EF never tracks the results and has no table to write them to. Change the underlying rows with `ExecuteUpdateAsync`, `ExecuteSqlAsync` or a tracked entity query instead.
- When would you still map a keyless entity type instead of using SqlQuery<T>?When the shape comes from a database view or a fixed query used in many places, or you want LINQ to start from a `DbSet` with filters and navigations to regular entities. A keyless type mapped with `ToView` makes the view a first-class query root, while `SqlQuery<T>` suits one-off reports.
- SqlQuery<T> only works for types registered in OnModelCreating.
- Results of SqlQuery<T> are tracked like entities.
- SqlQuery<T> concatenates interpolated values into the SQL text.
- Composing LINQ over SqlQuery<int> works whatever the column is named.
Assumes EF Core 10; SqlQuery<T> with unmapped (non-model) types has been available since EF Core 8, earlier versions handled only scalars there.