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.
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).");
}
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();
Window function with Where filter applied before windowing. Filter employees by salary threshold, then apply ROW_NUMBER. Tests Pro filter handling in Window queries.
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.
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();
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();
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.
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.
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"
});