Table of Contents

Query sources

Query sources at a glance

A query begins with a source, and From has one overload per kind of source. Every overload returns an EntityBuilder<TEntity>, so the full operator set (Where/Join/GroupBy/Having/OrderBy/Limit/Select) applies no matter where the rows come from:

Source Entry point
Mapped entity (its table from metadata) From<TEntity>()
Raw table or CTE name (via TableAlias accessors) From(string)
Subquery / already-composed query From(QueryCommand<TResult>), From(EntityBuilder<TResult>)
Raw SQL fragment FromSql — Raw SQL
Typed CTE From(Cte<TResult>), From(CteReference<TResult>), declared with With / WithRecursive — CTEs
Lazy temporary table From(TempTableSource<TResult>) — AsTempTable
Table-valued function (runtime factory) From(() => ...) — Table-valued functions

Every source also accepts a FromOptions callback (FromOptions) for per-source modifiers such as TABLESAMPLE/SAMPLE and table/index hints (see Table sampling below). The additive CreateQueryBuilder* forwarders mirror these overloads one-to-one (see the API reference). Overrides that change the rendered source of an already-chosen entity are described in Per-query source overrides.

Subquery as source

From(query) also wraps a filtered query, which is the SQL FROM equivalent of a subquery:

var inner = dataContext.From<SimpleEntity>()
    .Where(it => it.Id > 8)
    .Select(it => new { it.Id });

var rows = await dataContext.From(inner)
    .Select(it => new { it.Id })
    .ToListAsync();

-- SQLite
select id
 from (select id
 from simple_entity where (id > 8))

Output:

Id
9
10

Constructing a derived source and materializing it are two different steps. From(inner) composes the derived table; reading its rows requires an outer Select, because the enclosing query can only address the output columns the derived SELECT declares. A bare From(inner).ToList() is accepted only when the inner projection is already a set of resolved column references (a mapped column, a renamed projected member, a computed scalar read through its output name). A projection whose members are row-member expressions — an anonymous new { it.Id } or a DTO new Dto { Id = it.Id } — cannot be reused that way and is refused during preparation with a QueryPreparationException; the rule is the expression shape of the inner projection, not the CLR type of its result. Add the outer selection (From(inner).Select(it => it.Id), as above) to read such a source. This rewrite works with member-init DTO and positional-record projections; the tested constructor-only class projection has a known pre-existing failure (Cannot find inner column Id), so use one of those supported forms instead. See Limitations. See Subqueries for the construction/materialization split.

A derived source may also project a computed scalar instead of a named member. The enclosing query addresses that value through the output column the derived select list exposes, so it can be ordered, filtered and projected like any other column:

var inner = dataContext.From<SimpleEntity>()
    .Where(it => it.Id <= 2)
    .Select(it => it.Id * 2);

var rows = dataContext.From(inner)
    .OrderBy(x => x)
    .Select(x => x * 3)
    .ToList();

-- SQLite
select (c0 * 3)
 from (select (id * 2) as 'c0'
 from simple_entity
 where (id <= 2)) as 't1'
 order by t1.c0

Output: 6, 12.

The generated output name in the sample (c0 here) is how the rendered SQL exposes the computed column; it is not a public naming contract. Read the value through the builder (as above) instead of hard-coding the rendered spelling, which is provider-specific and may change.

Table function as source

dataContext.From(() => ...) uses a runtime table-valued-function factory as the FROM source: the lambda carries the factory call and its arguments (see Table-valued functions). The built-in helpers cover the common set-returning functions; SqlFunctions.Postgres.unnest flattens a PostgreSQL array into one row per element:

var elements = dataContext
    .From(() => SqlFunctions.Postgres.unnest(SqlFunctions.Parameter<long[]>(0)))
    .Select(r => r.Value)
    .ToList(new long[] { 1, 2, 3 });

select unnest as "Value"
 from unnest(@norm_p0) as "t1"

If the fully qualified SqlFunctions.* names feel too verbose, import the surface with using static NextORM.Core.SqlFunctions; and drop the class prefix:

using static NextORM.Core.SqlFunctions;

var elements = dataContext
    .From(() => Postgres.unnest(Parameter<long[]>(0)))
    .Select(r => r.Value)
    .ToList(new long[] { 1, 2, 3 });

SqlFunctions.Postgres.generate_series(start, stop) generates a numeric series the same way. The built-ins are gated by the provider (SupportsTableFunction), and a custom function is declared as a TvfEntityBuilder<T> factory; see Table-valued functions.

Table sampling (TABLESAMPLE)

FromOptions.TableSample adds a TABLESAMPLE modifier to the primary table, so the database reads only a percentage of its rows instead of scanning the whole table. Sampling is a per-query source option, so it is configured in the From call. The percentage must be in (0, 100]; the sampling method defaults to TableSampleMethod.System and an optional seed makes the sample repeatable:

var rows = await dataContext.From<SimpleEntity>(o => o.TableSample(10, TableSampleMethod.System, seed: 42))
    .Select(x => x.Id)
    .ToListAsync();

-- PostgreSQL
select id
 from simple_entity tablesample system (10) repeatable (42)

-- SQL Server
select id
 from simple_entity tablesample (10 percent) repeatable (42)

PostgreSQL supports both System and Bernoulli; SQL Server supports only System. Every other provider throws NotSupportedException when the SQL is built (TableSample and ITableSampleMethods.Render). The modifier applies to the query's primary table only.

Per-query source overrides

By default a query reads the table mapped with EntityMetadataBuilder<T>.Table(...) (or the name passed to From("table")). The With* methods on EntityBuilder<TEntity> change the rendered source for this query only; the metadata and every other query are untouched:

public EntityBuilder<TEntity> WithTableName(string name);
public EntityBuilder<TEntity> WithSchema(string schema);
public EntityBuilder<TEntity> WithDatabase(string database);
public EntityBuilder<TEntity> WithServer(string server);
public EntityBuilder<TEntity> WithTableExpression(string sql);

var rows = await dataContext.From<IOrder>()
    .WithSchema("sales")
    .Select(x => x.Id)
    .ToListAsync();

-- SQL Server / PostgreSQL
select id
 from sales.orders

The schema/database/server qualifiers are provider-specific; the levels a provider cannot express are rejected with NotSupportedException when the SQL is built:

Provider WithSchema WithDatabase WithServer
PostgreSQL schema.table rejected rejected
SQL Server schema.table database.schema.table server.database.schema.table
MySQL / MariaDB db.table (schema = database) db.table rejected
SQLite db.table (attached database) db.table rejected
ClickHouse db.table db.table rejected
In-memory rejected rejected rejected

On MySQL, MariaDB, ClickHouse and SQLite the schema and the database name the same single qualifier, so only one of the two may be set. The parts are quoted with the provider's delimiter when identifier quoting is enabled (see WithQuotedIdentifiers), each part separately: [srv].[db].[sales].[orders], `db`.`orders`, "sales"."orders".

WithTableExpression replaces the table access with a raw SQL expression rendered as a derived source ((sql) AS alias) and cannot be combined with the name qualifiers. The fragment is emitted verbatim, so pass only trusted SQL (the caller owns its validity and injection safety, as with WithSql):

var rows = await dataContext.From<IOrder>()
    .WithTableExpression("select id from orders_2025 union all select id from orders_2026")
    .Select(x => x.Id)
    .ToListAsync();

-- PostgreSQL
select id
 from (select id from orders_2025 union all select id from orders_2026) as "t1"

The override is part of the plan-cache key, so two queries that differ only in the override never share a cached plan; without an override the generated SQL is exactly as before. The in-memory provider has no SQL source to rewrite and rejects every override.

Edit this page