Pure.RelationalSchema.Storage.PureQL.Projection 0.1.0-preview.6.1.0

Pure.RelationalSchema.Storage.PureQL.Projection

Compiles PureQL queries over relational schema datasets into one LINQ expression tree: a PureQLQuery from PureQL.CSharp.Model becomes an IStoredTableDataSet whose IQueryable<IRow> tree is handed, when enumerated, to the LINQ provider of the stored tables it reads (which translates it to SQL), and runs in memory when the provider cannot run it or the tables are held in memory.

Overview

Pure.RelationalSchema.Storage.PureQL.Projection is an interpreter for the PureQL specification 0.1.0-preview.2.0.1 over the Pure relational storage model. Given a collection of IStoredSchemaDataSet objects and a PureQLQuery, it produces an IStoredTableDataSet whose rows are the query's result under the specification's semantics.

The query is compiled when the projection is built to a single IQueryable<IRow> expression tree made of standard query operators over one root per stored table, ending in the Select that builds the result rows (see Expression tree). Enumerating the projection hands the query below the result rows to the provider of the stored tables it reads, so that a provider that translates it (to PostgreSQL) runs it as one SQL statement and the projection only maps its values to result rows (see Running a query). The tree runs in memory, with LINQ to Objects, only over tables held in memory, or when the provider refuses the query.

Public API

PureQLProjection

The entry point. Implements IStoredTableDataSet, IAsyncEnumerable<IRow> and IQueryable<IRow>.

What is supported

Every clause and operator of the specification: from and joins (inner, left, right, full, aliases and self-joins), where, groupBy over any row expression with keys referenced by alias, having, select, aggregate (total queries), orderBy over any expression, distinctBy, pagination, and subqueries read through from, join and in. All operators in the row, projection, group and total contexts are supported, including aggregates with predicate and over, and comparisons, min and max over every type.

A plain query returns one row per row left by where, a total query exactly one row (even over no rows) and a grouped query one row per group. distinctBy runs after orderBy and keeps, of the rows equal on the listed columns, the first in order.

The semantics are the specification's: null equals null and sorts first, ordering comparisons against null are false, lifted operators are null on a null operand, integer is a checked 64-bit integer, decimal is exact (System.Decimal), strings compare by code point, a datetime is an instant written in UTC, and seconds and time / datetime averages round half away from zero to the microsecond. Overflow, division by zero and dates outside the calendar fail the query with the corresponding .NET exception.

The checks the specification leaves to the interpreter run when the projection is built, and a failing query raises ArgumentException: entities, subqueries, sources and fields exist; each field declares its column's type and, after outer joins, its nullability; group key references name a key and repeat its type; source names, group key aliases, column aliases and subquery names are unique; distinctBy lists only column aliases; a subquery reads only subqueries declared before it; a bound parameter's literal has the parameter's type, and a bound pagination is in range.

Parameters

Parameters are bound by name to an IParameter of Pure.RelationalSchema.Storage.PureQL.Projection.Parameters.Abstractions, implemented by Pure.RelationalSchema.Storage.PureQL.Projection.Parameters. They are passed to the constructor that takes parameters. A Pure parameter's Type.Name is a PureQL type, Type.Nullable makes it that type's typed null, and its Value is the invariant text a stored cell holds, parsed as a cell is (a datetime without an offset is UTC). An IListParameter binds a list parameter with its Items, each a scalar parameter of the item type; its Value is not read.

The projection raises ArgumentException when it is built, for every parameter of the document, read or not, in any of these cases:

• a parameter is given no value; with the constructors without parameters, that is every parameter
• the value has another type, including a type name that is not a PureQL type
• a typed null is given for a parameter that is not nullable
• a Value is not a value of its type, or is date math that is malformed for its type or out of range
• a list parameter is given a parameter that is not an IListParameter, or a list item of another type or a typed null

Names the query does not use are ignored.

IStoredTableDataSet result = new PureQLProjection(
    datasets,
    query,
    new Dictionary<string, IParameter>
    {
        ["min_total"] = new DecimalParameter(new Decimal(100m)),
        ["page_size"] = new IntegerParameter(new Long(20)),
        ["since"] = new NullableDateParameter(),
        ["statuses"] = new StringListParameter([new String("paid"), new String("shipped")]),
    }
);

Date math

The value of a date, time or datetime parameter may be date math, the relative values of Elasticsearch and Grafana, which the current and offset parameters of Pure.RelationalSchema.Storage.PureQL.Projection.Parameters write: now, then at most one part per unit in the order y (years), M (months), d (days), h (hours), m (minutes) and s (seconds), each +N or -N, all of one sign, such as now-10d or now-1M-5d. A date takes y, M and d, a time h, m and s, a datetime all six. Any other text of these types is parsed as a cell is; a string parameter starting with now is its text.

Its value is the one at the instant a run starts. Each enumeration reads the projection's clock once, TimeProvider.GetUtcNow() truncated to the microsecond, and binds every date math parameter at that instant before the query goes to the provider or runs in memory, so a projection built once and run tomorrow has tomorrow's dates. Years and months move the instant by calendar months together (now+1y+1M is now+13M), with the day clamped to the end of a shorter month; then the days, hours, minutes and seconds are added. A date is the UTC date of the result and a time its UTC time of day, which wraps around midnight (now-9h at 08:30 is 23:30). When the projection is built, date math is checked at the clock's instant, and projection.Expression holds that value as the captured variable; a run that reads no date math does not read the clock.

On Thursday 2026-10-29 at 08:30 UTC:

IStoredTableDataSet lastTenDays = new PureQLProjection(
    datasets,
    query,
    new Dictionary<string, IParameter>
    {
        ["since"] = new DateOffsetParameter(new Long(-10)),
        ["until"] = new CurrentDateParameter(),
    },
    TimeProvider.System
);

Filtered tables

PureQLFilteredSchemaDataSet is an IStoredSchemaDataSet over another one whose tables hold only the rows a PureQL condition is true for: the rows a user may read, for instance. A PureQLProjection is given it in place of the dataset it filters.

IStoredSchemaDataSet visible = new PureQLFilteredSchemaDataSet(
    new PostgreSqlStoredSchemaDataSet(schema, connection),
    [
        // orders: an ITable of the schema; only the orders of the tenant
        KeyValuePair.Create(
            orders,
            new BooleanRow(
                new ComparisonRow(
                    new EqualRow(
                        new EqualUuidRow(
                            new UuidNullableRow(
                                new UuidRow(new FieldUuid("orders", "tenant_id"))
                            ),
                            new UuidNullableRow(new UuidRow(new LiteralUuid(tenant)))
                        )
                    )
                )
            )
        ),
    ]
);

IStoredTableDataSet result = new PureQLProjection([visible], query, parameters);

• Conditions are row expressions (BooleanRow), one or more per table. A condition belongs to the table with its table's hash (TableHash), so it may be given for any ITable equal to the dataset's. A table given several conditions holds the rows all of them are true for. A condition given for a table the origin does not have raises ArgumentException when the dataset's tables are first read (when a projection over it is built): it would otherwise filter nothing.
• Fields of a condition are columns of its table: a field names a column and declares its type and whether it may be null, as a field of a query does (a field declared nullable reads an empty cell as null, one declared non-null is parsed as it is). The condition may read columns no query reads. A field's source is not read: every field of a table's condition reads that table, whatever alias a query gives it.
• A condition is closed: it reads no parameter, no group key and no subquery column, only the table's fields and literals.
• Semantics are those of a query's where: a row is a row of the table only when the condition is true; where it is false or null, the table does not have the row.
• Every read of the table is filtered: from, every kind of join, subqueries, aggregates and in over a subquery, in the main query and in subqueries. The condition applies at the table's scan, below everything else, so for an outer join a row the condition drops is a row that does not exist: the preserved side still appears, with nulls.
• The filter runs where the query runs. The scan of a filtered table is Constant(table).Where(row => condition).Select(row => (field, ...)), the constant holding the origin's table and the condition reading cells as a field read does (see Expression tree), so the provider of the stored tables is still given the whole query and translates the condition into the statement, at the table's scan (... FROM orders WHERE tenant_id = ..., or LEFT JOIN (SELECT ... FROM orders WHERE ...)); Execution is Translated. Over tables held in memory the same tree runs in memory.
• A condition that cannot be applied fails, and never lets rows through: a field that names no column of the table, a field declared with another type than its column holds, a parameter, a group key or a subquery column raises ArgumentException whenever the table is read, before any row: when a projection whose query reads the table is built, or when the table is enumerated. A query that does not read the table is not affected.
• A filtered table read on its own (visible[orders], enumerated or queried with LINQ) is origin.Where(row => condition) run by the origin's provider, so it is filtered in the store too.

Expression tree

projection.Expression is built only from constructs a SQL provider translates: Queryable operators (Enumerable ones only over a group or a list), members and methods of the BCL's value types, strings and Math, value tuples, constants and captured parameters. Nothing in it is compiled, invoked or evaluated on the client except the result rows, built by the last Select, which only maps each row of the query, item by item, to an IRow. Its shape is fixed:

scan(from) [.Where] (.Join | .GroupJoin.SelectMany | .SelectMany)* [.Where(rest of where)]
  plain:   [.OrderBy/ThenBy] .Select(columns)
  grouped: .GroupBy(keys) [.Where(having)] [.OrderBy/ThenBy] .Select(columns)
  total:   one-row constant .Select(aggregates over the rows)
[.DistinctBy(columns)] [.Skip] [.Take]
.Select(row => new ResultRow(columns, new object[] { row.Item1, ... }))

Everything above the last line is the query a provider is given (see Running a query); its rows are value tuples of the result columns, in order.

• Sources. Every stored table is its own root, Constant(IStoredTableDataSet) typed IQueryable<IRow>, so a provider can tell tables (and their databases) apart and split the tree there. A table is always scanned as root.Select(row => (field, ...)), reading only the fields the query reads, and a filtered table (see Filtered tables) as root.Where(row => condition).Select(row => (field, ...)), its condition reading the row's cells as a field read does; a field read is IsNullOrEmpty(row.Cells[column].Value.TextValue) ? null : Parse(row.Cells[column].Value.TextValue, ...) for a field the query declares nullable and Parse(row.Cells[column].Value.TextValue, ...) for one it declares non-null, the IColumn a constant. A subquery is inlined where it is read.
• Rows are value tuples: a source row holds its fields, a joined row one source row per source, a group key one value per key, a query result one value per column. Every value is nullable: long? (integer), decimal?, string, bool?, DateOnly?, TimeOnly?, DateTime? (UTC), Guid?.
• Joins are hash joins on the equalities of their condition: each and-ed condition of on (or of where, when it moves into an inner join) that equates an expression over the rows so far with one over the joined source is a join key. An inner join is Join on the tuple of keys, the remaining conditions in a Where after it; an outer side is GroupJoin (keyed by the driving side: the rows so far for left, the joined source for right) then SelectMany over the matches the remaining conditions hold for, DefaultIfEmpty; full is the left join Concat the joined rows whose group of matches is empty (!Any()). A tuple of keys is never null, so null keys match, as = does. A join whose condition has no such equality is a correlated SelectMany over the joined rows the condition holds for (DefaultIfEmpty for an outer side; full adds the joined rows !Any(...) row matches). An unmatched side is the default tuple: all nulls.
• Aggregates are LINQ's over the group or, over all rows, over the query's rows, which are one shared subtree. A total query reads a one-row constant source, as SQL's SELECT without FROM.
• Semantics are C#'s, which match the specification: null == null, an ordering comparison with null is false, lifted operators. Strings compare with string.CompareOrdinal and order with StringComparer.Ordinal (SQL's "C" collation, code point order); ascending order puts null first. Parameters are captured variables, the Value of a constant holding the parameter, as C# closures are.

Built in, before any provider sees it:

• constants fold (an operation that fails, like 1 / 0, is kept and fails the query; two constant strings compare by code point), true/false fold out of conditions, lifting is dropped where a value cannot be null and an operation over a null literal is null;
• where is split into its and-ed conditions, each moved to the scan of the source it reads (above the scan's Select; only the condition of a filtered table stands below it), or into the join that first sees every source it reads, unless an outer join may leave one of them without a row; a condition on a subquery source moves into the subquery (onto its having when it reads an aggregate);
• adjacent Wheres merge, Where(true) and identity Selects are dropped, consecutive Selects are one, and pagination ends the query (only the result rows' Select follows it);
• a repeated order key or one that is the same for every row is dropped, a distinctBy over every group key is dropped, and a subquery read only by in (without pagination) is neither ordered nor distinctBy-filtered.

Running a query

Enumerating the projection hands the query below the result rows (projection.Expression without its last Select) to the Provider of the first stored table the query reads whose provider is not LINQ to Objects' (EnumerableQuery, the provider of a table held in memory), as provider.CreateQuery(query), and maps each value the provider returns to a result row. A provider that translates the tree, such as Pure.RelationalSchema.Storage.PostgreSQL.Querying, thus runs the whole query (filters, joins, grouping, ordering, pagination) as one SQL statement, and reads only the result; the stored tables are never enumerated in full. For that, the provider must understand the tree's shape: value tuples, and a field read as IsNullOrEmpty(row.Cells[column].Value.TextValue) ? null : Parse(row.Cells[column].Value.TextValue, ...) (long.Parse, decimal.Parse, bool.Parse, DateOnly.Parse, TimeOnly.Parse, DateTime.Parse, Guid.Parse with the invariant culture, or the text itself for a string column), which is the typed read of the column and maps to the column as a whole (Pure.RelationalSchema.Storage.PostgreSQL.Querying from 0.1.0-preview.0.9.0 reads it as the stored column itself, with no text in between, so a join or filter on a date, uuid or number compares the column, which PostgreSQL hashes, estimates and indexes). A field the query declares non-null is read without the null check, as Parse(row.Cells[column].Value.TextValue, ...): the value is never null, so SQL compares, joins and filters on it with =, which a hash or merge join and an index serve, where a nullable value needs IS NOT DISTINCT FROM (null equals null), which PostgreSQL can only evaluate row by row in a nested loop. An aggregate over all rows in a plain select reads the one shared subtree of the query's rows, which Linq.Expressions.Translation.PostgreSql from 0.1.0-preview.0.7.0 translates as a window aggregate (COUNT(*) OVER ()) computed in the pass that reads the rows, not as a subquery that runs the joins again. Declare a join key non-null wherever the column holds no NULL. A join key declared nullable that the join's on (or a where condition) guards with notEqual(key, null) is compared with = too, as the specification allows: the key is read as its value, or the default of its type where it is null (key ?? default), so it is never null; the guard drops every row with a null key, so the result is the same.

The tree runs in memory, with LINQ to Objects, when the tables are held in memory, or when the provider refuses the query with NotSupportedException (when the query is created, enumerated, or before its first value): a construct the provider cannot translate, tables of more than one database, or a source that is not a stored table (a total query's one-row constant). Any other exception the provider raises fails the enumeration. After an enumeration, Execution says which happened (Translated or InMemory) and FallbackReason holds the provider's exception when the query fell back to memory, so that a host can log it.

Run in memory, the tree is compiled once per enumeration as Enumerable calls: each stored table is read once, a query nested in a lambda that reads nothing of it (a joined source, an in list, an aggregate over all rows) is computed once and answers in from a hash set, hash joins cost the rows of both sides rather than their product, and strings order by code point. LINQ composed over the projection (projection.Take(10), projection.Count()) runs in memory over the projection's rows, which are read as above.

Storage mapping

Empty cell text is NULL. The query says whether a field holds it: a field declared nullable reads an empty cell as null, a field declared non-null (in every reference where its source has a row of its own, so not on the optional side of an outer join) is parsed as it is, and an empty cell there fails the query, in memory with FormatException (an empty string cell is the empty string). A stored datetime without an offset is read as UTC. Result columns are typed long for integer and, for decimal, a column type named decimal of this library, as Pure.RelationalSchema has no column type that holds a decimal exactly; result cells hold invariant text (True/False, a decimal in the specification's spelling, exact with no exponent and no trailing zeros in the fraction, yyyy-MM-dd, HH:mm:ss[.fffffff], yyyy-MM-ddTHH:mm:ss[.fffffff]Z, the UTC instant written with Z as a datetime literal is, lower case uuids).

Not supported

• Stored times and datetimes are read to the .NET tick (100 ns); the specification's values have microsecond precision, so finer stored digits are kept as read.

Dependencies

• Pure.Primitives — core primitive value types (String, Date, DateTime, Time, Guid, Number)
• Pure.RelationalSchema — relational schema abstractions (ISchema, ITable, IColumn, column type hierarchy)
• Pure.RelationalSchema.HashCodes — structural hash codes for relational schema types
• Pure.RelationalSchema.Storage — in-memory relational data model (IStoredSchemaDataSet, IStoredTableDataSet, IRow, ICell)
• PureQL.CSharp.Model — PureQL query model (PureQLQuery, Query, expressions per context) — tracks PureQL specification 0.1.0-preview.2.0.0
• Pure.Collections.Generic — generic collection utilities used in row projection

Target Frameworks

• .NET 8
• .NET 9
• .NET 10

Installation

dotnet add package Pure.RelationalSchema.Storage.PureQL.Projection

Usage

// datasets: IEnumerable<IStoredSchemaDataSet> populated by your storage layer
// query:    a PureQLQuery, built directly or deserialized with
//           PureQL.CSharp.Model.Serialization

IStoredTableDataSet result = new PureQLProjection(datasets, query);

foreach (IRow row in result)
{
    // ...
}

await foreach (IRow row in result)
{
    // ...
}

A query selecting one field:

PureQLQuery query = new PureQLQuery(
    new MainPlainQuery(
        new From(new FromEntity("mySchema.myTable")),
        [
            new SelectItemProjection(
                new SelectItemProjectionNonNullable(
                    new SelectItemProjectionString(
                        "name",
                        new StringProjection(new FieldString("mySchema.myTable", "name"))
                    )
                )
            ),
        ]
    )
);

PureQL.CSharp.Model.Samples holds a query for every feature of the specification; this package's tests run all of them.