Table of Contents

Projections

Rules that apply to every projection:

  • The lambda's shape decides the select list. Columns that are not projected are not read.
  • A projected member whose name matches the mapped column name (case-insensitive) is emitted without an alias. A renamed or calculated member is aliased: as 'Calc' on SQLite, as [Calc] on SQL Server, as "Calc" on PostgreSQL.
  • Constructor arguments, tuple items and member-initialiser assignments all map to select-list entries; the runtime materialiser builds the object from the reader.
  • On the in-memory provider no SQL exists; the same expression is compiled and run over the in-memory data set.

The Output: tables below show the rows returned by each example against the integration-test seed data: simple_entity holds ids 1-10 and complex_entity three rows (tests/nextorm.integration.tests/Providers/TestProviders/SqliteTestProvider.cs).

Anonymous type

var rows = await dataContext.From<SimpleEntity>()
    .Select(entity => new { entity.Id })
    .ToListAsync();

select id
 from simple_entity

Output:

Id
1
2
3
4
5
6
7
8
9
10

entity.Id maps to the id column of simple_entity, so no alias is emitted.

Modified and calculated columns

A member can be computed from other columns or constants:

var rows = await dataContext.From<SimpleEntity>()
    .Select(entity => new { Id = entity.Id + 1 })
    .ToListAsync();

select (id + 1) as 'Id'
 from simple_entity

Output:

Id
2
3
4
5
6
7
8
9
10
11

The arithmetic expression is parenthesised and, because it is not a plain column, it is aliased with the projected member name. Naming the member keeps the alias stable for an enclosing query:

var rows = await dataContext.From<ComplexEntity>()
    .Select(it => new { it.Id, Calc = it.Id + 1 })
    .ToListAsync();

select id, (id + 1) as 'Calc'
 from complex_entity

String members concatenate with the provider's concat operator (|| on SQLite and PostgreSQL, + on SQL Server):

var rows = await dataContext.From<ComplexEntity>()
    .Select(it => new { it.Id, Display = it.String + "/" + it.RequiredString })
    .ToListAsync();

-- SQLite
select id, ((somestring || '/') || requiredstring) as 'Display'
 from complex_entity

Output:

Id Display
1 dadfasd/sdf
2 xxx/asdfgoi
3 null

Columns by name

A mapped entity gives access to its columns through the properties declared on it. When you need an undeclared column — for example in a wide ClickHouse table — use SqlFunctions.Column<T>:

var rows = await dataContext.From<SimpleEntity>()
    .Select(entity => new { Region = SqlFunctions.Column<ulong>(entity, "region_id") })
    .ToListAsync();

-- SQLite
select region_id as 'Region'
 from simple_entity

The first argument must be the query lambda parameter (a source), and the name is a raw database column name: it is always safely quoted by the provider, independently of the context's UseQuotedIdentifiers option (`region_id` on ClickHouse and MySQL, "region_id" on PostgreSQL and SQLite, [region_id] on SQL Server). A dot is a literal name character — not a schema or alias qualifier — and the name is never interpreted as SQL, so manual pre-quoting is not supported: Column(t, "[1]") is the literal three-character name [1] (rendered [[1]]] on SQL Server), while the column named 1 is Column(t, "1"). The value is materialized as T, so the type must be one the row reader supports. Unlike a mapped member, the column is not validated against entity metadata: a misspelled name raises an error on the database side.

The same accessor works in a predicate and on a joined projection:

var rows = await dataContext.From<SimpleEntity>()
    .Join<ComplexEntity>((s, c) => s.Id == c.Id)
    .Where(p => SqlFunctions.Column<long>(p.Item2, "region_id") > 0)
    .Select(p => new { p.Item1.Id, Region = SqlFunctions.Column<long>(p.Item2, "region_id") })
    .ToListAsync();

For a source that has no entity type at all (From("table")), columns are read through TableAlias — see Joins and CTE. The in-memory provider has no column-name concept and rejects SqlFunctions.Column.

DTO

A non-anonymous type is projected through its constructor:

public class SimpleEntityDto(int id)
{
    public int Id { get; } = id;
}

var rows = await dataContext.From<SimpleEntity>()
    .Select(entity => new SimpleEntityDto(entity.Id))
    .ToListAsync();

select id
 from simple_entity

Output:

Id
1
2
3
4
5
6
7
8
9
10

A DTO projection is a direct-read projection: it binds the mapped columns of the entity you query. It does not change the rule for a derived source read without an outer Select — a DTO used as the inner projection of From(query) is refused exactly like the anonymous form when its members have to be re-translated, because the restriction is the projection's expression shape, not the type being anonymous. Name the columns with an outer Select when the DTO is the inner projection (see Subqueries). 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.

Record

Positional records are projected by their constructor too:

public record SimpleEntityRecord(long Id);

var rows = await dataContext.From("simple_entity")
    .Select(tbl => new SimpleEntityRecord(tbl.GetInt64("id")))
    .ToListAsync();

select id
 from simple_entity

Output:

Id
1
2
3
4
5
6
7
8
9
10

tbl.GetInt64("id") already has the record member's type, so no conversion is added. When the projected type is wider or narrower than the source column, nextorm renders the conversion as cast(...).

Tuple

var rows = await dataContext.From("simple_entity")
    .Select(tbl => new Tuple<long>(tbl.GetInt64("id")))
    .ToListAsync();

select id
 from simple_entity

Output:

Item1
1
2
3
4
5
6
7
8
9
10

Tuple.Create is the row-value constructor surface: it renders a tuple column where the provider supports row values, and remains subject to the provider and context restrictions (for example, SQLite accepts it only as a comparison operand, not as a SELECT projection). A ValueTuple is different: a top-level generic ValueTuple.Create(...) factory call and an explicit new ValueTuple<...>(...) construction are not supported projections. Both are rejected during query preparation with a NotSupportedException that names the projection type and the Select position, before any SQL is rendered or a database is touched — not as a row-materializer failure. Project an explicit DTO or anonymous shape instead; nested tuple members keep their own existing restrictions.

Member initialiser

Instead of a constructor, a projection can use an object initialiser:

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

select id
 from simple_entity

Output:

Id
1
2
3
4
5
6
7
8
9
10

Primitive and scalar projection

Select can return a single value instead of a row object:

var ids = await dataContext.From<SimpleEntity>()
    .Where(it => it.Id < 5)
    .Select(it => it.Id)
    .ToListAsync();

select id
 from simple_entity where (id < 5)

Output:

Id
1
2
3
4

A boolean member works the same way:

var flags = await dataContext.From<ComplexEntity>()
    .Where(it => it.Boolean == true)
    .Select(it => it.Boolean)
    .ToListAsync();

select b
 from complex_entity where b = 1

Output:

Boolean
true

When that single column is a byte[] or string, it can be read as a stream instead of being materialised: ToStream/ToTextReader (and their async forms) open the provider's LOB accessors for that one column — see Streaming large objects.

An enum-typed column is the one scalar shape that needs a value converter: a bare enum or nullable-enum projection without one (Select(it => it.State), Select(it => it.MaybeState)) is refused during preparation with NotSupportedException — "Enum scalar projections without a value converter are not supported in a server-side query." — instead of falling through to row materialization. Map the column through a converter (see Value converters and JSON columns) and its scalar projection behaves like any other converted column. The refusal applies to server-side query preparation only: the in-memory provider materializes the projection from the source objects, so an enum scalar keeps working there.

Whole-entity projection

A mapped single source can be projected as a whole entity with Select(it => it). The statement reuses the entity's ordinary mapped column selection, so it renders the explicit mapped columns — never * — and materialises the rows like a projection-less read of the same source:

var rows = await dataContext.From<SimpleEntity>()
    .Where(it => it.Id < 3)
    .Select(it => it)
    .ToListAsync();

select id
 from simple_entity where (id < 3)

An entity with several columns renders every mapped column in its declared order. The rendered text is the same CommandText the query produces through ToCommand(), so the identity form is not a separate projection pipeline: it resolves through the same entity select list and its cache entry.

Whole-object shapes that are not a mapped identity fail closed during preparation instead of rendering a malformed statement. A conversion of the row to an unrelated type (Select(it => (object)it)) is refused with DataContextException, and a prepared select list that resolved no columns at all is rejected with BuildSqlCommandException before anything is rendered.

Nested entity and calculated columns over a projection

A QueryCommand<TResult> can itself be used as the source of another query with dataContext.From(query), so an inner projection (including calculated columns) can be read and projected again:

var inner = dataContext.From<ComplexEntity>()
    .Select(it => new { it.Id, Calc = it.String + it.String });

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

-- SQLite: no derived-table alias is required
select id, Calc
 from (select id, (somestring || somestring) as 'Calc'
 from complex_entity)

SQL Server requires the derived table to be aliased, and PostgreSQL likewise:

-- SQL Server
select id, Calc
 from (select id, (somestring + somestring) as [Calc]
 from complex_entity) as [t1]

The enclosing Select is what makes such a read work: the outer query addresses the derived SELECT's output columns, so a named or computed inner column is reused as-is. Reading the same inner query without an outer projection (dataContext.From(inner).ToList()) is accepted only when every inner column is already such a resolved reference; a projection built from row-member expressions (an anonymous new { it.Id } or a DTO new Dto { Id = it.Id }) is refused during preparation with a QueryPreparationException and must be read through an outer Select. That is an expression-shape restriction, not an anonymous-type requirement — see Subqueries.

Collection-valued projection

A projection member can name a declared collection navigation and receive that parent's collection in the result — a collection-valued slot. The direct form projects the member as-is; the adapter form lifts it into a per-parent pipeline:

// direct: the whole per-parent collection
var posts = ctx.From<Post>()
    .OrderBy(p => p.Id)
    .Select(p => new { p.Id, p.Comments })
    .ToList();

// adapter: filter, order and page each parent's collection before projecting it
var pageSize = 2;
var digest = ctx.From<Post>()
    .OrderBy(p => p.Id)
    .Select(p => new
    {
        p.Id,
        Recent = p.Comments
            .AsEntityBuilder<Comment>()
            .Where(c => c.Active)
            .OrderBy(c => c.CreatedAt)
            .ThenBy(c => c.Id, OrderDirection.Desc)
            .Limit(pageSize)
            .Select(c => new CommentRow { Id = c.Id, CreatedAt = c.CreatedAt })
            .ToList(),
    })
    .ToList();
  • The relationship must be declared (HasMany/HasManyThrough/[Relationship]); both one-to-many and many-to-many slots are supported, and slots nest through the adapter's Select(...) to any depth with per-level filtering, ordering and paging.
  • Materialization is the eager loading's. The entity graph is loaded with the ordinary split (default) or single-query route — two statements for a depth-one one-to-many slot, one statement in single-query mode — and the projected shape is then computed in memory over the materialized entities. There is no SQL column pruning in this release.
  • Seven destination shapes are accepted: List<T>, T[], IEnumerable<T>, ICollection<T>, IList<T>, IReadOnlyCollection<T>, IReadOnlyList<T>. Any other shape fails closed.
  • The adapter is what makes the parameterless terminals matter. EntityBuilder<TEntity>.ToList()/ToArray() and QueryCommand<TResult>.ToList()/ToArray() exist as parameterless overloads so the chain is expressible in an expression tree (the params ReadOnlySpan<object?> twins cannot be referenced from one).

The operator set, the pre-SQL refusals, the cardinality rule for a projected reference and the v1 limits are documented in Collection-valued projection slots.

Named join aliases

A joined query can name its slots instead of addressing them positionally. A join carries an optional trailing Alias.<Name> argument that names the new slot, and the root source can be named with .WithAlias(Alias.X):

var rows = await dataContext.From<Order>(b => b.Table("orders"))
    .WithAlias(Alias.Order)
    .Join<Person>(people, (p, buyer) => p.Order.BuyerId == buyer.Id, Alias.Buyer)
    .Select(p => new { OrderId = p.Order.Id, Buyer = p.Buyer.Id })
    .ToListAsync();

The named members are expression-only: they exist so a Select/Where expression tree can name the joined table of the slot, and the translator rewrites them to that table. Reading one outside an expression tree — for example materialising p.Buyer directly — throws NotSupportedException by design instead of returning a defaulted value; project the column you need (Select(p => p.Buyer.Id)) instead. A named member can be used anywhere a positional p.Item2.Id would be — in Select, Where and later joins. A purely positional chain uses the accumulator types Projection<T1>..Projection<T1..T8>, whose ItemN members are ordinary properties; a root alias uses Projection<T1> — the only dimension-1 projection, a plain IProjection that is not directly extendable, its slot-1 transition being supplied by the generated positional members — and a named slot is an expression-only property added on top of the same accumulator. See Joins.

Edit this page