Runtime operations

This page covers the runtime-only operations on a DynamicTable<T>: undeclared columns, schema auto-mapping, ordering, and table lifecycle. See Declaring table types for the declaration and CRUD basics.

Custom (undeclared) columns

A runtime table may carry extra columns beyond the declared shape. Opt in per query with WithCustomColumns(...), filter on them with the DB.CustomField<T>("name") marker inside a normal predicate, and read them back with TryGetCustomValue<T>(...) on each row:

using static Socigy.OpenSource.DB.Core.SyntaxHelper.DB;

var rows = await AuditEntry.WithTableName("audit_2026_06")
    .WithCustomColumns("region", "score")        // capture extra columns into each row
    .Query(x => CustomField<int>("score") > 10)  // filter on an undeclared column
    .WithConnection(conn)
    .ToListAsync();

foreach (var r in rows)
    if (r.TryGetCustomValue<string>("region", out var region))
        Console.WriteLine(region);

CustomField<T> is a translation marker, rewritten into SQL at build time; it never runs at runtime. Custom values are dictionary-backed, so they cost slightly more than a declared property. TryGetCustomValue<T> converts the stored value to T and returns false when the column was not captured.

Auto-mapping a live schema

Skip naming the extras: MapTypeAsync(name, conn) introspects the live table once, discovers every column beyond the declared shape, binds them, and caches the resolved schema per (type, table name):

var t = await AuditEntry.MapTypeAsync("audit_2026_06", conn);   // discovers + binds extras, then cached
var rows = await t.Query(x => x.UserId == id).ToListAsync();     // declared props + every extra column

Ordering and pagination

On a dynamic table OrderBy takes a raw SQL fragment (the column shape is bound late, so there is no lambda form). Pair it with Limit/Offset:

var page = await AuditEntry.WithTableName("audit_2026_06")
    .WithConnection(conn)
    .Query(x => x.UserId == id)
    .OrderBy("\"at\" DESC")
    .Limit(20).Offset(40)
    .ToListAsync();

Use the generated {Property}ColumnName constants to keep raw fragments in sync with the schema, for example OrderBy($"{AuditEntry.AtColumnName} DESC").

Table lifecycle (create and drop)

Because [TableType] tables are not part of the migration history, the type can create and drop its own table at runtime, with DDL derived from the declared shape:

var t = AuditEntry.WithTableName("audit_2026_06").WithConnection(conn);

await t.InstantiateAsync();           // CREATE TABLE IF NOT EXISTS "audit_2026_06" (...declared columns...)
bool exists = await t.InstanceExistsAsync();
await t.DeleteInstanceAsync();        // DROP TABLE IF EXISTS "audit_2026_06"

Notes and limits

• Results are typed T, not dynamic, so dynamic tables work under NativeAOT. Only custom-column access goes through a dictionary.
• InstantiateAsync maps the common CLR types (Guid, int/long/short, string, bool, decimal/double/float, DateTime, byte[]), [Encrypted] to bytea, and JSON to jsonb. [Default] expressions are not emitted in the runtime DDL, so provide those values yourself or create the table out of band. Enum columns fall back to text.
• [TableType]-only classes are never touched by the migration runner.
• UpdateAsync requires a predicate and excludes the primary key (and, by default, auto-increment columns) from the SET list.