Code Examples

Data Model - EmployeeProps Click to view data model used in all examples
Tag with priority for categorization.
/// <summary>
/// Tag with priority for categorization.
/// </summary>
public class Tag
{
    public string Name { get; set; } = string.Empty;
    public int Priority { get; set; }
    public string? Description { get; set; }
}

/// <summary>
/// Project metrics for analytics.
/// </summary>
[RedbScheme("ProjectMetrics")]
public class ProjectMetricsProps
{
    public long ProjectId { get; set; }
    public long? TasksCompleted { get; set; }
    public long? TasksTotal { get; set; }
    public long? BugsFixed { get; set; }
    public double? Budget { get; set; }
    public long? TeamSize { get; set; }
    public Tag[]? Tags { get; set; }
    public string[]? Technologies { get; set; }
}

/// <summary>
/// Address with city, street and building details.
/// </summary>
public class Address
{
    public string City { get; set; } = string.Empty;
    public string Street { get; set; } = string.Empty;
    public BuildingInfo? Building { get; set; }
}

/// <summary>
/// Building information with floor and amenities.
/// </summary>
public class BuildingInfo
{
    public int Floor { get; set; }
    public string Name { get; set; } = string.Empty;
    public string[]? Amenities { get; set; }
    public int[]? AccessCodes { get; set; }
    public string[]? ParkingSpots { get; set; }
    public int[]? ElevatorFloors { get; set; }
}

/// <summary>
/// Contact detail key-value pair.
/// </summary>
public class ContactDetail
{
    public string Label { get; set; } = string.Empty;
    public string Value { get; set; } = string.Empty;
}

/// <summary>
/// Contact information (email, phone, etc).
/// </summary>
public class Contact
{
    public string Type { get; set; } = string.Empty;
    public string Value { get; set; } = string.Empty;
    public bool IsVerified { get; set; }
    public int[]? NotificationHours { get; set; }
    public ContactDetail[]? Metadata { get; set; }
}

/// <summary>
/// Department info with tags, metrics and nested budget data.
/// </summary>
public class Department
{
    public string Name { get; set; } = string.Empty;
    public int HeadCount { get; set; }
    public string[] Projects { get; set; } = [];
    public Tag[] Leaders { get; set; } = [];
    public Dictionary<string, int>? BudgetByYear { get; set; }
}

/// <summary>
/// Employee props - main model for examples.
/// Demonstrates all supported types:
/// - Simple types (int, string, DateTime, long, decimal)
/// - Arrays (string[], int[])
/// - Business classes (Address with nested BuildingInfo)
/// - Array of business classes (Contact[])
/// - RedbObject references (CurrentProject, PastProjects[])
/// - Dictionary types (various key and value types)
/// </summary>
[RedbScheme("Employee")]
public class EmployeeProps
{
    // Basic info
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public int Age { get; set; }
    public DateTime HireDate { get; set; }
    public string Position { get; set; } = string.Empty;
    public decimal Salary { get; set; }
    public string Department { get; set; } = string.Empty;
    public string? EmployeeCode { get; set; }

    // Skills and certifications (arrays)
    public string[]? Skills { get; set; }
    public int[]? SkillLevels { get; set; }
    public string[]? Certifications { get; set; }
    public int[]? CertificationYears { get; set; }

    // Addresses (business classes)
    public Address? HomeAddress { get; set; }
    public Address? WorkAddress { get; set; }
    public Address? EmergencyAddress { get; set; }

    // Contacts (array of business classes)
    public Contact[]? Contacts { get; set; }

    // Project references (RedbObject)
    public RedbObject<ProjectMetricsProps>? CurrentProject { get; set; }
    public RedbObject<ProjectMetricsProps>[]? PastProjects { get; set; }

    // Phone directory: extension -> phone number
    public Dictionary<string, string>? PhoneDirectory { get; set; }

    // Office locations by city
    public Dictionary<string, Address>? OfficeLocations { get; set; }

    // Bonus history by year
    public Dictionary<int, decimal>? BonusByYear { get; set; }

    // Department details with complex nested data (Pro)
    public Dictionary<string, Department>? DepartmentHistory { get; set; }

    // Composite key: (year, quarter) -> performance score (Pro)
    public Dictionary<(int Year, string Quarter), string>? PerformanceReviews { get; set; }

    // Project metrics by code (Pro)
    public Dictionary<string, RedbObject<ProjectMetricsProps>>? ProjectMetrics { get; set; }
}

/// <summary>
/// Department props for tree examples (corporate hierarchy).
/// Used with TreeRedbObject for organizational structure.
/// Similar to CategoryTestProps in ConsoleTest.
/// </summary>
[RedbScheme("Department")]
public class DepartmentProps
{
    /// <summary>Department name (e.g. "IT Department Moscow").</summary>
    public string Name { get; set; } = string.Empty;

    /// <summary>Description (e.g. "Software development team").</summary>
    public string Description { get; set; } = string.Empty;

    /// <summary>Is department active?</summary>
    public bool IsActive { get; set; } = true;

    /// <summary>Budget in USD.</summary>
    public decimal Budget { get; set; }

    /// <summary>Department code (e.g. "IT-MSK-DEV").</summary>
    public string Code { get; set; } = string.Empty;
}

/// <summary>
/// City props for list examples (linked objects).
/// Used as target object for RedbListItem.Object reference.
/// </summary>
[RedbScheme("City")]
public class CityProps
{
    /// <summary>City name.</summary>
    public string Name { get; set; } = string.Empty;
    
    /// <summary>Population count.</summary>
    public int Population { get; set; }
    
    /// <summary>Region or district.</summary>
    public string Region { get; set; } = string.Empty;
    
    /// <summary>Is capital city?</summary>
    public bool IsCapital { get; set; }
    
    /// <summary>GPS coordinates [lat, lon].</summary>
    public double[] Coordinates { get; set; } = [];
}

/// <summary>
/// Person props demonstrating ListItem fields.
/// Shows single ListItem and List of ListItems usage.
/// </summary>
[RedbScheme("Person")]
public class PersonProps
{
    /// <summary>Person name.</summary>
    public string Name { get; set; } = string.Empty;
    
    /// <summary>Age in years.</summary>
    public int Age { get; set; }
    
    /// <summary>Email address.</summary>
    public string Email { get; set; } = string.Empty;
    
    /// <summary>Single ListItem field (e.g. status from dictionary).</summary>
    public RedbListItem? Status { get; set; }
    
    /// <summary>Array of ListItems (e.g. roles from dictionary).</summary>
    public List<RedbListItem>? Roles { get; set; }
}

/// <summary>
/// Product with a unique business key (V4 [RedbUnique]): the SKU is unique per scheme,
/// enforced by the database over the hash of the canonical value. Used by E003.
/// </summary>
public class ProductProps
{
    /// <summary>Unique business key. NULL never participates in uniqueness.</summary>
    [RedbUnique]
    public string? Sku { get; set; }

    /// <summary>Display name (not unique).</summary>
    public string Title { get; set; } = string.Empty;

    /// <summary>Price in USD.</summary>
    public decimal Price { get; set; }
}

PVT prefilter in tree queries

Tree counterpart of E202: dumps the SQL a tree query generates with and without the PVT
prefilter, and checks that the row count is unchanged. See docs/PVT_PREFILTER_PLAN.md.
dotnet run --project redb.Examples -- E204 (off)
REDB_PVT_PREFILTER=1 dotnet run --project redb.Examples -- E204 (on)
A subtree already bounds the work, unlike a flat query which always scans the whole scheme,
so the prefilter only starts paying once the subtree itself is large. Measured on a 6-level
tree of 99200 nodes: 1189 -> 401 ms and 716677 -> 333755 buffers, identical result sets.
Needs a tree with real depth under the Employee scheme: E203 seeds one, employees arranged as a
reporting tree with ten direct reports per manager. Without depth this example still runs, it
just measures very little.
The differential check here is by COUNT rather than by id set: a tree query over a large
subtree returns tens of thousands of objects and materialising them all would dwarf the
query being measured. Full id-set equality was verified separately in psql.
var sw = Stopwatch.StartNew();

// What the service actually runs with: Program.cs may set it without the environment variable.
var enabled = redb.Configuration.EnablePvtPrefilter;

var root = await redb.TreeQuery<EmployeeProps>().WhereRoots().FirstOrDefaultAsync();
if (root == null)
{
    sw.Stop();
    return Fail("E204", "PVT prefilter in tree queries", ExampleTier.Pro, sw.ElapsedMilliseconds,
        "No Employee tree found. Seed one with E203.");
}

var log = new StringBuilder();
var summary = new List<string>();
var withPrefilter = 0;

log.AppendLine($"-- EnablePvtPrefilter = {(enabled ? "true (enabled)" : "false (disabled)")}");
log.AppendLine($"-- root id = {root.Id}");
log.AppendLine();

async Task CaseAsync(string label, string expectation, IRedbQueryable<EmployeeProps> query)
{
    log.AppendLine("-----------------------------------------------------------------");
    log.AppendLine($"-- {label}");
    log.AppendLine($"-- expected when enabled: {expectation}");
    log.AppendLine("-----------------------------------------------------------------");

    string sql;
    try
    {
        sql = await query.ToSqlStringAsync();
    }
    catch (Exception ex)
    {
        log.AppendLine($"-- SQL BUILD FAIL: {ex.GetType().Name}: {ex.Message}");
        log.AppendLine();
        summary.Add($"{label}: SQL build failed");
        Console.WriteLine($"[BUILD-FAIL] {label}: {ex.Message}");
        return;
    }

    log.AppendLine(sql);
    // Console.WriteLine();
    // Console.WriteLine($"--- {label} (expected when enabled: {expectation}) ---");
    // Console.WriteLine(sql);

    var hasRow = sql.Contains("AND ((v._id_structure");
    // Dropping _objects oo is the second half of the change and only fires together
    // with the prefilter; surfacing it separately makes a half-applied change visible.
    var droppedObjects = sql.Contains("FROM tree t\n") || sql.Contains("    FROM tree t");
    var form = hasRow ? (droppedObjects ? "Row+" : "Row") : "none";
    if (hasRow) withPrefilter++;

    string count;
    var timer = Stopwatch.StartNew();
    try
    {
        count = (await query.CountAsync()).ToString();
    }
    catch (Exception ex)
    {
        count = $"EXEC FAIL {ex.GetType().Name}: {ex.Message.Split('\n')[0]}";
    }
    timer.Stop();

    log.AppendLine($"-- prefilter form: {form}");
    log.AppendLine($"-- count: {count} in {timer.ElapsedMilliseconds} ms");
    log.AppendLine();

    summary.Add($"{label}: {form}, count={count}");
    Console.WriteLine($"[{form,5}] {label} -> count={count} in {timer.ElapsedMilliseconds} ms");
}

// The text-search shape: one needle across two string fields. Row form plus the dropped
// _objects join, and pg_trgm finally reaches the column.
await CaseAsync(
    "OR: two string fields, same needle",
    "Row+ (prefilter and _objects join dropped)",
    redb.TreeQuery<EmployeeProps>(root.Id)
        .Where(e => e.Position.Contains("Design") || e.Department.Contains("Design")));

// Two predicates on one field merge into a single selective branch.
await CaseAsync(
    "AND: range on one field",
    "Row+ (both bounds merged)",
    redb.TreeQuery<EmployeeProps>(root.Id)
        .Where(e => e.Salary >= 60000m && e.Salary < 90000m));

// Dates use _datetimeoffset and a different parameter round-trip.
await CaseAsync(
    "AND: date range",
    "Row+",
    redb.TreeQuery<EmployeeProps>(root.Id)
        .Where(e => e.HireDate >= new DateTime(2030, 1, 1) && e.HireDate < new DateTime(2031, 1, 1)));

// Pivot not covered: Position and Age have no branch of their own, so the guard suppresses
// the prefilter rather than nulling their columns out.
await CaseAsync(
    "AND: three fields, one selective",
    "none (pivot not covered)",
    redb.TreeQuery<EmployeeProps>(root.Id)
        .Where(e => e.Position != "" && e.Age >= 30 && e.Salary > 70000m));

// Null check switches the CTE to the LEFT JOIN form, where dropping rows would blur
// "absent" and "present but not matching".
await CaseAsync(
    "AND: null check plus selective field",
    "none (hasNullCheck)",
    redb.TreeQuery<EmployeeProps>(root.Id)
        .Where(e => e.EmployeeCode == null && e.Salary > 70000m));

string? outPath = null;
try
{
    var dir = Path.Combine(AppContext.BaseDirectory, "Output");
    Directory.CreateDirectory(dir);
    outPath = Path.Combine(dir, enabled ? "tree_prefilter_on.txt" : "tree_prefilter_off.txt");
    await File.WriteAllTextAsync(outPath, log.ToString());
}
catch (Exception ex)
{
    Console.WriteLine($"[WARN] failed to write dump file: {ex.Message}");
}

sw.Stop();

// The run time above is the sum over all the queries of the example, each a count over the
// whole subtree of the root, so the count of queries comes first: without it the figure reads
// as one slow query.
var output = new List<string>
{
    $"{summary.Count} queries in this example: SQL printed, then each counted over the subtree of root {root.Id}",
    $"Prefilter: {(enabled ? "ENABLED" : "disabled")}, applied in {withPrefilter} of {summary.Count} cases"
};
output.AddRange(summary);
output.AddRange(new[]
{
    outPath != null ? $"Output: {outPath}" : "Output file: <not written>",
    "Diff tree_prefilter_off.txt against tree_prefilter_on.txt: counts must match exactly"
});
SqlDebugPrefilterProPvtTreeIRedbService.TreeQueryIRedbQueryable.ToSqlStringAsync
PG Pro 5344ms(5)
postgres.pro.repeat 3070ms(5)
postgres.pro.cache 3282ms(5)
MS Pro 6039ms(5)
mssql.pro.repeat 8009ms(5)
mssql.pro.cache 4559ms(5)
sqlite.pro 2210ms(5)
sqlite.pro.repeat 2205ms(5)
sqlite.pro.cache 2190ms(5)