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; }
}

Seed volume: employees as a reporting tree

Seeds a large company: Employee objects arranged as a reporting tree.
Every employee but the first reports to a manager, and every manager has
direct reports, so 100000 employees form a tree six levels deep.
The same objects answer both kinds of query:
redb.Query<EmployeeProps>().Where(e => e.Salary > 90000) // everyone
redb.TreeQuery<EmployeeProps>(managerId).Where(e => e.Salary > 90000) // one manager's people
dotnet run --project redb.Examples -- E203
REDB_SEED_COUNT=20000 dotnet run --project redb.Examples -- E203
The whole-suite sweep never seeds. Seeding runs only when the example is NAMED on the
command line (default 100000 employees). REDB_SEED_COUNT overrides the size (0 disables even an
explicit run), REDB_SEED_BATCH the batch (default 1000).
Objects have the shape E000 builds: about 60 _values rows each, with arrays, nested classes
and dictionaries, so 100000 employees land around 6M rows in _values.
This example WRITES a lot. It appends and never clears: every run adds one more company with
its own head, so running it twice doubles the data. It ends by refreshing planner statistics
through redb.Maintenance.AnalyzeAsync(); after a bulk load the planner's estimates are
stale until that is done.
// Seeding volume is a decision, not a side effect. Being NAMED on the command line is
// that decision; being reached by the whole-suite sweep is not - the sweep always skips.
var isExplicitRun = ExplicitlyRequestedIds?.Contains("E203") == true;
var total = ReadInt("REDB_SEED_COUNT", isExplicitRun ? 100_000 : 0);
var batchSize = ReadInt("REDB_SEED_BATCH", 1_000);

if (total == 0)
{
    return Ok("E203", "Seed volume: employees as a reporting tree", ExampleTier.Pro, 0,
    [
        "Skipped: the whole-suite sweep never seeds.",
        "To seed: dotnet run --project redb.Examples -- E203   (default 100000)",
        "REDB_SEED_COUNT overrides the size, REDB_SEED_BATCH the batch (default 1000).",
        "100000 employees land around 6M rows in _values and take ~20 minutes.",
        "It appends and never clears, so running it twice doubles the data."
    ]);
}

if (total < 0 || batchSize <= 0)
    return Fail("E203", "Seed volume: employees as a reporting tree", ExampleTier.Pro, 0,
        "REDB_SEED_COUNT must not be negative and REDB_SEED_BATCH must be positive");

// Continue numbering after whatever is already there, so names and codes stay unique
// across runs.
var existing = await redb.Query<EmployeeProps>().CountAsync();

Console.WriteLine();
Console.WriteLine($"Seeding {total:N0} employees in batches of {batchSize:N0}, {DirectReports} direct reports per manager");
Console.WriteLine($"Already in scheme: {existing:N0}. New objects start at index {existing:N0}.");
Console.WriteLine($"Expect roughly {total * 60L:N0} rows in _values. This appends, it never clears.");
Console.WriteLine();

var sw = Stopwatch.StartNew();

// All ids up front: a manager's id has to be known before the reports are created.
var ids = await redb.Context.NextObjectIdBatchAsync(total);

var batchTimer = new Stopwatch();
var seeded = 0;
var batches = 0;

// Bulk-load mode: the plain secondary indexes of _values and _objects go before the load and are
// rebuilt after it, over the whole tables. Keys stay: primary keys, unique indexes and the foreign
// keys resting on them check every row as usual. Dropping and rebuilding are inside the measured time.
var droppedIndexes = await SetAsidePlainIndexesAsync(redb);
var rebuildTimer = new Stopwatch();
try
{
    while (seeded < total)
    {
        var take = Math.Min(batchSize, total - seeded);

        batchTimer.Restart();
        var employees = E000_BulkInsert.CreateEmployees(take, existing + seeded);
        for (var i = 0; i < employees.Count; i++)
        {
            // Position n reports to position (n - 1) / DirectReports; position 0 heads the
            // company. A manager always comes earlier than the reports, so it is already saved
            // or sits in the same batch.
            var position = seeded + i;
            employees[i].id = ids[position];
            employees[i].parent_id = position == 0 ? null : ids[(position - 1) / DirectReports];
        }

        await redb.AddNewObjectsAsync(employees.Cast<IRedbObject<EmployeeProps>>().ToList());
        batchTimer.Stop();

        seeded += employees.Count;
        batches++;

        var pct = seeded * 100.0 / total;
        var overallRate = seeded * 1000.0 / Math.Max(sw.ElapsedMilliseconds, 1);
        var remaining = overallRate > 0
            ? TimeSpan.FromSeconds((total - seeded) / overallRate)
            : TimeSpan.Zero;

        Console.WriteLine(
            $"[{seeded,8:N0}/{total:N0}] {pct,5:F1}%  " +
            $"batch {batchTimer.ElapsedMilliseconds,6:N0} ms  " +
            $"avg {overallRate,7:F0} obj/s  " +
            $"elapsed {sw.Elapsed:hh\\:mm\\:ss}  " +
            $"ETA {remaining:hh\\:mm\\:ss}");
    }
}
finally
{
    // Also after a failed batch: a database left without its indexes would slow every later query.
    rebuildTimer.Start();
    await RestoreIndexesAsync(redb, droppedIndexes);
    rebuildTimer.Stop();
}

sw.Stop();

var finalCount = await redb.Query<EmployeeProps>().CountAsync();
var rate = seeded * 1000.0 / Math.Max(sw.ElapsedMilliseconds, 1);

// A bulk load leaves the planner's statistics behind reality. The Maintenance facade runs
// the provider's own refresh (ANALYZE / sp_updatestats / PRAGMA analysis_limit + ANALYZE),
// so the seed hands back a database that is ready to be queried and measured.
Console.WriteLine();
Console.WriteLine("Refreshing planner statistics (redb.Maintenance.AnalyzeAsync)...");
var analyzeSw = Stopwatch.StartNew();
await redb.Maintenance.AnalyzeAsync();
analyzeSw.Stop();
Console.WriteLine();

var levels = 1;
for (long reach = 1, width = 1; reach < total; levels++)
{
    width *= DirectReports;
    reach += width;
}
DropPostgresIndexesAsync
private static async Task<List<string>> DropPostgresIndexesAsync(IRedbService redb)
{
    const string plainIndexes = """
        FROM pg_index x
        JOIN pg_class i ON i.oid = x.indexrelid
        JOIN pg_class t ON t.oid = x.indrelid
        JOIN pg_namespace n ON n.oid = t.relnamespace
        WHERE n.nspname = current_schema()
          AND t.relname IN ('_values', '_objects')
          AND NOT x.indisunique
          AND NOT EXISTS (SELECT 1 FROM pg_constraint c WHERE c.conindid = x.indexrelid)
        """;

    var definitions = await redb.Context.QueryScalarListAsync<string>(
        $"SELECT pg_get_indexdef(x.indexrelid) {plainIndexes} ORDER BY i.relname");
    var names = await redb.Context.QueryScalarListAsync<string>(
        $"SELECT format('%I', i.relname) {plainIndexes} ORDER BY i.relname");

    Console.WriteLine($"Bulk-load mode: dropping {names.Count} plain indexes of _values and _objects (keys stay).");
    foreach (var name in names)
        await redb.Context.ExecuteAsync($"DROP INDEX IF EXISTS {name}");

    return definitions;
}
DisableSqlServerIndexesAsync
private static async Task<List<string>> DisableSqlServerIndexesAsync(IRedbService redb)
{
    var targets = await redb.Context.QueryScalarListAsync<string>("""
        SELECT QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
        FROM sys.indexes i
        JOIN sys.tables t ON t.object_id = i.object_id
        JOIN sys.schemas s ON s.schema_id = t.schema_id
        WHERE s.name = SCHEMA_NAME()
          AND t.name IN ('_values', '_objects')
          AND i.type = 2
          AND i.is_unique = 0
          AND i.is_primary_key = 0
          AND i.is_unique_constraint = 0
          AND i.is_disabled = 0
        ORDER BY i.name
        """);

    Console.WriteLine($"Bulk-load mode: disabling {targets.Count} plain indexes of _values and _objects (keys stay).");
    foreach (var target in targets)
        await redb.Context.ExecuteAsync($"ALTER INDEX {target} DISABLE");

    return targets.Select(target => $"ALTER INDEX {target} REBUILD").ToList();
}
DropSqliteIndexesAsync
private static async Task<List<string>> DropSqliteIndexesAsync(IRedbService redb)
{
    const string plainIndexes = """
        FROM sqlite_master
        WHERE type = 'index'
          AND tbl_name IN ('_values', '_objects')
          AND sql IS NOT NULL
          AND upper(sql) NOT LIKE 'CREATE UNIQUE%'
        """;

    var definitions = await redb.Context.QueryScalarListAsync<string>($"SELECT sql {plainIndexes} ORDER BY name");
    var names = await redb.Context.QueryScalarListAsync<string>(
        $"SELECT '\"' || replace(name, '\"', '\"\"') || '\"' {plainIndexes} ORDER BY name");

    Console.WriteLine($"Bulk-load mode: dropping {names.Count} plain indexes of _values and _objects (keys stay).");
    foreach (var name in names)
        await redb.Context.ExecuteAsync($"DROP INDEX IF EXISTS {name}");

    return definitions;
}
RestoreIndexesAsync
private static async Task RestoreIndexesAsync(IRedbService redb, List<string> restoreStatements)
{
    if (restoreStatements.Count == 0)
        return;

    Console.WriteLine();
    Console.WriteLine($"Bulk-load mode: rebuilding {restoreStatements.Count} indexes...");
    var sw = Stopwatch.StartNew();
    foreach (var statement in restoreStatements)
        await redb.Context.ExecuteAsync(statement);
    sw.Stop();
    Console.WriteLine($"Bulk-load mode: indexes rebuilt in {sw.ElapsedMilliseconds:N0} ms");
}
ReadInt
private static int ReadInt(string name, int fallback)
{
    var raw = Environment.GetEnvironmentVariable(name);
    return int.TryParse(raw, out var parsed) ? parsed : fallback;
}
SetupBulkInsertTreeBenchmarkIRedbService.AddNewObjectsAsyncIRedbContext.NextObjectIdBatchAsync
PG Pro 748385ms(100000)
postgres.ef 0ms
MS Pro 651875ms(100000)
sqlite.pro 1227290ms(100000)