The Shadow Record: A Virtual Root That Doesn't Exist in the Database — "Company = 0" Above Departments

Version note: The C# code in this post was tested with .NET 10 and Entity Framework Core (EF Core) 10, and the SQL (Structured Query Language) examples with SQL Server 2025.

Guess First: Clicking the "Sample Company" Node

The HR (human resources) Portal's left menu has a department tree. At the very top is the company name, "Sample Company", and below it Engineering, Finance and Human Resources. The user clicks the top node, and the screen sends GET /api/employees?departmentId=0 to list every employee in the company. The "New employee" form on the same screen also fills in the selected node's id as the default department. Inside the endpoint is the plainest query we know:

int departmentId = 0; // "Sample Company" was selected in the Angular tree

int count = await db.Employees.Where(e => e.DepartmentId == departmentId).CountAsync();
Console.WriteLine($"Employees in department 0: {count}");

db.Employees.Add(new Employee { DepartmentId = departmentId, FullName = "Edsger Dijkstra", Email = "edsger@example.com" });
try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateException ex)
{
    Console.WriteLine(ex.InnerException?.Message);
}

Two questions: what does the count return, and what happens if the user presses "Save" without changing the form? And a third one, the one that really matters: does the "Sample Company" node in this tree have a row in the database?

Context and Problem: A Tree Without a Root

The HR Portal's Departments table is a classic adjacency list: every department has a ParentId, and for top-level departments that column is NULL. This is what it looks like in the database:

Id ParentId Name
-- -------- ----
1 NULL Finance
2 NULL Human Resources
3 NULL Engineering
4 1 Accounting
5 1 Payroll
6 2 Recruiting
7 3 Backend
8 3 Frontend

This is not a tree but a forest: there are three separate roots and none of them is "the company". Company information lives in the Companies table, but it is nowhere to be found as a node of the department tree. Yet three different parts of the application want a single root. The tree component in Angular works much more simply with one root, and the breadcrumb should start with "Sample Company / Engineering / Backend". Reports want an "entire company" filter: the department picker should have "Sample Company" at the top. Authorization wants a "company-wide" scope: the HR director sees every department, a department manager only their own.

This post starts from an idea commonly used in the field: instead of adding the root record to the database, produce it in the API (Application Programming Interface). Departments start at 1 in the database; the API puts a virtual "Company" node with id 0 at the top of the tree, takes its name from the Companies table and makes the top-level departments its children. We call this record that does not exist in the database a shadow record. The literature calls it a virtual root or synthetic root; as a pattern it is close to Martin Fowler's Special Case: instead of handling "there is no root" with null checks everywhere, return a special object that has the expected shape.

The idea is strong because it does not touch the database, needs no migration and gives the UI (user interface) exactly the shape it wants. Its weak spot comes from the same place: 0 looks like an id but is not one. Inside the API, code that knows what 0 means lives next to code that does not, and when the code that does not know uses 0 as a real department id, the system breaks.

Symptoms

if (departmentId == 0) lines multiply in the code, and each one does something slightly different: one skips the filter, one returns 400, one silently turns it into 1.

Some reports come back empty when "entire company" is selected, others are correct. The difference is whether the person who wrote the report knew about 0.

The logs contain FK_Employees_Departments_DepartmentId violations, and the value is 0 in all of them.

DepartmentId = 0 shows up in audit records, cache keys or exported Excel files, and you cannot join those rows to any table.

A team building an integration copied the tree returned by the API into its own database and took department 0 for a real department.

The Naive Solution and Why It Breaks

The naive solution is to produce 0 in the tree and send it back to every endpoint as an ordinary int. The real output of the opening code:

Employees in department 0: 0
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Employees_Departments_DepartmentId". The conflict occurred in database "HrPortal", table "dbo.Departments", column 'Id'.

The read side does not fail, it returns an empty list. That is worse than an error: the screen says "There are no employees in this company" and nobody gets an alert. From SQL Server's point of view the query is flawless; there is no row with DepartmentId = 0. On the write side the foreign key kicks in, and at least no dirty data is created. But note: that protection exists only because there is an FK (foreign key). Had the same 0 been written to a column without an FK, say a report definition's DefaultDepartmentId or an audit table, nothing would break and 0 would quietly mix into the data.

The root cause is this: a virtual root is a presentation decision, but in the naive solution it travels as a data value. The code that builds the tree knows what 0 means; the code that writes the query, saves the form or builds the cache key does not. Fowler's Special Case solves this with an object: the behavior of the special case lives inside that object. 0 is a number without behavior; its meaning has to be remembered again everywhere it is used.

Options

A. A virtual root produced only in the API

The idea itself: the root is produced in memory on every request and has no counterpart in the database.

// The tree root is produced in memory on every request; there is no row for it
return new DepartmentNode(DepartmentScope.CompanyRootId, companyName, "company", topLevel);

The database and existing queries do not change at all; the UI gets its single-rooted tree. The cost is that the meaning of 0 has to be preserved throughout the API. On its own, this option is exactly the naive solution; it gets strong when combined with D below.

B. A real root row in the database

Make the company a real node of the tree: the root is the only row with ParentId = NULL, and everybody else has a parent. You can have the database enforce the "single root" rule with a filtered unique index:

CREATE TABLE dbo.OrgUnits
(
    Id        int IDENTITY(1, 1) PRIMARY KEY,
    CompanyId int           NOT NULL,
    ParentId  int           NULL REFERENCES dbo.OrgUnits (Id),
    Name      nvarchar(200) NOT NULL
);

/* One root per company: at most one row with a NULL ParentId */
CREATE UNIQUE INDEX UX_OrgUnits_SingleRoot
    ON dbo.OrgUnits (CompanyId)
    WHERE ParentId IS NULL;

INSERT dbo.OrgUnits (CompanyId, ParentId, Name) VALUES (1, NULL, N'Sample Company');
INSERT dbo.OrgUnits (CompanyId, ParentId, Name) VALUES (1, 1, N'Finance');
INSERT dbo.OrgUnits (CompanyId, ParentId, Name) VALUES (1, NULL, N'Engineering');

Msg 2601, Level 14, State 1, Server b8be943222fa, Line 16
Cannot insert duplicate key row in object 'dbo.OrgUnits' with unique index 'UX_OrgUnits_SingleRoot'. The duplicate key value is (1).

The root now has a real id, and every FK, every join and every report knows about it. The "entire company" filter becomes a "subtree of the root" query, which is solved with the recursive CTE (Common Table Expression) or closure table from the hierarchy post. The cost is that the company also behaves like a department: headcount reports count the root as a department, somebody can assign employees to the root department, and the root's id is not 0 but whatever identity gives it, 1. An existing database needs a migration that updates the ParentId of every top-level department.

C. A separate Company entity as the root

The root is the real row in the Companies table; the tree carries nodes of two different types. Because ids can collide, the node id has to travel together with its type:

{ "id": "company:1", "name": "Sample Company", "kind": "company", "children": [
    { "id": "department:3", "name": "Engineering", "kind": "department", "children": [] } ] }

The company's real data, such as its name, tax number and address, is already in Company; nothing is virtual. It scales naturally when multi-tenancy arrives or a holding wants to see several companies in one tree. The cost is a change in the API contract: the tree component in Angular and every client must work with a string node key instead of a numeric id.

D. An explicit scope instead of 0 in the contract

Do not use 0 at all in filtering endpoints, and name the scope:

GET /api/employees?scope=company
GET /api/employees?scope=department&departmentId=7

The API documents itself; scope=company cannot be misunderstood. On the C# side this scope is carried by a DepartmentScope type, and the query layer never sees a bare int. The cost is that existing clients are sending departmentId=0; to avoid breaking them, both must be accepted during a transition period.

Implementation: Let 0 Live Only at the Boundary

The path we chose combines A and D: the virtual root stays in the tree, because the UI's need is real and can be met without touching the database. But 0 is turned into a DepartmentScope at the edge of the API, while the request is being bound, and never travels as a number inside.

DepartmentScope is a value object with two states: the whole company or a single real department.

// The "which department(s)?" question at the API boundary: either the whole company or one real department
public sealed record DepartmentScope
{
    public const int CompanyRootId = 0;

    private DepartmentScope(int? departmentId) => DepartmentId = departmentId;

    // null = the whole company; 0 never reaches the database
    public int? DepartmentId { get; }
    public bool IsCompany => DepartmentId is null;

    public static DepartmentScope Company { get; } = new((int?)null);

    public static DepartmentScope Of(int departmentId) => departmentId > 0
        ? new DepartmentScope(departmentId)
        : throw new ArgumentOutOfRangeException(nameof(departmentId), "Real departments start at 1.");

    public int ToApiId() => DepartmentId ?? CompanyRootId;

    // Minimal APIs find this method and bind the query/route value through it
    public static bool TryParse(string? value, IFormatProvider? provider,
        [NotNullWhen(true)] out DepartmentScope? scope)
    {
        scope = null;
        if (string.Equals(value, "company", StringComparison.OrdinalIgnoreCase))
            scope = Company;
        else if (int.TryParse(value, out int id) && id >= 0)
            scope = id == CompanyRootId ? Company : Of(id);
        return scope is not null;
    }

    public override string ToString() => IsCompany ? "company" : $"department:{DepartmentId}";
}

public static class DepartmentScopeQueries
{
    public static IQueryable<Employee> InScope(this IQueryable<Employee> employees, DepartmentScope scope)
        => scope.IsCompany ? employees : employees.Where(e => e.DepartmentId == scope.DepartmentId);
}

There are three deliberate decisions here. First, "the whole company" is null inside, not 0: under no circumstances can DepartmentId reach the database as 0. Second, TryParse accepts both the old 0 and the explicit company; the existing Angular client does not break, and new clients use the readable value. If a minimal API finds a static TryParse with this signature on the parameter type, it binds the query string through it. Third, the type is a sealed record, not a record struct. As a struct, default(DepartmentScope) would be a valid value and, with a null DepartmentId, it would represent the whole company. A forgotten assignment would fall back to the widest permission; a default that errs in the wrong direction from a security standpoint.

ToString() is not arbitrary either: it produces company and department:7 for cache keys and audit records. That way no employees:0 key appears in the cache, and no meaningless 0 appears in the audit table.

The code that builds the tree runs in a single pass and produces the root from the company name, not from the database:

public sealed record DepartmentRow(int Id, int? ParentId, string Name);

public sealed record DepartmentNode(int Id, string Name, string Kind, IReadOnlyList<DepartmentNode> Children);

public static class DepartmentTreeBuilder
{
    public static DepartmentNode Build(string companyName, IReadOnlyList<DepartmentRow> rows)
    {
        ILookup<int?, DepartmentRow> byParent = rows.ToLookup(r => r.ParentId);
        int visited = 0;

        DepartmentNode Map(DepartmentRow row)
        {
            visited++;
            List<DepartmentNode> children = byParent[row.Id].OrderBy(c => c.Name).Select(Map).ToList();
            return new DepartmentNode(row.Id, row.Name, "department", children);
        }

        // The root is not in the database: departments with a null ParentId become its children
        List<DepartmentNode> topLevel = byParent[null].OrderBy(r => r.Name).Select(Map).ToList();

        // Rows in a cycle or with a missing parent are unreachable from the root; don't lose them silently
        if (visited != rows.Count)
            throw new InvalidOperationException(
                $"Department tree is broken: {rows.Count - visited} of {rows.Count} rows are unreachable from the root.");

        return new DepartmentNode(DepartmentScope.CompanyRootId, companyName, "company", topLevel);
    }
}

The check line matters. If the adjacency list has a cycle (A's parent is B, B's parent is A) or rows still pointing at a deleted parent, those rows cannot be reached from any root with ParentId = NULL. With a virtual root they silently drop out of the tree, and a user reports that a department "disappeared". Comparing the counts turns this into an exception. Preventing cycles at data entry is the topic of the hierarchy post.

The endpoints:

app.MapGet("/api/departments/tree", async (HrPortalContext db) =>
{
    string company = await db.Companies.Select(c => c.Name).SingleAsync();
    List<DepartmentRow> rows = await db.Departments
        .Select(d => new DepartmentRow(d.Id, d.ParentId, d.Name)).ToListAsync();
    return DepartmentTreeBuilder.Build(company, rows);
});

app.MapGet("/api/employees", async (DepartmentScope departmentId, HrPortalContext db) =>
    await db.Employees.InScope(departmentId)
        .OrderBy(e => e.FullName)
        .Select(e => new { e.Id, e.FullName, e.DepartmentId })
        .ToListAsync());

app.MapPost("/api/employees", async (CreateEmployee body, HrPortalContext db) =>
{
    // There is no virtual root on the write side: an employee belongs to a real department
    if (body.DepartmentId == DepartmentScope.CompanyRootId)
        return Results.ValidationProblem(new Dictionary<string, string[]>
        {
            ["departmentId"] = ["0 is the virtual company root. Choose a real department."]
        });

    if (!await db.Departments.AnyAsync(d => d.Id == body.DepartmentId))
        return Results.ValidationProblem(new Dictionary<string, string[]>
        {
            ["departmentId"] = [$"Department {body.DepartmentId} does not exist."]
        });

    var employee = new Employee { DepartmentId = body.DepartmentId, FullName = body.FullName, Email = body.Email };
    db.Employees.Add(employee);
    await db.SaveChangesAsync();
    return Results.Created($"/api/employees/{employee.Id}", new { employee.Id, employee.DepartmentId });
});

The output of the tree endpoint (abbreviated):

{
    "id": 0,
    "name": "Sample Company",
    "kind": "company",
    "children": [
        {
            "id": 3,
            "name": "Engineering",
            "kind": "department",
            "children": [
                { "id": 7, "name": "Backend", "kind": "department", "children": [] },
                { "id": 8, "name": "Frontend", "kind": "department", "children": [] }
            ]
        },
        {
            "id": 1,
            "name": "Finance",
            ...

On the Angular side, the tree component's data source is now a single-element array: [root]. The breadcrumb always starts with "Sample Company", and since the root node's kind is company, the "New employee" button can be disabled on that node. Even if the UI repeats this decision, the real authority is the API, as you will see below.

The results of the requests we sent to the list endpoint with curl:

GET  /api/employees?departmentId=0        200  8 employees
GET  /api/employees?departmentId=company  200  8 employees
GET  /api/employees?departmentId=7        200  Ada Lovelace, Alan Turing
GET  /api/employees?departmentId=-1       400
GET  /api/employees?departmentId=abc      400
GET  /api/employees                       400

For -1 and abc, TryParse returns false and the minimal API produces 400; a request without the parameter is also 400, because the scope is required. There is no implicit default like "I sent nothing, so I see everything". The SQL that EF Core generates shows that the two scopes really are different queries:

-- departmentId=0
SELECT [e].[Id], [e].[FullName], [e].[DepartmentId]
FROM [Employees] AS [e]
ORDER BY [e].[FullName]

-- departmentId=3
SELECT [e].[Id], [e].[FullName], [e].[DepartmentId]
FROM [Employees] AS [e]
WHERE [e].[DepartmentId] = @scope_DepartmentId
ORDER BY [e].[FullName]

The company scope has no WHERE at all; no tricks like WHERE DepartmentId = 0 or WHERE @p = 0 OR ... are needed. The latter matters in particular: "catch-all" queries in the style of WHERE (@departmentId = 0 OR DepartmentId = @departmentId) share a single cached plan in SQL Server and can produce very bad plans depending on the parameter. Building two separate queries per scope removes that problem from the start.

There is no virtual root on the write side. An employee always belongs to a real department, and 0 is rejected with a clear message:

{"type":"https://tools.ietf.org/html/rfc9110#section-15.5.1","title":"One or more validation errors occurred.","status":400,"errors":{"departmentId":["0 is the virtual company root. Choose a real department."]},"traceId":"00-0b19ec20fcc07aac471d872fb915901d-931e0a9d8014dcb6-00"}

The same request with a real department returns 201:

{"id":9,"departmentId":7}

Authorization hooks into the same type: if the scope IsCompany, the user must have company-wide permission; otherwise the requested department must be one the user manages. This check happens in one place, right after DepartmentScope is bound; resource-based authorization from the ASP.NET Core Web API series post "Authorization in ASP.NET Core Web API: Roles, Policies and Resource-Based Authorization" is made for exactly this.

The OpenAPI document also needs to say, in the description of the departmentId parameter, "0 or company: the whole company; read endpoints only". An undocumented 0 ends with the integration team creating a row called "Department 0" in its own database. Check the generated document by eye as well: there are issues in the dotnet/aspnetcore repository reporting that custom types bound via TryParse show up as object rather than string in the OpenAPI schema.

Trade-off Table

When Not to Use It

Do not use a virtual root if the root has real data. If the company needs to be an FK target, with a budget, a manager, a cost center or an approver, then "company" is no longer a presentation detail but an entity; move to C or B.

Do not use 0 as the root id if there is a screen where one user sees several companies in the same tree. In a holding view each company has its own root, and "which company is 0?" has no answer. In multi-tenancy, screens that look at a single company can resolve 0 from the tenant context, but which tenant 0 belongs to must never be taken from the request.

Do not publish 0 if the API has a public, long-lived contract. A decision made for an internal UI lives on for years in integration partners' databases. Use scope=company or a typed node key in the external contract.

If there is no real department tree and it is only two levels deep, you may not need a virtual root at all; "All departments" can sit as a plain item in a dropdown.

Checklist

• Is 0 (or whatever id the virtual root has) turned into a type while the request is bound, so that it never travels as a number inside?
• Are there no paths left that write 0 to the database, a cache key, an audit record or a log?
• Do write endpoints reject the virtual root with a clear validation message?
• Is the "entire company" scope a separate query, or a @p = 0 OR ... catch-all query?
• Is there a separate authorization check for the company-wide scope, and is the scope's default value not "the widest"?
• Are rows unreachable from the root detected when the tree is built?
• Does the OpenAPI document explain what 0 and company mean?
• Is a second company or a holding view on the roadmap? If so, is the cost of moving to C known?

Related Patterns and Sources

The code-level relatives of this pattern are Special Case and Null Object. The definition of Special Case in PoEAA (Patterns of Enterprise Application Architecture, Martin Fowler, 2002) and the EAA (Enterprise Application Architecture) catalog on martinfowler.com are the primary sources; the code itself is in the planned Design Patterns series post "Code-Level Patterns Beyond GoF" (GoF: the Gang of Four, i.e. Gamma, Helm, Johnson and Vlissides' 1994 book Design Patterns). For binding a custom type with a static TryParse in minimal APIs, the "Parameter binding in Minimal API apps" page on Microsoft Learn, and for the plan problem of catch-all queries, SQL Server's parameter sensitivity and query plan cache documentation are good starting points.

Within the series, this post deliberately sits next to the following one: here 0 is "a virtual record living in the API", while in the third post sentinel values are "magic values stored in the data". The hierarchy itself (subtree queries, preventing cycles, hierarchyid) is in the tenth post, and the root-per-tenant question in the thirteenth. From the published series, the ASP.NET Core Web API posts "Swagger and OpenAPI in ASP.NET Core Web API: Documentation with Swashbuckle" and "Global Error Handling in ASP.NET Core: Exception Middleware and ProblemDetails", and the OWASP (Open Worldwide Application Security Project) series post on IDOR (Insecure Direct Object Reference), "OWASP A01: Broken Access Control (1) — IDOR, Admin Endpoints and Authorization in ASP.NET Core", complement this one.

A Small Experiment

★☆☆ Turn DepartmentScope into a readonly record struct and print default(DepartmentScope).IsCompany. How could this default turn into an authorization hole in an endpoint?

★★☆ Write a subtree filter for DepartmentScope.Of(3) that covers not only Engineering but also Backend and Frontend: first by walking the department list in memory, then with a single recursive CTE. Compare the SQL each approach produces.

★★★ Design the move to Option C: node keys in the form company:1 and department:7, accepting the old 0 and numeric ids for one release, and moving the Angular client to the new keys. Which step would you take in which release?

In this post, 0 could at least be caught at the API boundary and turned into a type. The 0s, -1s and 9999-12-31s in the next post are not that lucky: they live directly in the database, inside the columns, and they get mixed into everything from AVG to index statistics.