Under the Hood

Not an ORM. Not a wrapper. A data platform with its own expression compiler, bulk protocol, and structural diff engine. This page documents how it actually works, with real class names and SQL architecture.

Expression Compiler

ExpressionToSqlCompiler walks C# expression trees and emits native parameterized SQL. Ternary → CASE WHEN, nullable navigation → IS NOT NULL, StringComparison → ILIKE. Covers edge cases that even EF Core does not fully handle.

Diff Tree Save

Change Tracking builds two ValueTreeNode trees (memory vs DB), compares them with hash-based skip — unchanged subtrees are free, like git. Only the minimal set of SQL operations is emitted. No delete-all/re-insert.

Structured Storage, Full LINQ

Values stored relationally in typed columns with FK constraints — not as JSON blobs. Yet full LINQ works: Where, OrderBy, GroupBy, Window, aggregation — all compiled to real SQL against real indexes. The usual trade-off “flexible schema = no queries” does not apply here.

Core Architecture

IRedbService — The Entry Point

Everything goes through IRedbService. It composes 10+ provider interfaces into a single service. Concrete database behavior comes from RedbServiceBase subclasses that override abstract factory methods.

IRedbService
ISchemeSyncProvider Code-first schema sync. SyncSchemeAsync<T>(), auto-discovery via [RedbScheme] attribute.
IObjectStorageProvider Object CRUD. CreateAsync, LoadAsync, SaveAsync, DeleteAsync. Strategies: DeleteInsert, ChangeTracking.
ITreeProvider Tree hierarchy. LoadTree, GetChildren, MoveObject, GetPathToRoot. Polymorphic (multi-scheme).
IQueryableProvider LINQ queries. Query<T>() → IRedbQueryable<T>. Also TreeQuery<T>() for tree-scoped queries.
IUserProvider User CRUD & auth. CreateUserAsync, ValidateUserAsync, ChangePasswordAsync, GetUsersAsync.
IRoleProvider Roles. CreateRoleAsync, AssignUserToRoleAsync, RemoveUserFromRoleAsync. Cascade delete on role removal.
IPermissionProvider Access control. CanUserEditObject, CanUserSelectObject, CanUserInsertScheme, CanUserDeleteObject.
IListProvider Ordered lists. RedbListItem with lazy object loading. Cached with TTL (default 5 min).
IValidationProvider Data validation rules per scheme.
IRedbContext Raw SQL facade. QueryAsync<T>, ExecuteAsync, BeginTransactionAsync, ExecuteAtomicAsync, batch ID generation.

Initialization — 9 steps in InitializeAsync()

  1. Set type resolver for SystemTextJsonRedbSerializer
  2. Auto-sync schemes — scan assemblies for [RedbSchemeAttribute], call SyncSchemeAsync<T>() per type
  3. Sync UserConfigurationProps system scheme
  4. Initialize default user configuration
  5. RedbObjectFactory.Initialize(this)
  6. RedbObject.SetSchemeSyncProvider(_schemeSync)
  7. WarmupMetadataCacheAsync() — calls SQL warmup_all_metadata_caches()
  8. InitializePropsCache() — creates MemoryRedbObjectCache
  9. _treeProvider.InitializeTypeRegistryAsync()

Provider Factory Pattern

RedbServiceBase defines 10 abstract factory methods: CreateSchemeSyncProvider, CreateObjectStorageProvider, CreateTreeProvider, CreateQueryableProvider, CreateUserProvider, CreateRoleProvider, CreatePermissionProvider, CreateListProvider, CreateLazyPropsLoader, CreateValidationProvider. Each PostgreSQL/MSSQL provider project overrides these to inject database-specific behavior. Your business code never sees the difference.

Data Model

RedbObject<T> — The Core Entity

Every object in RedBase maps to the _objects table. System fields are stored in columns. Business data (Props) is stored as structured values with strong typing and referential integrity enforced at the database level.

_types Type registry (Long, String, Guid, Object, ListItem, Class, Array, Dictionary…)
← FK
_structures Field definitions. _id_type FK → _types — each field has a strict type enforced by the database
← FK
_schemes C# class → scheme. Structure hash for change detection. Maps to [RedbScheme] attribute
_values Prop values in native typed columns (not strings). FK to _structures and _objects. Object refs (_Object FK → _objects) and list refs (_ListItem FK → _list_items) — dangling references impossible
← FK
_objects Object instances. _id_scheme FK → _schemes. Tree hierarchy via _id_parent (self FK, ON DELETE CASCADE). + _value_* columns for RedbPrimitive<T>

What this gives you

Type safety: every field in _structures has _id_type (FK → _types). You can’t store a string where a number is expected — the database schema prevents it.

Referential integrity: _values._Object is a real FK to _objects, _values._ListItem is a real FK to _list_items. The database prevents orphaned references — no silent data corruption.

Native storage: values live in typed columns — NUMERIC(38,18) for money, timestamptz for dates, uuid for GUIDs, bytea for binary data. Indexes work on real types, not serialized strings.

RedbObject<T> in C#

System fields: Id, ParentId, SchemeId, OwnerId, DateCreate, DateModify, Name, Note, Hash.

Props (generic TProps) — your C# class. Lazy-loaded via ILazyPropsLoader.

20+ value types: int, long, short, byte, float, double, decimal, bool, string, DateTime, DateOnly, TimeOnly, TimeSpan, Guid, char, byte[], enum — plus all Nullable<T> variants.

Containers: T[], List<T>, Dictionary<string, T>, Dictionary<int, T>, Dictionary<(int, string), T> (tuple keys), nested RedbObject<T> (single & arrays), Dictionary<string, RedbObject<T>>, nested classes and arrays inside dictionary values. RedbListItem — reference to _list_items (FK-backed enums).

Tree variant

TreeRedbObject<T> adds: Parent, Children, IsLeaf, Level, Ancestors, Descendants.

RedbPrimitive<T>

When Props is a single value (not a class), it’s stored directly in _objects._value_* columns — no _values rows needed. Supported: all numeric types, string, Guid, bool, DateTime, byte[].

Relational Collection Storage

Arrays, dictionaries, and nested documents are stored relationally in _values — not as serialized JSON. Each element is a row with typed columns, FK constraints, and indexes.

_array_parent_id (self FK) links nested structures. _array_index stores position for arrays ('0','1','2') or string keys for dictionaries. This means you can query individual array elements via SQL — WHERE, JOIN, aggregation — without deserializing anything.

Three-level uniqueness via partial unique indexes guarantees data integrity at the DB level:

  • Root fields: (structure, object) WHERE _array_index IS NULL AND _array_parent_id IS NULL
  • Nested array bases: (structure, object, parent) WHERE _array_index IS NULL AND _array_parent_id IS NOT NULL
  • Array elements: (structure, object, parent, index) WHERE _array_index IS NOT NULL

Polymorphic Scheme Types

A scheme is not just "table = class". _schemes._type (FK → _types) can be: Class, Array, Dictionary, JsonDocument, XDocument. This means RedbObject<T> can wrap a single class, an array, a dictionary, or a full JSON/XML document — all stored relationally with the same FK and index infrastructure.

Global Identity

global_identity sequence (starting at 1,000,000) generates IDs for all tables — objects, values, structures, schemes, users, roles, permissions. Every ID is globally unique across the entire database. No collisions between tables. Enables safe cross-table references and simplified export/import.

Attribute Semantics & Computed Properties

[RedbScheme("Name")] — maps class to scheme. Supports parent hint: [RedbScheme("_parent: Store")] — declares that objects of this scheme are always children of the specified parent scheme in the tree.

[RedbIgnore] — property is excluded from DB storage (not serialized to _values). Use for computed properties: [RedbIgnore] public double Margin => Price * Qty; Computed on read — zero storage cost. Works inside nested classes and arrays too.

[JsonIgnore] — different semantics: excluded from API serialization only, but stored in DB normally. Useful for internal fields that shouldn’t leak to clients.

Non-generic RedbObject: when Props is not needed, values are stored directly in _objects columns: _value_string, _value_long, _value_bool, _value_double. No _values rows, no structures. Maximum storage efficiency for simple key-value pairs.

Operator overloading on Props: Props classes can define operator + and others for aggregation patterns (e.g. summing analytics metrics in C# after loading). RedBase preserves standard C# semantics.

Query Pipeline

Two Engines, One API

Both Free and Pro editions execute real SQL against the same tables. The difference is how the C# expression becomes SQL.

Free Edition
C# Lambda
↓
BaseFilterExpressionParser
↓
FilterExpression
↓
FacetFilterBuilder
↓
JSON Facets
↓
search_objects_with_facets()
↓
Results
25+ filter operators. Supports nullable fields, array access, class properties. JSON facet format is a stable contract — call from Python, Go, Java, or raw SQL.
SQL Functions used: search_objects_with_facets, search_objects_with_facets_base, search_objects_with_projection_by_paths, search_objects_with_projection_by_ids, search_tree_objects_with_facets, search_tree_objects_with_facets_base, get_object_json, warmup_all_metadata_caches
Pro Edition
C# Lambda
↓
ProFilterExpressionParser no restrictions
↓
ExpressionToSqlCompiler per-DB implementation
↓
Native Parameterized SQL
↓
ProSqlBuilder + SqlParameterCollector
↓
Results
Direct compilation. C# expression tree → native parameterized SQL. Inner SqlExpressionVisitor walks the tree recursively. Per-DB implementation: Postgres and MSSQL each have their own compiler.
Additional Pro capabilities: Sql.Function<T>("COALESCE", ...), computed expressions in WHERE, unlimited filter nesting, Distinct/DistinctBy, combined GroupBy+Window, Pivot queries (PivotSqlGenerator — CTE-based)
Same .Where(p => p.Name == "test"). Same IRedbQueryable<T>. Free works. Pro eliminates two interpretation layers and gives the database a query plan it can actually cache. The difference is 3–10x on real workloads.

Why Pro Queries Are Faster — Interpreted vs Compiled

Free — Interpreted
C# Expression
↓
FacetFilterBuilder
↓
JSON {"Age":{"$gt":30}}
↓
search_objects_with_facets()
↓
plpgsql: parse JSON → dynamic SQL
↓
Execute — new plan every call
Two interpreters in series. No plan reuse. No parameterization.
vs
Pro — Compiled
C# Expression
↓
ExpressionToSqlCompiler recursive tree walker
↓
Parameterized SQL WHERE pvt."Age" > @p1
↓
Execute — cached plan
Direct compilation. Parameterized. Plan cached by PostgreSQL.

Free serializes your C# filter to JSON, ships it to a plpgsql procedure that parses that JSON and builds dynamic SQL with string concatenation — inside the database, on every call. PostgreSQL sees a different query string each time. No plan caching. No parameterization at the DB level.

Pro walks the expression tree in C# (SqlExpressionVisitor), emits a stable SELECT … WHERE pvt."Field" = $1 with SqlParameterCollector-managed bind variables. The database gets the same SQL text every time — prepared statement, cached plan, zero runtime interpretation. Base-field predicates (o._name, o._id) hit the primary index directly; if no Props fields are in the filter, the PVT CTE is never generated.

Value Filtering — Single-Pass Pivot vs Correlated EXISTS

When properties live in _values rows, filtering by multiple fields requires a strategy. The choice of that strategy defines the performance ceiling.

Pro — PVT CTE Single Pass
WITH pvt_cte AS (SELECT v._id_object,
(array_agg(_Long) FILTER (WHERE _id_structure = $1))[1] AS "Age",
(array_agg(_String) FILTER (WHERE _id_structure = $2))[1] AS "City"
FROM _values WHERE _id_structure = ANY($3)
GROUP BY v._id_object)
↓
WHERE pvt."Age" > $4 AND pvt."City" = $5
↓
SELECT o.* FROM _objects o JOIN pvt_cte ON ...
One scan. One GROUP BY. 5 fields or 50 — same cost. All parameters bind-safe.
vs
Free — Correlated EXISTS
WHERE o._id_scheme = $1
↓
AND EXISTS (SELECT 1 FROM _values WHERE _id_object = o._id AND _id_structure = $2 AND _Long > 30)
↓
AND EXISTS (SELECT 1 FROM _values WHERE _id_object = o._id AND _id_structure = $3 AND _String = 'NY')
↓
AND EXISTS (…) × N fields
One subquery per field. Works well for 1–3 filters. Slows down as field count grows.

Pro uses PvtSqlGenerator.GeneratePvtCte() to pivot all needed fields into flat CTE columns via a single GROUP BY v._id_object. The FILTER (WHERE _id_structure = $N) clause splits values into columns during aggregation — one index scan on (_id_structure, _id_object), one aggregation pass, done. The outer WHERE applies standard B-tree predicates to already-flat columns. All values are parameterized via SqlParameterCollector.

Free uses _build_exists_condition() — each filter field becomes a correlated EXISTS subquery against _values. For simple queries with 1–2 fields this is perfectly efficient — PostgreSQL optimizes correlated EXISTS well when indexes are in place. As the number of filter fields grows, Pro’s single-pass pivot becomes increasingly advantageous.

Materialization — Complex Objects Without the Complexity

Pro — Bulk + Parallel
1 BULK SELECT all _values
↓
1 BULK SELECT all _list_items
↓
Preload schemes + types (in-memory)
↓
Parallel.ForEach — CPU-bound, zero DB access
↓
O(1) ILookup indexes, direct type conversion
↓
RedbObject<T>
No JSON. No serialization. All cores. O(1) field access.
vs
Free / Dapper / EF Core
get_object_json() per object
↓
PostgreSQL builds JSON in plpgsql
↓
System.Text.Json.Deserialize<T>()
↓
RedbObject<T>
Serialize → transfer → deserialize. Per object. Sequential.

Consider a real entity like EmployeeProps from the examples: 8 scalar fields, 4 arrays, 3 nested classes (each with its own arrays), 5 dictionaries (including Dictionary<string, Address> and Dictionary<(int, string), string>), nested RedbObject references, RedbListItem references. In a classic ORM like EF Core, this requires ~28 tables with FK constraints, junction tables for many-to-many, separate tables for every array and dictionary, and ~40–60 INSERT operations per single object across all those tables in the right FK order. 100 employees = 4,000–6,000 INSERTs.

Dapper does not solve this at all — it maps flat rows to flat objects. For a 28-table entity graph you write the JOINs, the FK ordering, and the assembly code yourself. There is no abstraction for nested documents, arrays, or dictionaries in Dapper.

EF Core can model this, but: DbContext snapshots every tracked entity (full in-memory clone for change detection), is not thread-safe (Parallel.ForEach is structurally impossible), cache lifetime = DbContext lifetime (one HTTP request), and INSERT ordering across 28 tables with cascading FKs is a known pain point.

Pro stores the same EmployeeProps in 2 tables (_objects + _values). Loading: 2 bulk SELECTs, then ProLazyPropsLoader indexes everything into ILookup and runs Parallel.ForEach — pure CPU, O(1) per field. ProPropsMaterializer handles 20+ value types, nested objects, arrays, dictionaries, tuple-key dictionaries, ListItem references — all from flat _values rows, with no JSON step. On repeat loads, GlobalPropsCache returns the object from hash-validated memory — no SQL at all. Saving: single BulkInsert via PostgreSQL COPY protocol. 100 employees ≈ 3,000 rows, one command.

Projection — Load Only What You Select

.Select(x => new { x.Props.Name, x.Props.City }) — a projection tells the engine exactly which fields you need. Both editions analyze the C# expression tree at runtime via ProjectionFieldExtractor. What happens next is fundamentally different.

Free — SQL Projection Function
ProjectionFieldExtractor
↓
Extract fieldPaths + structureIds
↓
Filter → JSON → plpgsql interpreter
↓
search_objects_with_projection_by_paths
↓
build_flat_projection() → partial JSON
↓
Deserialize JSON → partial Props
↓
compiledProjection(obj) → TResult
Projection reads only requested _values rows via SQL function. Filter goes through JSON → plpgsql dynamic SQL.
vs
Pro — Compiled SQL + Filtered _values
ProjectionFieldExtractor
↓
Extract structureIds HashSet<long>
↓
BuildQuerySqlAsync → compiled WHERE + PVT CTE
↓
SELECT from _objects → matching object IDs
↓
ProLazyPropsLoader: _values WHERE _id_structure = ANY($2)
↓
Parallel.ForEach materialization no JSON
↓
compiledProjection(obj) → TResult
No plpgsql. No JSON serialization. Compiled WHERE with cached plan. _values filtered by structureIds → parallel materialization.

Free: single SQL function. search_objects_with_projection_by_paths does everything in one plpgsql call — resolves text paths to structure_ids via resolve_field_path(), filters objects, then calls build_flat_projection() to construct minimal JSON per object with only the requested fields. The JSON is deserialized into a partial RedbObject<TProps> (non-projected fields stay default), then compiledProjection extracts the needed values into TResult. SkipPropsLoading = true ensures no redundant re-load overwrites partial Props.

Pro: two-phase compiled pipeline. Pro takes a completely different path. ProQueryProvider.ExecuteToListAsync never calls search_objects_with_projection_by_paths. Instead: BuildQuerySqlAsync compiles the .Where() into parameterized SQL with a PVT CTE (if Props fields are filtered), executes a standard SELECT on _objects to get matching object IDs and base fields. No JSON. No plpgsql interpreter.

Then ProLazyPropsLoader.LoadPropsForManyAsync(objects, projectedStructureIds) loads only the projected _values rows: SELECT * FROM _values WHERE _id_object = ANY($1) AND _id_structure = ANY($2). Object has 30 fields — query reads 2. The result goes through the parallel materialization pipeline: ILookup grouping, scheme preload, Parallel.ForEach with ProPropsMaterializer. No JSON parsing, no deserialization — flat _values rows are assembled directly into C# properties. Partial Props are not cached (they are incomplete by design).

Comparison with ORMs. EF Core projection (.Select(x => new { x.Name })) generates SELECT "Name" FROM "Employees" — column-level projection on a flat table. Simple and fast for flat models. But when your entity has 28 related tables (nested objects, collections, dictionaries), EF Core requires .Include() chains or manual DTOs with JOINs — no expression-tree analysis to auto-detect which related entities are actually needed. RedBase does this automatically: one .Select() expression, and the engine reads only the matching _values rows.

This Is Not a Wrapper. Read the Code.

There is no DbContext inside. No EF dependency. No Dapper. No hidden SqlCommand. Every layer listed below was written from scratch and exists in the repository:

  • Expression compiler — ExpressionToSqlCompiler. Recursive SqlExpressionVisitor walks Binary, Unary, MethodCall, Member, Conditional, Constant nodes. Emits parameterized SQL per dialect. Separate implementations for PostgreSQL and MS SQL.
  • Pivot query generator — PvtSqlGenerator. Converts N property fields into one flat CTE via GROUP BY + FILTER. Handles nested objects, dictionaries, arrays, ListItem references. Generates array_agg / STRING_AGG per DB.
  • Materialization engine — ProLazyPropsLoader + ProPropsMaterializer: 2 bulk SELECTs → ILookup indexing → Parallel.ForEach. 20+ type handlers. Nested recursive loading. Zero serialization.
  • Storage schema — 6 tables, 3 levels of partial unique indexes, FK constraints between all entities, global identity sequence. Not an abstraction over someone else’s tables — the tables are the product.
  • Cache system — GlobalPropsCache with SHA-256 hash validation. GlobalMetadataCache for schemes and structures. GlobalListCache with TTL. Domain-isolated. Thread-safe. Cross-request.
  • Change tracking — ValueTreeBuilder constructs two trees (memory vs DB), ValueTreeComparer diffs them with hash-based subtree skip. Only changed nodes emit SQL. No delete-all/re-insert.

Dapper is a mapper. EF Core is a change tracker with a query translator. RedBase is a data engine — compiler, query planner, materializer, storage schema, cache, diff algorithm — built for workloads with complex nested entities, deep property graphs, and document-like structures that ORMs were never designed to handle.

ExpressionToSqlCompiler — What Gets Compiled

The inner SqlExpressionVisitor walks C# expression trees and produces native SQL. Every node type has a dedicated handler:

Binary & Unary

Arithmetic: +, -, *, /, %. Comparison: >, >=, <, <=, ==, !=. Logical: AND, OR, NOT. Null coalesce: x ?? default → COALESCE(x, default). Null check: x == null → IS NULL. Convert: pass-through for type casts.

Math.*

Math.Abs → ABS(), Math.Round → ROUND(), Math.Floor → FLOOR(), Math.Ceiling → CEIL(), Math.Max → GREATEST(), Math.Min → LEAST(). Multi-argument support (e.g. Round(x, 2)).

String.*

Contains → LIKE '%' || @p || '%', StartsWith → LIKE @p || '%', EndsWith → LIKE '%' || @p, ToUpper/ToLower → UPPER()/LOWER(), Trim → TRIM(). All parameterized — no SQL injection.

Collections

dict["key"] → pvt."Dict[key]" (PVT column reference). dict.ContainsKey("k") → pvt."Dict[k]" IS NOT NULL. list.Contains(val) → val = ANY(pvt."List"). Nested property paths: p.Address.City → structure ID resolution via SchemeFieldResolver.

ProSqlBuilder adds: filter compilation with recursive tree walking, expression-based ORDER BY (arithmetic, functions, custom SQL in ordering), pivot subquery generation via PivotSqlGenerator, aggregate column building, and type-aware SQL casting. SqlParameterCollector ensures injection safety across the entire pipeline.

IRedbQueryable<T> API surface

Where(), WhereRedb(), WhereIn(), OrderBy() / OrderByDescending(), Take(), Skip(), Select<TResult>() → IRedbProjectedQueryable<TResult>, Distinct(), DistinctBy(), ToListAsync(), CountAsync(), FirstOrDefaultAsync(), AnyAsync(), AllAsync(), WithMaxRecursionDepth(), WithLazyLoading().

Tree queries add: WhereHasAncestor, WhereHasDescendant, WhereLevel, WhereRoots, WhereLeaves. Scoped: TreeQuery<T>(rootObjectId, maxDepth), multi-root: TreeQuery<T>(rootIds).

Pro Engine

C# → SQL Compilation Pipeline

Pro doesn't interpret expressions — it compiles them. ExpressionToSqlCompiler walks the C# expression tree node-by-node and emits native parameterized SQL. ProSqlBuilder handles filter trees, arithmetic, functions, ordering, pivot subqueries. Per-database implementations for PostgreSQL and MS SQL.

Binary Operators

Arithmetic: +, -, *, /, %. Comparison: ==, !=, >, >=, <, <=. Logical: &&, ||. Null coalescing: ?? → COALESCE(x, y). String concatenation: + → || (PostgreSQL).

Math Functions

Math.Abs → ABS(), Math.Round → ROUND(), Math.Floor → FLOOR(), Math.Ceiling → CEIL(), Math.Max → GREATEST(), Math.Min → LEAST(). Compiled inline — no UDF overhead.

String Functions

.Contains() → LIKE '%' || @p || '%', .StartsWith() → LIKE @p || '%', .EndsWith() → LIKE '%' || @p, .ToUpper() → UPPER(), .ToLower() → LOWER(), .Trim() → TRIM(), .Length → LENGTH(). Case-insensitive variants via ILIKE.

DateTime & Property Access

.Year → EXTRACT(YEAR FROM col), .Month, .Day, .Hour, .Minute, .Second. Nested properties: x.Address.City → pvt."Address.City". Unlimited nesting depth via GetPropertyPath() chain walker.

Collection Operations

x.Tags.Contains("v") → @p = ANY(pvt."Tags"). x.Tags.Count → COALESCE(array_length(pvt."Tags", 1), 0). Dictionary: x.Dict["key"] → pivot column. x.Dict.ContainsKey("k") → IS NOT NULL. Array element access: x.Roles[].Value → = ANY(col).

Advanced

Sql.Function<T>("COALESCE", ...) — inject arbitrary SQL functions. Closure capture: var age = 30; x => x.Age > age — evaluated and parameterized. Nullable.GetValueOrDefault() — pass-through. Unary ! → NOT, type conversions pass through.

30+ Comparison Operators in ProSqlBuilder

Beyond basic =, <>, >, <: Contains, StartsWith, EndsWith (case-sensitive LIKE), ContainsIgnoreCase, StartsWithIgnoreCase, EndsWithIgnoreCase (ILIKE). Array operators: ArrayContains, ArrayAny, ArrayEmpty, ArrayCount, ArrayCountGt/Gte/Lt/Lte. Dictionary: ContainsKey → IS NOT NULL. All parameterized. All injection-safe.

Pivot Query Generation — PivotSqlGenerator

Turns structured property storage into flat rows for queries. Generates multi-CTE SQL: GeneratePvtCte() for flat fields, GenerateNestedDictCte() for nested dictionary keys, GenerateNestedOnlyPvtCte() for nested-only access. Dictionary fields like PhoneBook["home"] become real columns in the pivot. Array fields aggregated with array_agg(). Separate Postgres and MSSQL implementations.

Expression SQL Caching

ExpressionSqlCache — ConcurrentDictionary keyed by expression string hash. Compiled SQL from C# expressions is cached so repeated queries skip the full compilation walk. Cache is thread-safe and shared across the IRedbService lifetime.

Edge-Case Compilation

Nullable navigation: r.Auction != null && r.Auction.Costs > 100 → compiles to IS NOT NULL + nested property access. No NullReferenceException at SQL level.

Ternary in OrderBy: r.Auction != null ? r.Auction.Baskets : 0 → CASE WHEN pvt."Auction" IS NOT NULL THEN pvt."Auction.Baskets" ELSE 0 END. Full conditional sorting without post-processing.

StringComparison: .Contains(val, StringComparison.OrdinalIgnoreCase) → ILIKE. Case-insensitive search compiled natively, not via LOWER() wrapper.

ListItem in queries: x.Status.Value == "Active" → join to _list_items + filter on _name. WhereIn for multiple ListItem values. array.Any(s => s == x.Status) — ListItem IDs compiled as = ANY(@p).

WhereInRedb: .WhereInRedb(x => x.Name, names) → filters on base _objects columns (_id, _name, _note) via IN clause. Combinable with .Where() on Props fields in the same query.

Analytics

Aggregation, GroupBy, Window Functions

All analytics run as SQL on the database — not in C# memory. Aggregation, grouping, and window functions execute inside dedicated stored procedures, returning only the final result.

Aggregation

SumAsync, AverageAsync, MinAsync, MaxAsync, CountAsync — single-field aggregates. GetStatisticsAsync — all five in one call (parallel SQL).

AggregateAsync for multi-field: 

await query.AggregateAsync(x => new {
    Total = Agg.Sum(x.Props.Amount),
    Avg   = Agg.Avg(x.Props.Price),
    Count = Agg.Count()
});

SQL: aggregate_field(), aggregate_batch(). Array aggregation: Agg.Sum(x.Props.Items.Select(i => i.Price)).

GroupBy

GroupBy(x => x.Category) → IRedbGroupedQueryable → SelectAsync. Also GroupByRedb for base fields, GroupByArray for array elements. 

await query
    .GroupBy(x => x.Category)
    .SelectAsync(g => new {
        Category = g.Key,
        Total = Agg.Sum(g, x => x.Stock),
        Count = Agg.Count(g)
    });

SQL: aggregate_grouped(), aggregate_array_grouped(). GroupBy + WithWindow for combined analytics.

Window Functions

WithWindow(w => w.PartitionBy(...).OrderBy(...)) → IRedbWindowedQueryable → SelectAsync. 

await query
    .WithWindow(w => w
        .PartitionBy(x => x.Category)
        .OrderByDesc(x => x.Stock))
    .SelectAsync(x => new {
        x.Props.Name,
        Rank = Win.RowNumber()
    });

Ranking: RowNumber, Rank, DenseRank, Ntile. Offset: Lag, Lead, FirstValue, LastValue. Aggregates: Sum, Avg, Count, Min, Max OVER (...). Frame: ROWS/RANGE BETWEEN with FrameSpec. SQL: query_with_window().

Tree-scoped analytics: TreeQuery().GroupBy() — aggregation scoped to a subtree (not global). TreeQuery().WithWindow() — window functions within tree hierarchy: RowNumber(), RunningTotal(), ranking — all computed within the selected subtree. CreateBatchChildAsync — factory for batch creation of child objects under a parent.

SQL Preview — ToSqlString()

Like EF Core ToQueryString(). Call ToSqlStringAsync() or ToFilterJsonAsync() on any query — flat, tree, grouped, or windowed — to see the exact SQL that will execute. GroupBy preview via aggregate_grouped_preview() and aggregate_batch_preview(). Search preview via get_search_sql_preview() / get_search_tree_sql_preview().

Performance

Three-Level Caching

Not an afterthought. Caching is built into the core with domain isolation, hash-based validation, and TTL management.

GlobalMetadataCache

Scheme, type, and CLR type caching. Domain-isolated via ConcurrentDictionary. Internal lookup maps: SchemeByName, SchemeById, TypeById, SchemeNameToClrType, ClrTypeToSchemeId.

GlobalPropsCache

Full RedbObject caching with hash validation. Get<T>(objectId, hash) — returns cached object only if hash matches DB. MemoryRedbObjectCache backend: TTL, max size, per-user quotas.

GlobalListCache

Lists and list items. TTL-based (default 5 min). Caches: ListsById, ListsByName, ItemsByListId, ItemsById.

Database-Level Metadata Cache

Beyond the C# caches, RedBase maintains a _scheme_metadata_cache table in the database itself. Automatic triggers keep it in sync:

  • sync_metadata_cache_on_hash_change() — trigger on _schemes: recalculates cache when scheme hash changes
  • cleanup_metadata_cache_on_scheme_delete() — removes cache entries when scheme is deleted
  • check_metadata_cache_consistency() — validates cache integrity, reports stale/missing/orphaned entries
  • warmup_all_metadata_caches() — called on InitializeAsync, preloads all scheme metadata in one pass

This means metadata stays consistent even if multiple app instances share the same DB, or if schema changes happen outside the application (e.g., direct SQL migration).

Lazy Loading — Two-Phase Optimization

WithLazyLoading(true) or global EnableLazyLoadingForProps. Phase 1: loads only _objects base fields (fast, lightweight). Phase 2: on first .Props access, loads all values in bulk via LoadPropsForManyAsync for the entire result set. No N+1 problem — one batch query instead of one per object.

SQL: get_object_base_fields(), execute_objects_query_base(), get_filtered_object_ids(). Eager fallback via get_object_json() for single-object loads.

Portability

One Codebase, Multiple Databases

PostgreSQL and MS SQL Server. Same models, same queries, same trees. The provider abstraction handles dialect differences. And you can use multiple databases in a single application simultaneously.

ISqlDialect

Each database implements its own SQL dialect: parameter syntax, type mapping, function names. Pro extends this with ISqlDialectPro for migration SQL and materialization SQL. Your code never calls dialect methods directly.

Export & Import

ExportService writes FK-safe JSONL: Types → Lists → Schemes → Structures → Roles → Users → Objects → Values → Permissions. ImportService streams line-by-line, bulk-inserts via IDataProvider. Compressed .redb (ZIP) or plain JSONL.

Batch ID Generation

IRedbContext provides NextObjectIdBatchAsync(count) and NextValueIdBatchAsync(count) for high-throughput inserts. Each DB implements its own sequence strategy.

Multi-Database — Domain Isolation

Connect two or more databases in the same process — each gets its own IRedbService instance with fully isolated caches, schemes, and providers. No cross-contamination. 

// Primary database — PostgreSQL
services.AddRedbPro(o => o.UsePostgres(mainConnection));

// Secondary database — SQL Server (separate DI scope)
var docServices = new ServiceCollection();
docServices.AddRedbPro(o => o.UseMsSql(docConnection));
var redbDoc = docServices.BuildServiceProvider()
    .GetRequiredService<IRedbService>();
await redbDoc.InitializeAsync();

Each IRedbService computes a CacheDomain (from connection string or explicit config). All three cache levels — GlobalMetadataCache, GlobalPropsCache, GlobalListCache — are partitioned by domain via static ConcurrentDictionary<string, DomainCache>. Schemes from database A never appear in queries to database B.

Mix Postgres + MSSQL in one app, run a Postgres primary with an MSSQL analytics sidecar, or separate read/write databases — all with the same IRedbService API.

Security

Users, Roles, Permissions — Built In

Not bolted on. System tables _users, _roles, _permissions are created by InitializeAsync. Works without ASP.NET Identity — in console apps, Blazor, background services.

IUserProvider

CreateUserAsync(CreateUserRequest), ValidateUserAsync(login, password), ChangePasswordAsync(user, currentPwd, newPwd), GetUsersAsync(UserSearchCriteria?), DeleteUserAsync (soft-delete, system users 0/1 protected).

IRoleProvider

CreateRoleAsync, AssignUserToRoleAsync(user, role), RemoveUserFromRoleAsync. Role deletion cascades permissions.

IPermissionProvider

CanUserEditObject, CanUserSelectObject, CanUserInsertScheme, CanUserDeleteObject. Reads from IRedbSecurityContext (ambient per-request user scope).

Password Hashing

IPasswordHasher / SimplePasswordHasher — SHA256 + per-user salt. Pluggable: replace with bcrypt/scrypt via DI.

Hierarchical Permission Inheritance

Permissions propagate through the object tree via recursive CTE (up to 50 levels). The system finds the nearest ancestor with explicit permissions and applies them. Global permissions (_id_ref = 0) serve as fallback. Priority: user > role.

get_user_permissions_for_object() — recursive search up the tree, stops at first match. System user (id=0) gets full access without recursion. auto_create_node_permissions() trigger — when a child object is inserted, automatically creates permissions on the parent if missing, reducing recursion depth on subsequent lookups.

Database-Level Validation Triggers

Data integrity is enforced at the SQL level — even direct database access cannot corrupt the schema:

  • validate_structure_name() — rejects field names that violate C# naming rules, start with digits, use reserved words, or clash with system columns. Max 64 characters.
  • validate_scheme_name() — validates scheme names with namespace support (MyApp.Orders), nested classes (Order+Item), rejects empty parts, consecutive dots. System schemes (@__deleted) bypass validation.
  • protect_system_users() — prevents deletion or renaming of system users (id=0 sys, id=1 admin). Password/email/status changes are allowed.
Production

Built for Production

Features that companies build for months — included out of the box.

Schema Validation

IValidationProvider checks C# models against the database at startup. ValidateSchemaAsync<T>(), AnalyzeSchemaChangesAsync<T>(), ValidatePropertyConstraints. Detects type mismatches, missing structures, and schema drift before they hit production.

Soft Delete + Background Purge

SoftDeleteAsync atomically moves objects (and all their descendants via recursive CTE) into a trash container with scheme @__deleted. Optional trashParentId parameter lets you place the trash container under any parent (e.g. a user's recycle bin) — or null for root level. Deleted objects instantly disappear from all queries and lists — no extra filters needed. The trash container stores progress: total, deleted count, status. BackgroundDeletionService picks it up via Channel and purges data in batches (configurable batchSize). ON DELETE CASCADE handles related values automatically. On restart, RecoverOrphanedTasksAsync finds interrupted tasks and resumes from where they stopped. Cluster-safe: TryClaimOrphanedTaskAsync ensures only one instance claims each task.

Change Tracking — Diff Tree Save (Pro)

EavSaveStrategy.ChangeTracking. Instead of DeleteInsert (delete all + re-insert), Pro builds two ValueTreeNode trees — one from memory, one from DB — and runs a structural diff to generate only the minimal set of SQL operations.

Pipeline: ValueTreeBuilder.BuildTreeFromMemory() serializes C# object → flat RedbValue list → tree. BuildTreeFromDB() loads existing values → same tree structure. BuildTreeFromFlat() reconstructs parent-child hierarchy via _array_parent_id, builds StructureMap for O(1) lookup, assigns Hash per node.

Diff algorithm (ValueTreeDiff.CompareTreesWithHash): groups nodes by structure_id, then per group: CompareNodeGroup detects array vs scalar. CompareNodes — if both nodes have Hash and hashes match → skip entire subtree (no value comparison, no child traversal). CompareArrayElements — index-based matching of array items, detects inserts, deletes, and updates per element. Output: List<TreeChange> with types Insert, Delete, Update, Skip. Only changed nodes produce SQL — unchanged subtrees are free.

Data Migrations (Pro)

Fluent API (like EF Core IEntityTypeConfiguration): .Property(p => p.Field).ComputedFrom(p => p.A * p.B), .DefaultValue("value"), .Transform(v => v.Replace("-", "")), .OnTypeChange<string, decimal>().Using(v => decimal.Parse(v)), .When(p => condition). MigrationDiscovery finds implementations automatically. MigrationExpressionCompiler compiles C# to SQL UPDATEs. History stored in DB, idempotent (hash-based change detection).

Parallel Materialization Pipeline (Pro)

ProLazyPropsLoader.LoadPropsForManyAsync — 7-step pipeline with Parallel.ForEach:

  1. Bulk cache check — FilterNeedToLoad<T>() via hash: skip objects already in GlobalPropsCache
  2. One bulk SELECT — all _values for all object IDs in a single query
  3. Bulk preload ListItems — one query for all referenced _list_items
  4. Preload schemes + types — load scheme structures and type registry once, before parallelism
  5. Parallel.ForEach materialization — ProPropsMaterializer.GetObjectProps<T>() runs on thread pool; no DB access inside parallel loop
  6. Recursive nested object loading — MaterializeNestedObjectsDynamically() resolves nested RedbObject references with cycle detection
  7. Substitution — SubstituteNestedObjects() replaces ID placeholders with loaded objects in parent Props

ProPropsMaterializer handles hierarchical property assembly: BuildHierarchicalPropertiesOptimized<T>, BuildArrayField, BuildDictionaryField, ConvertValue (30+ type mappings). Thread-safe: all state passed in, no shared mutable data. Infinite recursion protection via ConcurrentDictionary<long, byte> _loadingInProgress.

Structure Tree Introspection

SQL-level schema reflection: get_scheme_structure_tree() returns the full field hierarchy for a scheme. get_structure_children(), get_structure_descendants() — navigate field trees. validate_structure_tree() — checks structural integrity (orphaned fields, circular refs, type mismatches). Powers schema sync, migration planning, and diagnostic tools.

Type Migration

migrate_structure_type(structureId, oldType, newType, dryRun) — change a field’s type in-place. get_value_column(typeName) resolves the target column. Supports dry-run for impact analysis. Available via ISqlDialect.Schemes_SyncMetadataCache().

Bulk Operations — COPY Protocol

IBulkOperations — 8 methods for high-throughput data manipulation. BulkInsertObjectsAsync / BulkInsertValuesAsync — uses PostgreSQL COPY binary protocol (or MSSQL SqlBulkCopy) for maximum insert speed. BulkUpdateObjectsAsync / BulkUpdateValuesAsync — temp table + UPDATE FROM pattern: COPY into temp, then single UPDATE JOIN. BulkDeleteObjectsAsync / BulkDeleteValuesAsync / BulkDeleteValuesByObjectIdsAsync / BulkDeleteValuesByListItemIdsAsync. All respect FK constraints and cascades. Used internally by ImportService and AddNewObjectsAsync.

Polymorphic Bulk Load & Parent Chain

LoadAsync(IEnumerable<long> objectIds) — polymorphic bulk load. Loads objects of different schemes in one call, resolves CLR types at runtime via SetTypeResolver. LoadWithParentsAsync<T>() — 8 overloads. Loads object with full parent chain to root. Returns TreeRedbObject<T> with populated Parent property. Bulk variants share common parent references (deduplication). Polymorphic variants via ITreeRedbObject for mixed-scheme trees.

Polymorphic Tree Operations

LoadPolymorphicTreeAsync, GetPolymorphicChildrenAsync, GetPolymorphicPathToRootAsync, GetPolymorphicDescendantsAsync — tree operations where child nodes can have different schemes. InitializeTypeRegistryAsync() maps scheme IDs to CLR types at startup. Returns ITreeRedbObject with runtime type resolution.

Configuration Presets

PredefinedConfigurations — 8 named profiles: Default, Development, Production, BulkOperations, HighPerformance, Debug, IntegrationTesting, DataMigration. Each profile tunes 35+ settings: cache sizes, TTLs, lazy loading, validation, hash computation. IsProductionSafe(), IsPerformanceOptimized() — runtime introspection. GetByName() for configuration-driven profile selection.

Batch Save & Mixed Operations

SaveAsync(IEnumerable<IRedbObject>) — save a mix of new and existing objects in one call. Automatically detects which objects need INSERT vs UPDATE. Works with both EavSaveStrategy.DeleteInsert and ChangeTracking. AddNewObjectsAsync — optimized bulk insert for new objects only (uses COPY protocol internally). Performance: batch save of 100 objects ~10x faster than sequential SaveAsync per object.

Performance

40+ Optimized Indexes

Every index is justified by EXPLAIN ANALYZE on real queries. Redundant indexes are explicitly removed with documented reasoning.

Covering Indexes

INCLUDE contains all value types for Index Only Scan — no table access needed. IX__values__object_structure_lookup includes every typed column. Measured: cost reduction 30%+ on faceted queries.

Partial Indexes

WHERE _array_index IS NULL reduces index size 30-40%. WHERE _String IS NOT NULL for targeted NOT NULL queries. WHERE _id_parent IS NULL for fast root object lookup.

GIN Trigram Search

pg_trgm extension for LIKE/ILIKE pattern matching without full table scan. Powers $contains, $startsWith, $endsWith, $matches (regex) operators.

Internals

Serialization & Type Resolution

IRedbObjectSerializer handles object ↔ JSON conversion. Default implementation uses System.Text.Json.

SystemTextJsonRedbSerializer

Deserialize<TProps>(string json) — typed deserialization.
DeserializeDynamic(string json, Type propsType) — runtime type resolution.
SetTypeResolver(Func<long, Type?>) — maps scheme IDs → CLR types for polymorphic load.

Schema Field Resolution

SchemeFieldResolver (Pro) resolves C# property paths to structure IDs. Cached per scheme. Used by ProSqlBuilder and ExpressionToSqlCompiler to map p.Address.City to the correct structure ID in the database.

Documentation

195+ Working Examples

Every example hits a real database. Each is tagged with [ExampleMeta]: id, title, category, tier (ExampleTier.Free / Pro / Enterprise), difficulty (1-5), tags, related APIs.

174+ Free examples
21 Pro examples
5 Difficulty levels
100% Real DB execution

Free examples cover

Bulk Insert, CRUD, Schema Sync, Nested Objects, Trees, LINQ Queries, GroupBy, Window Functions, Aggregation, Security (Users, Roles, Permissions), Export/Import, Soft Delete, Lists, Validation.

Pro examples cover

Deep Nesting (E041), Distinct Queries (E139-E140), Arithmetic/String/Math/DateTime Expressions (E151-E159), Tree Expressions (E160), Sql.Function (E161-E163), GroupBy+Window combined (E175, E195), TreeDistinct (E176-E177, E180).

Browse all examples →

By the Numbers

0 Migrations needed. Ever.
60+ SQL functions & triggers
40+ Optimized indexes
50+ Built-in data types
195+ Working examples
8 NuGet packages
10+ Provider interfaces
2 Database backends
.NET 8+ net8.0, net9.0, net10.0
We eat our own cooking. This documentation site runs on ASP.NET Blazor with RedBase as its data layer. Every page, example, and API reference you see is stored and served as a REDB object.

Not a toy. Not a wrapper. A data engine.

Expression compiler, PVT query planner, parallel materializer, structural diff engine, three-level cache — all built from scratch. Free MIT edition covers CRUD, queries, trees, security, export. Pro adds speed and advanced expressions.

dotnet new install redb.Templates && dotnet new redb -n MyApp
Quick Start See Pricing Browse Examples