Pure.RelationalSchema.Storage.PostgreSQL.Querying
0.1.0-preview.0.11.0
dotnet add package Pure.RelationalSchema.Storage.PostgreSQL.Querying --version 0.1.0-preview.0.11.0
NuGet\Install-Package Pure.RelationalSchema.Storage.PostgreSQL.Querying -Version 0.1.0-preview.0.11.0
<PackageReference Include="Pure.RelationalSchema.Storage.PostgreSQL.Querying" Version="0.1.0-preview.0.11.0" />
<PackageVersion Include="Pure.RelationalSchema.Storage.PostgreSQL.Querying" Version="0.1.0-preview.0.11.0" />
<PackageReference Include="Pure.RelationalSchema.Storage.PostgreSQL.Querying" />
paket add Pure.RelationalSchema.Storage.PostgreSQL.Querying --version 0.1.0-preview.0.11.0
#r "nuget: Pure.RelationalSchema.Storage.PostgreSQL.Querying, 0.1.0-preview.0.11.0"
#:package Pure.RelationalSchema.Storage.PostgreSQL.Querying@0.1.0-preview.0.11.0
#addin nuget:?package=Pure.RelationalSchema.Storage.PostgreSQL.Querying&version=0.1.0-preview.0.11.0&prerelease
#tool nuget:?package=Pure.RelationalSchema.Storage.PostgreSQL.Querying&version=0.1.0-preview.0.11.0&prerelease
Pure.RelationalSchema.Storage.PostgreSQL.Querying
PostgreSQL row querying for the Pure relational schema ecosystem — IQueryable / IAsyncEnumerable stored datasets over hash-named tables, read through IDbConnection.
Overview
Pure.RelationalSchema.Storage.PostgreSQL.Querying reads rows from a live PostgreSQL database through the storage abstractions defined in Pure.RelationalSchema.Storage:
- Row access —
PostgreSqlStoredTableDataSetexposes the rows of a table as bothIQueryable<IRow>andIAsyncEnumerable<IRow>, backed by aSELECTof all columns. - Query translation — LINQ composed over the stored tables of one connection runs as one PostgreSQL
SELECT, translated byLinq.Expressions.Translation.PostgreSql. A query that cannot be translated, such as one across connections, throwsNotSupportedExceptioninstead of running in memory. - Schema-wide access —
PostgreSqlStoredSchemaDataSetmaps everyITableof anISchemato itsPostgreSqlStoredTableDataSet. It implementsIPostgreSqlStoredSchemaDataSet, which is defined in the separatePure.RelationalSchema.Storage.PostgreSQL.Abstractionspackage. - Native AOT — the package is
IsAotCompatible: the entity types the translator maps are built from reflection objects, not emitted, and no query is executed in memory.
This package covers reads only. Creating the tables and inserting rows is handled by the related packages listed below.
The tables must already exist with the hash-based names produced by Pure.RelationalSchema.Storage.PostgreSQL.Creation (any other creator that produces the same names works too). Schema, table and column identifiers are the first 63 hexadecimal characters of the hashes from Pure.RelationalSchema.HashCodes, not the model names. The caller owns the IDbConnection: it must be opened before use, and this package never disposes it.
Public API
| Type | Kind | Description |
|---|---|---|
PostgreSqlStoredSchemaDataSet |
record | Maps each ITable in an ISchema to a PostgreSqlStoredTableDataSet; implements IPostgreSqlStoredSchemaDataSet (from Pure.RelationalSchema.Storage.PostgreSQL.Abstractions) |
PostgreSqlStoredTableDataSet |
record | IStoredTableDataSet backed by a PostgreSQL table; supports sync and async enumeration. Public members: SchemaName, Connection, TableSchema |
IPostgreSqlStoredSchemaDataSet (an IStoredSchemaDataSet with an IDbConnection Connection property) lives in Pure.RelationalSchema.Storage.PostgreSQL.Abstractions, so other packages can depend on it without depending on this one. All SQL statement builders, the row enumerables and the query provider are internal.
Query translation
The Provider of a PostgreSqlStoredTableDataSet translates the LINQ composed over it. When a query is enumerated (or a terminal operator such as Count or First runs), the whole query is translated into one SELECT and executed. Its sources must all be stored tables of one connection — the same IDbConnection instance; their schemas may differ.
Nothing runs in memory: a query that reads several connections, reads other sources, combines rows of different tables in a set operation, or uses an operator, member or result shape that cannot be translated throws NotSupportedException (with the translator's exception as InnerException when it is the translator that refused). So a join of two tables, of one schema or of two, is one SELECT ... JOIN ..., while a join of tables of two connections throws; call AsEnumerable() on each part to combine them in memory yourself.
Rows are read as cells of text, so the provider translates cell reads of the form row.Cells[column].Value.TextValue, where column is a constant or captured IColumn of a queried table, into the SQL text of the column, matching what enumeration returns. A NULL cell is empty text, as it is when enumerated (COALESCE(..., '')), so NULL cells compare, join, group and order as empty text does in LINQ to Objects:
| Column type | Text in SQL |
|---|---|
string |
the value |
int, ushort, long, uint, ulong, uuid |
CAST(... AS text) |
bool |
True / False |
datetime |
MM/dd/yyyy HH:mm:ss, composed from EXTRACT parts |
date |
MM/dd/yyyy, composed from EXTRACT parts |
time |
HH:mm, composed from EXTRACT parts |
double, float |
the shortest round-trip text, as .NET writes it: CAST(... AS text) with the exponent in upper case, and in plain form where .NET uses it and PostgreSQL does not (1e15 to 1e17 for double, 1e6 to 1e9 for float) |
determined_hash |
the bytes of the column, a bytea, decoded as UTF-8 text (CONVERT_FROM(..., 'UTF8')), so that a comparison, a join with or union to a text column, Length, Substring, case conversion, Replace or Trim over it are those of PostgreSQL over the text enumeration reads; PostgreSQL fails on bytes that are no UTF-8 where .NET replaces them |
row.Cells.Count and row.Cells.Keys are translated as the columns of the row's table. Cells of a column of any other type cannot be translated (the exception names the column and its type), nor can any other use of a row (a cell's IString itself, ...).
A cell parsed to its type is read as the column itself, with no text in between: T.Parse(row.Cells[column].Value.TextValue, ...) — T the column's type or another integer type (bool, int, long, ushort, uint, ulong, decimal, double, Guid, DateOnly, TimeOnly, DateTime), with the invariant culture, number styles that allow a leading sign and read the text as decimal and date styles that assume no time zone — is the column, never NULL to the translator, so a join or filter on it compares the column with =, which PostgreSQL hashes, estimates and indexes; string.IsNullOrEmpty(text) ? null : (T?)T.Parse(text) is the nullable column, and string.IsNullOrEmpty(text) of such a column tests it for NULL. A double, float, string or determined_hash column is read as text whatever is parsed from it. A parse with other styles stays a parse of the cell text: with DateTimeStyles.AssumeUniversal | DateTimeStyles.AdjustToUniversal the text is read as the instant it says in UTC, with the kind Utc as in memory, where the column itself has the kind Unspecified; AssumeUniversal alone or AssumeLocal, which go through the time zone of the client, and number styles no SQL cast honours — AllowHexSpecifier or AllowBinarySpecifier, which read another number, or styles without a leading sign, which reject a text the cast accepts — throw NotSupportedException. A datetime or time column read so has its whole precision, where its cell text keeps seconds or minutes only, and an empty cell parsed bare is NULL, which read into a non-nullable value throws InvalidOperationException (the parse of the empty text fails in memory too).
A translated query returns rows of its tables (through Where, OrderBy, Skip, Take, Distinct, DistinctBy, MinBy, MaxBy, Cast, set operations of the same table, the element operators and the Select, Join, GroupJoin and SelectMany projections that return a row, also one held by an anonymous type, a value tuple or a record built earlier in the query, and the row of a left join — GroupJoin ... SelectMany(x => x.Inner.DefaultIfEmpty(), ...) — which is null where none matches), scalars — the signed and unsigned numbers, DateTime, DateOnly, TimeOnly and TimeSpan values among them, read from timestamp, date, time and interval results — and constructed objects — anonymous types, value tuples, records, objects built with initializers, the keys of a GroupBy and the results of its result selector — whose constructor arguments and assigned members are rows, scalars or such objects, also when a GroupJoin or a set operation (Union, Concat, ...) builds them; a set operation over objects holding rows of different tables throws, as one over the rows does. Any other result shape throws NotSupportedException. Results keep the semantics of enumeration — FirstOrDefault(predicate, defaultValue) returns the given default, MinBy of no rows is null, an aggregate of no rows or a NULL read into a non-nullable value throws InvalidOperationException — except that SQL compares and orders text by the database collation. The order of the rows is kept as enumeration keeps it: a later OrderBy sorts stably, Distinct yields the first occurrences in the order of its source, GroupBy the groups in the order of their first elements and Concat an ordered side in its order.
Several aggregates of stored tables are read as one row in one statement from a one-element in-memory root whose selector holds the aggregates: new[] { 0 }.AsQueryable().Select(_ => new { Orders = orders.LongCount(), Revenue = orders.Sum(...) }), created through the provider of one of the tables (orders.Provider.CreateQuery<T>(expression)), runs as SELECT (SELECT COUNT(*) ...), (SELECT SUM(...) ...) without FROM. The tables must be stored tables of one connection.
IQueryable<IRow> customers = shop[customersTable];
IQueryable<IRow> orders = shop[ordersTable];
// One statement: SELECT ... FROM ... AS t0 INNER JOIN ... AS t1 ON ... WHERE ...
var totals = customers
.Where(c => c.Cells[activeColumn].Value.TextValue == "True")
.Join(
orders,
c => c.Cells[idColumn].Value.TextValue,
o => o.Cells[customerIdColumn].Value.TextValue,
(c, o) => new
{
Name = c.Cells[nameColumn].Value.TextValue,
Total = o.Cells[totalColumn].Value.TextValue,
}
)
.ToArray();
IStoredTableDataSet is both IQueryable<IRow> and IAsyncEnumerable<IRow>, so with System.Linq.Async (or .NET 10's System.Linq.AsyncEnumerable) in scope, compose over it as IQueryable<IRow> to pick the translated operators. A table used inside a lambda of a query — captured as a variable of any of its types (IQueryable<IRow>, IStoredTableDataSet, IEnumerable<IRow> with AsQueryable()), read through the indexer of a schema data set, or returned by a method — is a source of the same statement.
Enumerating a table, synchronously or asynchronously, reads every row before returning the first, so queries can run on the connection inside the loop. Asynchronous enumeration over a connection that is not a DbConnection (a decorator, for instance) reads synchronously.
Dependencies
Pure.RelationalSchema.Storage— storage abstractions (IStoredSchemaDataSet,IStoredTableDataSet,IRow) that this package implementsPure.RelationalSchema.Storage.PostgreSQL.Abstractions—IPostgreSqlStoredSchemaDataSet, the interface implemented byPostgreSqlStoredSchemaDataSetLinq.Expressions.Translation.PostgreSql— translates LINQ expression trees into PostgreSQLSELECTstatementsPure.RelationalSchema.HashCodes— hash codes used to derive PostgreSQL object namesPure.Primitives.Abstractions— base interfaces for primitive types (IString, etc.)Pure.Primitives— concrete primitive type implementationsPure.Primitives.String.Operations— string composition utilities used to build SQL statementsPure.Primitives.Choices— choice typesPure.Collections.Generic— generic collection implementations (e.g.Dictionary<TKey1, TKey2, TValue>with dual-key lookup)
Target Frameworks
- .NET 8
- .NET 9
- .NET 10
- .NET 11
Installation
dotnet add package Pure.RelationalSchema.Storage.PostgreSQL.Querying
The example below also uses Pure.RelationalSchema.Storage.PostgreSQL.Creation to create the tables. Any creator that produces the same hash-based names works; Creation is not a dependency of this package.
Usage
using System.Data;
using Npgsql;
using Pure.RelationalSchema.Abstractions.Column;
using Pure.RelationalSchema.Abstractions.Schema;
using Pure.RelationalSchema.Abstractions.Table;
using Pure.RelationalSchema.ColumnType;
using Pure.RelationalSchema.Storage.Abstractions;
using Pure.RelationalSchema.Storage.PostgreSQL.Abstractions;
using Pure.RelationalSchema.Storage.PostgreSQL.Creation;
using Pure.RelationalSchema.Storage.PostgreSQL.Querying;
using Column = Pure.RelationalSchema.Column.Column;
using Schema = Pure.RelationalSchema.Schema.Schema;
using String = Pure.Primitives.String.String;
using Table = Pure.RelationalSchema.Table.Table;
// The caller owns the connection and opens it.
IDbConnection connection = new NpgsqlConnection(connectionString);
connection.Open();
// Describe a schema with one table.
IColumn idColumn = new Column(new String("Id"), new IntColumnType());
IColumn nameColumn = new Column(new String("Name"), new StringColumnType());
ITable usersTable = new Table(new String("Users"), [idColumn, nameColumn], []);
ISchema schema = new Schema(new String("Shop"), [usersTable], []);
// Create the tables with hash-based names (DDL runs lazily, on first access).
ISchema createdSchema = new PostgreSqlCreatedSchema(schema, connection);
_ = createdSchema.Tables.ToArray();
// Query them.
IPostgreSqlStoredSchemaDataSet dataSet =
new PostgreSqlStoredSchemaDataSet(createdSchema, connection);
IStoredTableDataSet users = dataSet[usersTable];
// Synchronous: IQueryable<IRow>
foreach (IRow row in users)
{
string name = row.Cells[nameColumn].Value.TextValue;
Console.WriteLine(name);
}
// Asynchronous: IAsyncEnumerable<IRow>
await foreach (IRow row in users)
{
string id = row.Cells[idColumn].Value.TextValue;
Console.WriteLine(id);
}
Every cell value is an IString; read its text through TextValue.
Related packages
Pure.RelationalSchema.Storage.PostgreSQL.Abstractions— the sharedIPostgreSqlStoredSchemaDataSetinterfacePure.RelationalSchema.Storage.PostgreSQL.Creation— creates the schema and tables this package reads fromPure.RelationalSchema.Storage.PostgreSQL.Insertion— inserts rows into those tables
| Product | Versions Compatible and additional computed target framework versions. |
|---|---|
| .NET | net8.0 is compatible. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. net9.0 is compatible. net9.0-android was computed. net9.0-browser was computed. net9.0-ios was computed. net9.0-maccatalyst was computed. net9.0-macos was computed. net9.0-tvos was computed. net9.0-windows was computed. net10.0 is compatible. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed. net11.0 is compatible. |
-
net10.0
- Linq.Expressions.Translation.PostgreSql (>= 0.1.0-preview.0.9.0)
- Pure.Collections.Generic (>= 0.1.0-preview.3.1.0)
- Pure.Primitives (>= 3.8.0)
- Pure.Primitives.Abstractions (>= 4.4.0)
- Pure.Primitives.Choices (>= 1.4.0)
- Pure.Primitives.String.Operations (>= 1.8.0)
- Pure.RelationalSchema.HashCodes (>= 3.4.0)
- Pure.RelationalSchema.Storage (>= 0.1.0-preview.8.1.0)
- Pure.RelationalSchema.Storage.PostgreSQL.Abstractions (>= 0.1.0-preview.0.2.0)
-
net11.0
- Linq.Expressions.Translation.PostgreSql (>= 0.1.0-preview.0.9.0)
- Pure.Collections.Generic (>= 0.1.0-preview.3.1.0)
- Pure.Primitives (>= 3.8.0)
- Pure.Primitives.Abstractions (>= 4.4.0)
- Pure.Primitives.Choices (>= 1.4.0)
- Pure.Primitives.String.Operations (>= 1.8.0)
- Pure.RelationalSchema.HashCodes (>= 3.4.0)
- Pure.RelationalSchema.Storage (>= 0.1.0-preview.8.1.0)
- Pure.RelationalSchema.Storage.PostgreSQL.Abstractions (>= 0.1.0-preview.0.2.0)
-
net8.0
- Linq.Expressions.Translation.PostgreSql (>= 0.1.0-preview.0.9.0)
- Pure.Collections.Generic (>= 0.1.0-preview.3.1.0)
- Pure.Primitives (>= 3.8.0)
- Pure.Primitives.Abstractions (>= 4.4.0)
- Pure.Primitives.Choices (>= 1.4.0)
- Pure.Primitives.String.Operations (>= 1.8.0)
- Pure.RelationalSchema.HashCodes (>= 3.4.0)
- Pure.RelationalSchema.Storage (>= 0.1.0-preview.8.1.0)
- Pure.RelationalSchema.Storage.PostgreSQL.Abstractions (>= 0.1.0-preview.0.2.0)
-
net9.0
- Linq.Expressions.Translation.PostgreSql (>= 0.1.0-preview.0.9.0)
- Pure.Collections.Generic (>= 0.1.0-preview.3.1.0)
- Pure.Primitives (>= 3.8.0)
- Pure.Primitives.Abstractions (>= 4.4.0)
- Pure.Primitives.Choices (>= 1.4.0)
- Pure.Primitives.String.Operations (>= 1.8.0)
- Pure.RelationalSchema.HashCodes (>= 3.4.0)
- Pure.RelationalSchema.Storage (>= 0.1.0-preview.8.1.0)
- Pure.RelationalSchema.Storage.PostgreSQL.Abstractions (>= 0.1.0-preview.0.2.0)
NuGet packages (1)
Showing the top 1 NuGet packages that depend on Pure.RelationalSchema.Storage.PostgreSQL.Querying:
| Package | Downloads |
|---|---|
|
Pure.RelationalSchema.Storage.PostgreSQL
Meta-package that references the Abstractions, Creation, Querying, Insertion, Migration, Destruction and Existence packages of the Pure relational schema PostgreSQL storage. It contains no code. |
GitHub repositories
This package is not used by any popular GitHub repositories.
| Version | Downloads | Last Updated |
|---|---|---|
| 0.1.0-preview.0.11.0 | 94 | 11/11/2026 |
| 0.1.0-preview.0.10.0 | 131 | 11/9/2026 |
| 0.1.0-preview.0.9.1 | 240 | 11/2/2026 |
| 0.1.0-preview.0.9.0 | 178 | 10/28/2026 |
| 0.1.0-preview.0.8.3 | 78 | 10/28/2026 |
| 0.1.0-preview.0.8.2 | 79 | 10/28/2026 |
| 0.1.0-preview.0.8.1 | 71 | 10/28/2026 |
| 0.1.0-preview.0.8.0 | 74 | 10/28/2026 |
| 0.1.0-preview.0.7.0 | 92 | 10/27/2026 |
| 0.1.0-preview.0.6.0 | 148 | 10/26/2026 |
| 0.1.0-preview.0.5.0 | 101 | 10/23/2026 |
| 0.1.0-preview.0.4.0 | 157 | 10/22/2026 |
| 0.1.0-preview.0.3.0 | 86 | 10/22/2026 |
| 0.1.0-preview.0.2.0 | 172 | 10/16/2026 |
| 0.1.0-preview.0.1.0 | 71 | 10/15/2026 |