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

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
PG Pro 487ms(4)
postgres.pro.repeat 466ms(4)
postgres.pro.cache 500ms(4)
MS Pro 336ms(4)
mssql.pro.repeat 639ms(4)
mssql.pro.cache 186ms(4)
sqlite.pro 234ms(4)
sqlite.pro.repeat 221ms(4)
sqlite.pro.cache 225ms(4)