Примеры кода

Изучайте RedBase на примерах. Просматривайте рабочие примеры кода для LINQ-запросов, массовых операций, древовидных структур и многого другого.

Data Model - EmployeeProps Нажмите, чтобы увидеть модель данных, используемую во всех примерах
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; }
}

Update - Existing Object

Demonstrates updating an existing object by loading, modifying, and saving it.
Shows the standard update pattern in REDB: Load -> Modify -> Save.
var sw = Stopwatch.StartNew();

// Create an object to update
var scheme = await redb.EnsureSchemeFromTypeAsync<EmployeeProps>();
var original = new RedbObject<EmployeeProps>
{
    name = $"ToUpdate_{DateTime.UtcNow.Ticks}",
    scheme_id = scheme.Id,
    Props = new EmployeeProps
    {
        FirstName = "Original",
        LastName = "Name",
        Age = 30,
        Salary = 60000m,
        Department = "Sales"
    }
};
await redb.SaveAsync(original);
var originalSalary = original.Props.Salary;

// Load the object
var loaded = await redb.LoadAsync<EmployeeProps>(original.Id);
if (loaded == null)
{
    sw.Stop();
    return Fail("E187", "Update - Existing Object", ExampleTier.Free, sw.ElapsedMilliseconds,
        "Failed to load object for update.");
}

// Modify Props
loaded.Props.FirstName = "Updated";
loaded.Props.Salary = 75000m; // Raise!
loaded.Props.Department = "Engineering";

// Save changes (same ID = update)
await redb.SaveAsync(loaded);

// Verify changes
var verified = await redb.LoadAsync<EmployeeProps>(original.Id);

sw.Stop();

// Cleanup
await redb.DeleteAsync(original.Id);

if (verified == null)
{
    return Fail("E187", "Update - Existing Object", ExampleTier.Free, sw.ElapsedMilliseconds,
        "Failed to verify updated object.");
}
UpdateModifySaveIRedbService.LoadAsyncIRedbService.SaveAsync
MS Pro 278ms(1)
mssql.pro.cache 147ms(1)
mssql.pro.repeat 180ms(1)
postgres.ef 1060ms(1)
postgres.ef.repeat 1019ms(1)
PG Pro 185ms(1)
postgres.pro.cache 126ms(1)
postgres.pro.repeat 107ms(1)
sqlite.pro 35ms(1)
sqlite.pro.cache 8ms(1)
sqlite.pro.repeat 8ms(1)

Soft-Delete - Nested Reference Not Materialized

Verifies the soft-delete read-path fix (_id_scheme = -10).
A surviving Employee references a Project via the RedbObject _Object edge
(EmployeeProps.CurrentProject). When the Project is soft-deleted
(moved to trash, _id_scheme = -10 — NOT purged), loading the Employee must
NOT materialize the tombstone through the nested reference.
Invariant checked (edition-agnostic):
loaded.CurrentProject?.Props == null
Free nulls the reference outright (get_object_json returns NULL for the
trashed target); Pro leaves an id-only placeholder with Props == null
(the trashed target is filtered out of Materialization_SelectObjectsByIds,
so it is never substituted). Either way the trashed object is not loaded as
live data. The _values._Object pointer is left intact, so the reference
returns once the target is restored from trash.
var sw = Stopwatch.StartNew();

var projectScheme = await redb.EnsureSchemeFromTypeAsync<ProjectMetricsProps>();
var employeeScheme = await redb.EnsureSchemeFromTypeAsync<EmployeeProps>();

// Project that the employee will reference.
var project = new RedbObject<ProjectMetricsProps>
{
    name = $"SD_Project_{DateTime.UtcNow.Ticks}",
    scheme_id = projectScheme.Id,
    Props = new ProjectMetricsProps
    {
        ProjectId = 2001,
        TasksCompleted = 10,
        TasksTotal = 20,
        Budget = 250000
    }
};
await redb.SaveAsync(project);

// Surviving employee with an _Object reference to the project.
var employee = new RedbObject<EmployeeProps>
{
    name = $"SD_Employee_{DateTime.UtcNow.Ticks}",
    scheme_id = employeeScheme.Id,
    Props = new EmployeeProps
    {
        FirstName = "Jane",
        LastName = "Survivor",
        Age = 40,
        Salary = 95000m,
        Department = "Engineering",
        CurrentProject = project
    }
};
await redb.SaveAsync(employee);

// Sanity: before soft-delete the nested reference materializes fully.
var before = await redb.LoadAsync<EmployeeProps>(employee.Id);
var beforeOk = before?.Props?.CurrentProject?.Props != null;

// Soft-delete the referenced project: it moves to trash (_id_scheme = -10),
// it is NOT physically purged, and the _values._Object pointer stays intact.
await redb.SoftDeleteAsync(new[] { project.Id });

// Reload the survivor (cache is off in the examples, so this re-materializes).
var after = await redb.LoadAsync<EmployeeProps>(employee.Id);
var nested = after?.Props?.CurrentProject;
var tombstoneLeaked = nested?.Props != null;          // trashed object materialized as live data
var placeholderLeaked = nested != null;               // id-only placeholder left behind (no Free/Pro parity)

sw.Stop();

// Cleanup: drop the survivor first (removes the _Object pointer), then the
// trashed project (now unreferenced, so the hard delete is unblocked).
await redb.DeleteAsync(employee.Id);
await redb.DeleteAsync(project.Id);

if (!beforeOk)
{
    return Fail("E188", "Soft-Delete - Nested Reference Not Materialized", ExampleTier.Free,
        sw.ElapsedMilliseconds,
        "Sanity check failed: CurrentProject.Props was null BEFORE soft-delete (reference never loaded).");
}

if (tombstoneLeaked)
{
    return Fail("E188", "Soft-Delete - Nested Reference Not Materialized", ExampleTier.Free,
        sw.ElapsedMilliseconds,
        "REGRESSION: soft-deleted (_id_scheme=-10) project was still materialized through the nested reference.");
}

if (placeholderLeaked)
{
    return Fail("E188", "Soft-Delete - Nested Reference Not Materialized", ExampleTier.Free,
        sw.ElapsedMilliseconds,
        "PARITY GAP: trashed reference left as an id-only placeholder instead of null (Free returns null).");
}
SoftDeleteReferenceTrash-10IRedbService.SoftDeleteAsyncRedbObject<TProps>
MS Pro 377ms(1)
mssql.pro.cache 301ms(1)
mssql.pro.repeat 324ms(1)
PG Pro 234ms(1)
postgres.pro.cache 184ms(1)
postgres.pro.repeat 193ms(1)
sqlite.pro 20ms(1)
sqlite.pro.cache 7ms(1)
sqlite.pro.repeat 12ms(1)

SumAsync - With Filter

SumAsync with Where filter applied before aggregation.
Filter employees by department, then calculate total salary.
Tests Pro filter handling in Aggregation.
var sw = Stopwatch.StartNew();

// Uncomment to see generated SQL:
//var sql = await redb.Query<EmployeeProps>()
//    .Where(x => x.Department == "Engineering")
//    .ToSqlStringAsync();
//Console.WriteLine(sql);

// Sum salaries only for Engineering department
var engineeringSalary = await redb.Query<EmployeeProps>()
    .Where(x => x.Department == "Engineering")
    .SumAsync(x => x.Salary);

// Compare with total (no filter)
var totalSalary = await redb.Query<EmployeeProps>()
    .SumAsync(x => x.Salary);

// Filter and sum on nested classes: home city one level deep, floor two levels deep
var berlinFloors = await redb.Query<EmployeeProps>()
    .Where(x => x.HomeAddress!.City == "Berlin")
    .SumAsync(x => x.HomeAddress!.Building!.Floor);

sw.Stop();
SumWhereFilterAggregationIRedbQueryable.SumAsyncIRedbQueryable.Where
MS Pro 225ms(1)
mssql.pro.cache 275ms(1)
mssql.pro.repeat 350ms(1)
postgres.ef 77ms(1)
postgres.ef.repeat 78ms(1)
PG Pro 134ms(1)
postgres.pro.cache 136ms(1)
postgres.pro.repeat 134ms(1)
sqlite.pro 342ms(1)
sqlite.pro.cache 260ms(1)
sqlite.pro.repeat 294ms(1)

Window - With Filter

Window function with Where filter applied before windowing.
Filter employees by salary threshold, then apply ROW_NUMBER.
Tests Pro filter handling in Window queries.
var sw = Stopwatch.StartNew();

// Window with filter: only high earners (Salary > 70000)
var windowQuery = redb.Query<EmployeeProps>()
    .Where(x => x.Salary > 70000m)
    .Take(20)
    .WithWindow(w => w
        .PartitionBy(x => x.Department)
        .OrderByDesc(x => x.Salary));

// Uncomment to see generated SQL:
//var sql = await windowQuery.ToSqlStringAsync(x => new { x.Props.FirstName, Rank = Win.RowNumber() });
//Console.WriteLine(sql);

var ranked = await windowQuery.SelectAsync(x => new
{
    Name = x.Props.FirstName,
    Department = x.Props.Department,
    Salary = x.Props.Salary,
    Rank = Win.RowNumber()
});

sw.Stop();

var top = ranked.FirstOrDefault();
WindowWhereFilterRowNumberIRedbQueryable.WithWindowIRedbQueryable.WhereWin.RowNumber
MS Pro 177ms(20)
mssql.pro.cache 589ms(20)
mssql.pro.repeat 603ms(20)
PG Pro 372ms(20)
postgres.pro.cache 375ms(20)
postgres.pro.repeat 381ms(20)
sqlite.pro 650ms(20)
sqlite.pro.cache 371ms(20)
sqlite.pro.repeat 397ms(20)

AggregateAsync - With Filter

Multiple aggregations with Where filter applied before aggregation.
Filter employees by position, then calculate Sum, Avg, Min, Max.
Tests Pro filter handling in batch Aggregation.
var sw = Stopwatch.StartNew();

// Aggregate only Developers
var query = redb.Query<EmployeeProps>()
    .Where(x => x.Position == "Developer" && x.HomeAddress!.City != "Tokyo");

// Uncomment to see generated SQL:
// var sql = await query.ToSqlStringAsync();
// Console.WriteLine(sql);

var stats = await query.AggregateAsync(x => new
{
    TotalSalary = Agg.Sum(x.Props.Salary),
    AvgSalary = Agg.Average(x.Props.Salary),
    MinSalary = Agg.Min(x.Props.Salary),
    MaxSalary = Agg.Max(x.Props.Salary),
    MinFloor = Agg.Min(x.Props.HomeAddress!.Building!.Floor),
    MaxFloor = Agg.Max(x.Props.HomeAddress!.Building!.Floor),
    AvgFloor = Agg.Average(x.Props.HomeAddress!.Building!.Floor),
    Count = Agg.Count()
});

sw.Stop();
AggregateWhereFilterSumAvgIRedbQueryable.AggregateAsyncIRedbQueryable.Where
MS Pro 262ms(1)
mssql.pro.cache 265ms(1)
mssql.pro.repeat 266ms(1)
postgres.ef 143ms(1)
postgres.ef.repeat 139ms(1)
PG Pro 124ms(1)
postgres.pro.cache 127ms(1)
postgres.pro.repeat 126ms(1)
sqlite.pro 110ms(1)
sqlite.pro.cache 76ms(1)
sqlite.pro.repeat 86ms(1)

AverageAsync - With Filter

AverageAsync with Where filter applied before aggregation.
Filter employees by age range, then calculate average salary.
Tests Pro filter handling in single Aggregation.
var sw = Stopwatch.StartNew();

// Average salary for employees aged 30-40
var avgSalaryMiddle = await redb.Query<EmployeeProps>()
    .Where(x => x.Age >= 30 && x.Age <= 40)
    .AverageAsync(x => x.Salary);

// Compare with overall average
var avgSalaryAll = await redb.Query<EmployeeProps>()
    .AverageAsync(x => x.Salary);

// Filter and average on nested classes: home city one level deep, floor two levels deep
var avgFloorLondon = await redb.Query<EmployeeProps>()
    .Where(x => x.HomeAddress!.City == "London")
    .AverageAsync(x => x.HomeAddress!.Building!.Floor);

sw.Stop();
AverageWhereFilterAggregationIRedbQueryable.AverageAsyncIRedbQueryable.Where
MS Pro 201ms(1)
mssql.pro.cache 281ms(1)
mssql.pro.repeat 340ms(1)
postgres.ef 79ms(1)
postgres.ef.repeat 82ms(1)
PG Pro 151ms(1)
postgres.pro.cache 146ms(1)
postgres.pro.repeat 146ms(1)
sqlite.pro 165ms(1)
sqlite.pro.cache 162ms(1)
sqlite.pro.repeat 184ms(1)

MinMaxAsync - With Filter

MinAsync/MaxAsync with Where filter applied before aggregation.
Filter employees by department, then find salary range.
Tests Pro filter handling in single Aggregation.
var sw = Stopwatch.StartNew();

// Salary range for Sales department only
var query = redb.Query<EmployeeProps>()
    .Where(x => x.Department == "Sales");

var minSalary = await query.MinAsync(x => x.Salary);
var maxSalary = await query.MaxAsync(x => x.Salary);

// Compare with overall range
var minAll = await redb.Query<EmployeeProps>().MinAsync(x => x.Salary);
var maxAll = await redb.Query<EmployeeProps>().MaxAsync(x => x.Salary);

// Filter and range on nested classes: home city one level deep, floor two levels deep
var berlin = redb.Query<EmployeeProps>()
    .Where(x => x.HomeAddress!.City == "Berlin");
var minFloor = await berlin.MinAsync(x => x.HomeAddress!.Building!.Floor);
var maxFloor = await berlin.MaxAsync(x => x.HomeAddress!.Building!.Floor);

sw.Stop();
MinMaxWhereFilterAggregationIRedbQueryable.MinAsyncIRedbQueryable.MaxAsyncIRedbQueryable.Where
MS Pro 449ms(1)
mssql.pro.cache 546ms(1)
mssql.pro.repeat 693ms(1)
postgres.ef 133ms(1)
postgres.ef.repeat 136ms(1)
PG Pro 267ms(1)
postgres.pro.cache 270ms(1)
postgres.pro.repeat 265ms(1)
sqlite.pro 455ms(1)
sqlite.pro.cache 352ms(1)
sqlite.pro.repeat 335ms(1)

GroupBy + Window - With Filter

GroupBy + Window with Where filter applied before grouping.
Filter employees by salary, group by department, then rank groups.
Tests Pro filter handling in GroupedWindow queries.
var sw = Stopwatch.StartNew();

// Filter high earners, group by department, rank by total salary
var ranked = await redb.Query<EmployeeProps>()
    .Where(x => x.Salary > 60000m)
    .GroupBy(x => x.Department)
    .WithWindow(w => w.OrderByDesc(g => Agg.Sum(g, x => x.Salary)))
    .SelectAsync(g => new
    {
        Department = g.Key,
        TotalSalary = Agg.Sum(g, x => x.Salary),
        Count = Agg.Count(g),
        Rank = Win.Rank()
    });

//var sql = await redb.Query<EmployeeProps>()
//    .Where(x => x.Salary > 60000m)
//    .GroupBy(x => x.Department)
//    .WithWindow(w => w.OrderByDesc(g => Agg.Sum(g, x => x.Salary)))
//    .ToSqlStringAsync(g => new
//    {
//        Department = g.Key,
//        TotalSalary = Agg.Sum(g, x => x.Salary),
//        Count = Agg.Count(g),
//        Rank = Win.Rank()
//    });
//Console.WriteLine(sql);

sw.Stop();

var top = ranked.FirstOrDefault();
GroupByWindowWhereFilterRankIRedbGroupedQueryable.WithWindowIRedbQueryable.Where
MS Pro 164ms(5)
mssql.pro.cache 572ms(5)
mssql.pro.repeat 591ms(5)
PG Pro 300ms(5)
postgres.pro.cache 280ms(5)
postgres.pro.repeat 275ms(5)
sqlite.pro 365ms(5)
sqlite.pro.cache 316ms(5)
sqlite.pro.repeat 388ms(5)

SQL dump for smoke cases (Pro vs PVT)

Dumps SQL generated for a representative set of LINQ queries that mirror
the cases in redb.Postgres/sql/v2-pvt/99_smoke_auto.sql.
Designed so that running it against the Pro provider produces a SQL log
that can be compared side-by-side with the SQL produced by free v2-pvt
(pvt_build_query_sql).
The example does not execute any data queries. It only invokes
for each
query and writes the resulting SQL to:
  • the console (RAISE-style banner per case);
  • a text file at Output/smoke_sql_dump.txt in the example output
directory, so it can be diffed with the v2-pvt INSPECT block.
var sw = Stopwatch.StartNew();

var log = new StringBuilder();
int ok = 0, fail = 0;

Task EmployeeAsync(string label, Func<IRedbQueryable<EmployeeProps>, IRedbQueryable<EmployeeProps>> build)
    => DumpAsync<EmployeeProps>(label, () => build(redb.Query<EmployeeProps>()).Take(100));

Task PersonAsync(string label, Func<IRedbQueryable<PersonProps>, IRedbQueryable<PersonProps>> build)
    => DumpAsync<PersonProps>(label, () => build(redb.Query<PersonProps>()).Take(100));

Task CityAsync(string label, Func<IRedbQueryable<CityProps>, IRedbQueryable<CityProps>> build)
    => DumpAsync<CityProps>(label, () => build(redb.Query<CityProps>()).Take(100));

Task DepartmentAsync(string label, Func<IRedbQueryable<DepartmentProps>, IRedbQueryable<DepartmentProps>> build)
    => DumpAsync<DepartmentProps>(label, () => build(redb.Query<DepartmentProps>()).Take(100));

async Task DumpAsync<TProps>(string label, Func<IRedbQueryable<TProps>> build) where TProps : class, new()
{
    log.AppendLine("-----------------------------------------------------------------");
    log.AppendLine($"-- {label}");
    log.AppendLine("-----------------------------------------------------------------");
    IRedbQueryable<TProps>? q = null;
    try
    {
        q = build();
    }
    catch (Exception ex)
    {
        log.AppendLine($"-- BUILD FAIL: {ex.GetType().Name}: {ex.Message}");
        Console.WriteLine($"[BUILD-FAIL] {label}: {ex.Message}");
        fail++;
        log.AppendLine();
        return;
    }
    try
    {
        var sql = await q.ToSqlStringAsync();
        log.AppendLine(sql);
        Console.WriteLine($"[OK]   {label}");
        ok++;
    }
    catch (Exception ex)
    {
        log.AppendLine($"-- SQL FAIL: {ex.GetType().Name}: {ex.Message}");
        Console.WriteLine($"[FAIL] {label}: {ex.Message}");
        fail++;
    }
    log.AppendLine();
}

// ---------------- Employee: scalar predicates --------------------
await EmployeeAsync("eq string",                q => q.Where(e => e.FirstName == "Ivan"));
await EmployeeAsync("ne string",                q => q.Where(e => e.FirstName != "Ivan"));
await EmployeeAsync("in string",                q => q.Where(e => new[] { "IT", "HR" }.Contains(e.Department)));
await EmployeeAsync("nin string",               q => q.Where(e => !new[] { "Sales" }.Contains(e.Department)));
await EmployeeAsync("range int",                q => q.Where(e => e.Age >= 18 && e.Age <= 65));
await EmployeeAsync("numeric gt decimal",       q => q.Where(e => e.Salary > 1000m));
await EmployeeAsync("numeric lt decimal",       q => q.Where(e => e.Salary < 1_000_000m));
await EmployeeAsync("numeric range",            q => q.Where(e => e.Salary >= 0m && e.Salary <= 1_000_000m));
await EmployeeAsync("null check",               q => q.Where(e => e.EmployeeCode == null));
await EmployeeAsync("notNull check",            q => q.Where(e => e.EmployeeCode != null));
await EmployeeAsync("startsWith",               q => q.Where(e => e.FirstName.StartsWith("A")));
await EmployeeAsync("endsWith",                 q => q.Where(e => e.LastName.EndsWith("ov")));
await EmployeeAsync("contains",                 q => q.Where(e => e.Position.Contains("eng")));
await EmployeeAsync("containsIgnoreCase",       q => q.Where(e => e.Position.ToLower().Contains("eng")));
await EmployeeAsync("startsWithIgnoreCase",     q => q.Where(e => e.FirstName.ToLower().StartsWith("a")));
await EmployeeAsync("endsWithIgnoreCase",       q => q.Where(e => e.LastName.ToLower().EndsWith("ov")));
await EmployeeAsync("$and+$or",                 q => q.Where(e => e.Age > 18 && (e.Department == "IT" || e.Department == "HR")));
await EmployeeAsync("$or top-level",            q => q.Where(e => e.Department == "IT" || e.Department == "HR"));
await EmployeeAsync("$not",                     q => q.Where(e => !(e.Age < 18)));
await EmployeeAsync("$not + $and",              q => q.Where(e => !(e.Age < 18 && e.Department == "IT")));
await EmployeeAsync("multi-field AND merge",    q => q.Where(e => e.Department == "IT" && e.Age > 18));
await EmployeeAsync("date gt",                  q => q.Where(e => e.HireDate > new DateTime(2020, 1, 1)));
await EmployeeAsync("date lte",                 q => q.Where(e => e.HireDate <= new DateTime(2030, 1, 1)));
await EmployeeAsync("date range",               q => q.Where(e => e.HireDate >= new DateTime(2000, 1, 1) && e.HireDate < new DateTime(2030, 1, 1)));

// ---------------- Employee: base-field pushdown (WhereRedb) ------
await EmployeeAsync("base _id gt",              q => q.WhereRedb(o => o.Id > 0));
await EmployeeAsync("base 0$:Name startsWith",  q => q.WhereRedb(o => o.Name.StartsWith("E")));
await EmployeeAsync("pushdown _date_create gte",q => q.WhereRedb(o => o.DateCreate >= new DateTimeOffset(2000, 1, 1, 0, 0, 0, TimeSpan.Zero)));
await EmployeeAsync("pushdown base+props AND",  q => q.WhereRedb(o => o.Id > 0).Where(e => e.FirstName.StartsWith("A")));
await EmployeeAsync("pushdown $or all-base",    q => q.WhereRedb(o => o.Id < 10 || o.Id > 100));
await EmployeeAsync("pushdown $not base",       q => q.WhereRedb(o => !(o.Id < 0)));
await EmployeeAsync("pushdown nested $and+$or", q => q.WhereRedb(o => o.Id > 0).Where(e => e.Department == "IT" || e.Department == "HR"));

// ---------------- Employee: expression engine (B2) ---------------
await EmployeeAsync("expr arithmetic gt",       q => q.Where(e => e.Age + 1 > 18));
await EmployeeAsync("expr mul base pushdown",   q => q.WhereRedb(o => o.Id * 2 < 1_000_000));
await EmployeeAsync("expr upper eq",            q => q.Where(e => e.FirstName.ToUpper() == "IVAN"));
await EmployeeAsync("expr concat ilike",        q => q.Where(e => (e.FirstName + " " + e.LastName).ToLower().Contains("van")));
await EmployeeAsync("expr coalesce eq",         q => q.Where(e => (e.EmployeeCode ?? "none") == "IT"));
await EmployeeAsync("expr length gt",           q => q.Where(e => e.FirstName.Length > 0));
await EmployeeAsync("expr abs sub",             q => q.Where(e => Math.Abs(e.Age - 30) < 1000));
await EmployeeAsync("expr salary * 12",         q => q.Where(e => e.Salary * 12m > 1_000_000m));

// ---------------- Employee: arrays -------------------------------
await EmployeeAsync("array contains",           q => q.Where(e => e.Skills!.Contains("C#")));
await EmployeeAsync("array length gt",          q => q.Where(e => e.Skills!.Length > 0));
await EmployeeAsync("array length eq 3",        q => q.Where(e => e.Skills!.Length == 3));
await EmployeeAsync("array any",                q => q.Where(e => e.Skills!.Any()));
await EmployeeAsync("array not any",            q => q.Where(e => !e.Skills!.Any()));
await EmployeeAsync("array int contains 5",     q => q.Where(e => e.SkillLevels!.Contains(5)));

// ---------------- Employee: dictionaries -------------------------
await EmployeeAsync("dict ContainsKey work",    q => q.Where(e => e.PhoneDirectory!.ContainsKey("work")));
await EmployeeAsync("dict indexer eq",          q => q.Where(e => e.PhoneDirectory!["work"] == "+7-000-0000000"));
await EmployeeAsync("dict nested .City",        q => q.Where(e => e.OfficeLocations!["Moscow"].City == "Moscow"));
await EmployeeAsync("dict nested .Street!=null",q => q.Where(e => e.OfficeLocations!["Moscow"].Street != null));

// ---------------- Person: ListItem accessors ---------------------
await PersonAsync("Person eq Name",             q => q.Where(p => p.Name == "Alice"));
await PersonAsync("Person Age range",           q => q.Where(p => p.Age >= 18 && p.Age <= 99));
await PersonAsync("Person Email contains @",    q => q.Where(p => p.Email.Contains("@")));
await PersonAsync("Person Status.Value eq",     q => q.Where(p => p.Status!.Value == "Active"));
await PersonAsync("Person Roles[].Value any",   q => q.Where(p => p.Roles!.Any(r => r.Value == "admin")));

// ---------------- City -------------------------------------------
await CityAsync("City bool true",               q => q.Where(c => c.IsCapital == true));
await CityAsync("City bool false",              q => q.Where(c => c.IsCapital == false));
await CityAsync("City coords length eq 2",      q => q.Where(c => c.Coordinates.Length == 2));
await CityAsync("City population gt",           q => q.Where(c => c.Population > 1000));
await CityAsync("City region startsWith M",     q => q.Where(c => c.Region.StartsWith("M")));
await CityAsync("City name in",                 q => q.Where(c => new[] { "Moscow", "Paris" }.Contains(c.Name)));

// ---------------- Department -------------------------------------
await DepartmentAsync("Dept eq bool",           q => q.Where(d => d.IsActive == true));
await DepartmentAsync("Dept name startsWith",   q => q.Where(d => d.Name.StartsWith("IT")));
await DepartmentAsync("Dept code contains '-'", q => q.Where(d => d.Code.Contains("-")));
await DepartmentAsync("Dept budget >= 0",       q => q.Where(d => d.Budget >= 0m));
await DepartmentAsync("Dept description != null", q => q.Where(d => d.Description != null));

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

sw.Stop();
SqlDebugSmokeProPvtIRedbQueryable.ToSqlStringAsync
MS Pro 148ms(65)
mssql.pro.cache 12ms(65)
mssql.pro.repeat 10ms(65)
PG Pro 132ms(65)
postgres.pro.cache 13ms(65)
postgres.pro.repeat 14ms(65)
sqlite.pro 56ms(65)
sqlite.pro.cache 9ms(65)
sqlite.pro.repeat 11ms(65)

NullCheck + base Id: ambiguous _id in PVT CTE

Reproduces the ambiguous _id defect in the PVT CTE.
See docs/BUG_PVT_NULLCHECK_AMBIGUOUS_ID.md.
A props filter == null switches the PVT CTE into the two-table form
(FROM _objects o_src LEFT JOIN _values v), while the base-field filter
is compiled without a table alias. _id is the only column present in
BOTH _objects and _values, so the combination produces
column reference "_id" is ambiguous.
Four cases: two controls that must stay green, the repro, and a control that
pins the diagnosis to _id specifically (_id_parent exists only
in _objects, so it must stay green even in the two-table form).
Scope: verified against redb.Postgres.Pro. MSSql.Pro and SQLite.Pro generate
the same shape but are out of scope for now.
var sw = Stopwatch.StartNew();

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

async Task CaseAsync(string label, string expectation, IRedbQueryable<EmployeeProps> query)
{
    log.AppendLine("-----------------------------------------------------------------");
    log.AppendLine($"-- {label}");
    log.AppendLine($"-- expected: {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 ({ex.GetType().Name})");
        Console.WriteLine($"[BUILD-FAIL] {label}: {ex.Message}");
        return;
    }

    log.AppendLine(sql);
    log.AppendLine();

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

    // Static verdict: the two-table CTE form together with an unqualified _id.
    var twoTableForm = sql.Contains("FROM _objects o_src");
    var hasBareId = BareId.IsMatch(sql);
    var verdict = twoTableForm && hasBareId ? "AMBIGUOUS" : "clean";
    if (verdict == "AMBIGUOUS") suspects++;

    // Dynamic verdict: does the database actually accept it.
    string execVerdict;
    try
    {
        var rows = await query.ToListAsync();
        execVerdict = $"executed, {rows.Count} rows";
    }
    catch (Exception ex)
    {
        execVerdict = $"{ex.GetType().Name}: {ex.Message.Split('\n')[0]}";
    }

    log.AppendLine($"-- static: two-table form={twoTableForm}, bare _id={hasBareId} => {verdict}");
    log.AppendLine($"-- runtime: {execVerdict}");
    log.AppendLine();

    summary.Add($"{label}: {verdict} | {execVerdict}");
    Console.WriteLine($"[{verdict,9}] {label} -> {execVerdict}");
}

// 1. Control. Props == null alone: two-table form, but no base filter, so nothing to qualify.
await CaseAsync(
    "control: props == null only",
    "clean",
    redb.Query<EmployeeProps>()
        .Where(e => e.EmployeeCode == null)
        .Take(10));

// 2. Control. Base Id alone: single-table subquery over _objects, name resolves in its own scope.
await CaseAsync(
    "control: base Id only",
    "clean",
    redb.Query<EmployeeProps>()
        .WhereRedb(o => o.Id > 0)
        .Where(e => e.Age > 0)
        .Take(10));

// 3. Repro. Both at once.
await CaseAsync(
    "repro: props == null + base Id",
    "AMBIGUOUS",
    redb.Query<EmployeeProps>()
        .Where(e => e.EmployeeCode == null)
        .WhereRedb(o => o.Id > 0)
        .Take(10));

// 4. Control. Same shape, but the base column exists only in _objects.
//    Proves the defect is specific to _id and not to base-field pushdown in general.
await CaseAsync(
    "control: props == null + base ParentId",
    "clean",
    redb.Query<EmployeeProps>()
        .Where(e => e.EmployeeCode == null)
        .WhereRedb(o => o.ParentId != null)
        .Take(10));

string? outPath = null;
try
{
    var dir = Path.Combine(AppContext.BaseDirectory, "Output");
    Directory.CreateDirectory(dir);
    outPath = Path.Combine(dir, "nullcheck_baseid_ambiguity.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 printed and then
// executed, so the count 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 executed"
};
output.AddRange(summary);
output.AddRange(new[]
{
    $"Suspect cases: {suspects} (expected 1: the repro)",
    outPath != null ? $"Output: {outPath}" : "Output file: <not written>",
    "Report: docs/BUG_PVT_NULLCHECK_AMBIGUOUS_ID.md"
});
SqlDebugBugProPvtNullCheckIRedbQueryable.ToSqlStringAsyncIRedbQueryable.WhereRedb
MS Pro 243ms(4)
mssql.pro.cache 198ms(4)
mssql.pro.repeat 295ms(4)
PG Pro 464ms(4)
postgres.pro.cache 447ms(4)
postgres.pro.repeat 447ms(4)
sqlite.pro 195ms(4)
sqlite.pro.cache 175ms(4)
sqlite.pro.repeat 209ms(4)