Linq.Expressions.Translation.PostgreSql 0.1.0-preview.0.7.0

Linq.Expressions.Translation.PostgreSql

Translates the expression tree of an IQueryable<T> into a single PostgreSQL SELECT statement.

What is translated

• Where, Select, OrderBy/OrderByDescending/ThenBy/ThenByDescending, Order/OrderDescending, Skip, Take, TakeLast, SkipLast, Distinct, DistinctBy, Reverse, Cast and OfType; an operator that cannot extend the current SELECT (for example Where after Take) wraps it into a derived table, which exposes ordering keys the projection hides as extra "$n" columns so that the order survives (also for Reverse, Last and TakeLast). The ordering operators accept StringComparer.Ordinal (ORDER BY ... COLLATE "C", the byte-wise order of UTF-8, which is code-point order). DistinctBy is SELECT DISTINCT ON (keys), whose ORDER BY has to start with the keys: a source order that does not (OrderByDescending(o => o.PlacedAt).DistinctBy(o => o.UserId), the latest order per user) is restored outside a derived table, into which every later operator other than a projection or paging also wraps the kept rows.
• Comparison, logical, arithmetic, bitwise and conditional operators, ??, conversions (a DateTime compares with a DateTimeOffset through PostgreSQL's session time zone; a DateTime constant is a TIMESTAMP whatever its kind, as C# compares DateTime values by their ticks, and a Utc one converted to a DateTimeOffset keeps its zero offset), string concatenation, Nullable<T> members and constructed objects (new Summary(c.Id, c.Name), also of constants only), which are projected member by member, as is a constant tuple (default(ValueTuple<int?, string?>) or a captured one, whose members become literals, NULL for a default). string.CompareOrdinal(a, b) compared with 0 is a COLLATE "C" < b (<=, >, >=, =, <>), ranking a null operand below every string as .NET does (a IS NULL OR a COLLATE "C" < b).
• string members (Length, case conversion, trimming (Trim() removes every .NET white-space character, not only spaces), Substring, Remove, IndexOf/LastIndexOf/IndexOfAny, Replace, Contains/StartsWith/EndsWith as LIKE with escaped wildcards, IsNullOrEmpty, Concat, Format and interpolated strings without alignment or format strings; lengths and positions count code points, as PostgreSQL does, not UTF-16 code units, so a character outside the Basic Multilingual Plane, an emoji for example, counts once instead of twice), Math functions (Round half to even, .NET's default, or away from zero, of decimals and doubles, also to digits) and their int/long/decimal/double counterparts, double.IsNaN/IsInfinity/IsFinite, date and time parts and arithmetic (DateTime, DateTimeOffset, DateOnly, TimeOnly), TimeSpan fields and totals (Days, Hours, ..., TotalDays, ..., Ticks), DateTime.Now/UtcNow/Today, DateTimeOffset.Now/UtcNow, Guid.NewGuid, Enum.HasFlag and ToString of numbers and Guid. Ticks are 100 ns since 0001-01-01: DateTime.Ticks and TimeOnly.Ticks come from EXTRACT(EPOCH FROM ...), and TimeSpan.FromTicks, AddTicks and new DateTime(ticks[, kind]) of a computed count become interval arithmetic in microseconds (PostgreSQL keeps no finer unit; the kind has to be a constant other than Local, and Utc adds AT TIME ZONE 'UTC'). Parse of bool and Guid, and of numbers and TimeOnly with CultureInfo.InvariantCulture (plus constant NumberStyles/DateTimeStyles), is CAST(... AS boolean | uuid | bigint | numeric | ... | time): the text has to be in PostgreSQL's input syntax, which also accepts forms .NET rejects; the current-culture overloads are rejected. DateOnly.Parse and DateTime.Parse with the invariant culture are TO_DATE(..., 'MM/DD/YYYY') and TO_TIMESTAMP('+00 ' || ..., 'TZH MM/DD/YYYY HH24:MI:SS') AT TIME ZONE 'UTC', which read the invariant short date month first whatever the server's DateStyle, at the same cost as a cast: a month and a day of one or two digits, a four-digit year, any single separator (1/2/2024, 01-02-2024 10:30:00, also with .), leading spaces and a 24-hour time that may be missing or lack its seconds; fractional seconds, an AM/PM marker and anything else after the seconds are dropped, a two-digit year is the year itself (1/2/24 is the year 24), and any other form, ISO 8601 or a month name for example, fails on the server. The fixed +00 offset, taken back as UTC, keeps a DateTime as written even in a daylight-saving gap of the session's time zone.
• Contains (and Any(x => x == value)) on in-memory collections (IN (...)) and on array columns (= ANY (...), which is COALESCE(..., FALSE) as a value or under NOT, as an array may hold NULL elements), plus array Length, Count() and Any().
• Terminal operators: Count/LongCount, Sum, Average, Min, Max (of booleans as BOOL_AND/BOOL_OR; Min/Max of Guid is rejected, as PostgreSQL orders uuids differently), MinBy/MaxBy (the first row in the order of the key, NULLS LAST so that elements with a null key are skipped unless every key is null, and then of the source, so that ties resolve as in .NET; StringComparer.Ordinal gives COLLATE "C", and a Guid key is rejected like Min/Max of Guid), Any, All, Contains, First/Single/Last/ElementAt (and their OrDefault forms, which substitute the default of a value type inside a lambda), and aggregates after DefaultIfEmpty().
• The same operators inside lambdas, as correlated or uncorrelated subqueries (EXISTS, IN (SELECT ...), scalar subqueries). An aggregate (Count, Sum, Average, Min, Max, Any, All) in the projection of a query over the rows that projection reads — the same query, in any order, below any Where and Select of the aggregate's own — is a window aggregate over all of them, computed in the pass that reads the rows: orders.Select(o => new { o.Id, Share = o.Total / orders.Sum(x => x.Total) }) is SELECT t0."Id", t0."Total" / COALESCE(SUM(t0."Total") OVER (), 0) AS "Share" FROM "Order" AS t0, and a filtered one is COUNT(*) FILTER (WHERE ...) OVER (). The rows have to be the rows the projection reads (not once they are paged or made distinct) and the aggregate's lambdas must read nothing but their own element, otherwise it stays a scalar subquery; a filter, grouping, join or aggregate over a projection holding a window wraps it into a derived table, as SQL allows a window only in a projection, an order, paging or DISTINCT.
• Join (INNER JOIN; keys compare with =, so null keys never match, as in C#, while the members of a composite key, an anonymous type or a tuple that compares its members with the default comparer, match with IS NOT DISTINCT FROM when both are nullable), GroupJoin (aggregated groups as correlated subqueries, also after Where, Select, OrderBy, Distinct or paging of the group, or LEFT JOIN with SelectMany/DefaultIfEmpty, where a Where on the group before DefaultIfEmpty(), possibly chained, becomes part of the ON condition instead of dropping the unmatched rows) and SelectMany (CROSS JOIN, correlated INNER/LEFT JOIN, or JOIN LATERAL when the inner source is paged). A left-joined source that projects anything other than its own columns (a computed value, a constant, a column of the outer element) is joined as a derived table, so that an unmatched row reads it as NULL, as the default element is in LINQ to Objects, instead of evaluating it on the NULLs of the missing row. An outer row without a match reads the element DefaultIfEmpty() yields: null for a reference type (such a projection compared with null tests a column that is never NULL in a match, or every column when there is none, as a left-joined entity does), the default of a value type member by member (COALESCE(..., 0), down to a reference or nullable member, which stays NULL), or the explicit default of DefaultIfEmpty(value), which is translated only for a single value that is never NULL in a match, as NULL cannot be told from the absence of a row.
• GroupBy (with element and result selectors): GROUP BY the flattened key, HAVING for filters on groups, and aggregates over the group — Count (also with a predicate, as FILTER (WHERE ...)), Sum, Average, Min, Max, Any/All (BOOL_OR/BOOL_AND), Where(...) (possibly chained) before an aggregate and Select(...).Distinct() before Count, Sum or Average (COUNT(DISTINCT ...), SUM(DISTINCT ...), AVG(DISTINCT ...); Select after Distinct is rejected, as the aggregate would apply to the distinct projected values).
• Concat, Union, Intersect and Except as UNION ALL, UNION, INTERSECT and EXCEPT, parenthesizing operands that are paged or a different set operation. UnionBy is SELECT DISTINCT ON (keys) over both sides combined with UNION ALL, each marked with a hidden "$side" column, ordered by the keys and then the side, so that the element of the first sequence is the one kept, as in .NET; the result has no order, like Union. IntersectBy and ExceptBy filter the elements on the membership of their key in the second sequence, a subquery (IN (SELECT ...)) or an in-memory collection of keys (IN (...)), with the null semantics of Contains (a null key matches a NULL of the sequence), and keep one element per key with DISTINCT ON, as DistinctBy does.
• Captured variables and parameter-free subtrees, which are evaluated in memory and rendered as literals. A subtree that throws when it is evaluated where it may not be reached (a branch of a conditional, the right operand of ??, && or ||) is left to the database with the constants below it folded: o.Id < 0 ? one / zero : o.Total with zero == 0 is CASE WHEN t0."Id" < 0 THEN (SELECT 1) / 0 ELSE t0."Total" END, where the scalar subquery keeps the planner from computing the division while it plans the statement, as PostgreSQL folds an operation on constants alone before any row reaches it. Elsewhere the exception propagates from the translation.
• One row of aggregates over tables: a constant one-element sequence as the root (new[] { 0 }.AsQueryable().Select(_ => new { Orders = orders.Count(), Revenue = orders.Sum(o => o.Total) })) is a SELECT of scalar subqueries without FROM. The element itself has no column and cannot be read.

Anything else — user-defined methods, culture- or comparison-specific overloads, navigation properties, in-memory sources (other than the one-element root above), operators such as Zip, Aggregate or SkipWhile — throws NotSupportedException naming the offending member, method or expression node.

Overview

The package exposes exactly one public type, PostgreSqlQuery. It accepts a System.Linq.Expressions.Expression — the Expression of an IQueryable<T> — and exposes the PostgreSQL SELECT statement for that tree as its TextValue.

IQueryable<Order> orders = ...; // any IQueryable<T> whose provider builds the tree

string sql = new PostgreSqlQuery(
    orders.Where(o => o.Total > 100).Select(o => o.Id).Expression
).TextValue;

// SELECT t0."Id" FROM "Order" AS t0 WHERE t0."Total" > 100

The package does not execute queries, open connections or materialize results; it only produces SQL text.

Mapping

A table is named after the element type and its columns after its public properties with a public getter (indexers and write-only properties are skipped), in declaration order. [Table] (including its Schema), [Column] and [NotMapped] from System.ComponentModel.DataAnnotations.Schema override the defaults. Identifiers are always double-quoted, so their case is kept. A property whose type is not a scalar (or an array of scalars) is rejected, so navigation properties are not supported.

Nullability follows the CLR: Nullable<T>, nullable reference types and references without nullable annotations are nullable columns, and so are a conditional with a nullable branch, a scalar subquery or Min/Max of a reference type, a column of a set operation that is nullable on either side and every column of a left-joined source. A nullable value read as its value, Nullable<T>.Value or a cast (T)nullable, is never NULL where the C# evaluates (it would throw), so it compares and joins with =, also as a member of a composite key. C# semantics are preserved where SQL differs — equality with a nullable operand uses IS [NOT] DISTINCT FROM (a correlation key may still be compared with =, which is false for NULL in both languages, and so are the keys of Join and GroupJoin, which never match null keys; the members of a composite key do), a nullable comparison used as a value becomes COALESCE(..., FALSE), nullable keys sort as NULLS FIRST ascending, membership tests used as a value or under NOT become COALESCE(..., FALSE) (IN of a nullable value, = ANY of any value, as an array may hold NULL elements), a nullable string or number is concatenated as COALESCE(..., ''), a lifted ^ of booleans stays NULL, Contains of a nullable item against a subquery or an array column also matches a NULL of the sequence (Contains(null) is true for a sequence holding a null), a left-joined entity compared with null tests a column its table requires (or every column), Select(...).Distinct().Count() over nullable group values counts NULL as one of them, and OfType<T>() drops the nulls of a nullable sequence. Concatenating a bool, an enum or a date or time value is rejected, as PostgreSQL formats them differently from .NET.

Constants are inline literals

The result is a single string, not a command with a parameter collection. Every constant in the tree — literals, captured variables and in-memory collections — is rendered as a safely escaped inline PostgreSQL literal (for example, 'O''Brien' for the string O'Brien; control and non-space white-space characters become E'' escapes such as E'a\u00a0b'). No bind parameters ($1, @p0) are produced.

Native AOT and trimming

The package is marked IsAotCompatible and builds without trimming or AOT warnings. Translation reads the public properties of the queried element types to find their columns, so TextValue is annotated [RequiresUnreferencedCode]: in a trimmed or Native AOT application a property that the code never references is removed, and its column silently disappears from SELECT. Keep the properties of every mapped type and then acknowledge the IL2026 warning at the call site, for example:

[DynamicDependency(DynamicallyAccessedMemberTypes.PublicProperties, typeof(Order))]
[UnconditionalSuppressMessage("Trimming", "IL2026", Justification = "Order properties are preserved.")]
static string Sql(IQueryable<Order> orders)
{
    return new PostgreSqlQuery(orders.Expression).TextValue;
}