The Realm at Any Date
Who was allied with whom, as a single query
Who reported to whom on the day a decision was taken? Which suppliers were under contract when the incident happened? Every system that stores relationships is eventually asked what they looked like on a past date, and most answer it late, with a history table bolted on. If every relationship already carries a start and an end date, the answer is one filter on the data you store anyway.
Part 1 put every party in Westeros — houses and people alike — in one table, and every oath, marriage and alliance in one relationship table with a StartDate and an EndDate. Its era map drew the whole period at once. This part moves the date: the realm at any moment becomes a query.
Universal or Regira? Dating every relationship and filtering on a half-open interval is plain modeling and plain LINQ. The typed search object and its registration are Regira Entities; the trees are Regira.TreeList; the figures come from the sample's own export and the companion how-to's renderer.
Pivotal moments: a SearchObject as a time machine
The filter lives where every filter in Regira Entities lives — on a typed search object, translated by a query builder; From Query Parameters to a Composable Filter Pipeline is the introduction. Give the relationship its own vocabulary:
public record PartyRelationshipSearchObject : SearchObject
{
public ICollection<int>? PartyId { get; set; } // either side of the edge
public ICollection<int>? RelationshipTypeId { get; set; }
public DateTime? ActiveAt { get; set; } // ← the time machine
}
services.For<PartyRelationship, int, PartyRelationshipSearchObject>(e =>
{
e.Includes((q, _) => q.Include(x => x.Parent).Include(x => x.Child)
.Include(x => x.RelationshipType));
e.Filter((query, so) =>
{
if (so?.PartyId?.Any() == true)
query = query.Where(x => so.PartyId.Contains(x.ParentId)
|| so.PartyId.Contains(x.ChildId));
if (so?.RelationshipTypeId?.Any() == true)
query = query.Where(x => so.RelationshipTypeId.Contains(x.RelationshipTypeId));
if (so?.ActiveAt is { } at)
query = query.Where(x => (x.StartDate == null || x.StartDate <= at)
&& (x.EndDate == null || x.EndDate > at));
return query;
});
});
// in an API; the sample is a console app and calls the entity service directly
[ApiController, Route("party-relationships")]
public class PartyRelationshipController : EntityControllerBase<PartyRelationship, int,
PartyRelationshipSearchObject, PartyRelationshipDto, PartyRelationshipInputDto>;
Two conventions carry the correctness:
- The interval is half-open —
StartDate <= at < EndDate— so the day a relation ends it is already gone, and back-to-back relations (Bolton leaves Stark, Bolton joins the crown) never overlap at the boundary. nullmeans open-ended on that side, so ancient oaths and current ones need no sentinel dates.
The framework ships a ready-made FilterIsActiveOn for IHasStartEndDate entities, but it keeps a relation active on its end date (EndDate >= at); the half-open version is written out here for that reason. And because ActiveAt is just another search object field, it composes with everything else for free: ?activeAt=0299-06-15&relationshipTypeId=5 (SWORN_TO in the sample) is "who was sworn to whom mid-war", and paging, counts and DTO mapping behave as on any other endpoint.
The active relations feed two views. Folded with ToTreeList, they are the realm as a hierarchy you can diff and assert in a test; drawn, they are the figure under each tree. Part 1's era map is a third thing, a period, with its dashed, dagger-marked history.
var realm = parties.Where(p => ExistsAt(p, at)) // houses standing, people alive
.ToTreeList(p => ParentsOf(p, at)); // only ties active at `at`
foreach (var node in realm.OrderByHierarchy())
Console.WriteLine($"{new string(' ', node.Level * 3)}{node.Value.Title}");
ParentsOf(p, at) returns the other end of every oath, membership, wardship and hold on the crown active at at, so every house carries its people, in the order of their membership rows' SortOrder. Membership means allegiance, not blood — the family name already records that — which is why Catelyn counts as a Stark while Cersei stays a Lannister. A party with two ties hangs under both: Jaime is a Lannister sworn to the Iron Throne as Kingsguard, and Theon, a Greyjoy raised at Winterfell as Eddard's ward, appears under each house.
The sample prints the same tree through a small printer of its own (Print in Program.cs), which adds the branch lines and a note on the tie that holds each party in place. Run it for two pivotal dates and history draws itself: same data, one date apart. First the realm Robert built, what an API would answer to GET /party-relationships?activeAt=0297-06-15: every great house sworn to the Iron Throne — boring, as peace tends to look in graph form:
The Iron Throne
├─ Jaime Lannister — sworn 281
├─ House Arryn — sworn 283
│ ├─ Jon Arryn
│ └─ Lysa Arryn — née Tully
├─ House Baratheon — holds the crown
│ ├─ Robert Baratheon — king 283
│ ├─ Stannis Baratheon
│ ├─ Renly Baratheon
│ ├─ Joffrey Baratheon
│ └─ Tommen Baratheon
├─ House Greyjoy — sworn 289
│ ├─ Balon Greyjoy
│ └─ Theon Greyjoy
├─ House Hightower — sworn 283
├─ House Lannister — sworn 283
│ ├─ Tywin Lannister
│ ├─ Cersei Lannister
│ ├─ Jaime Lannister
│ └─ Tyrion Lannister
├─ House Martell — sworn 283
│ ├─ Doran Martell
│ └─ Oberyn Martell
├─ House Stark — sworn 283
│ ├─ Eddard Stark
│ ├─ Catelyn Stark — née Tully
│ ├─ Robb Stark
│ ├─ Sansa Stark
│ ├─ Arya Stark
│ ├─ Bran Stark
│ ├─ Jon Snow
│ ├─ Theon Greyjoy — ward 289
│ ├─ House Bolton — sworn 283
│ │ ├─ Roose Bolton
│ │ └─ Ramsay Bolton
│ ├─ House Karstark — sworn 283
│ │ └─ Rickard Karstark
│ └─ House Mormont — sworn 283
├─ House Tully — sworn 283
│ ├─ Hoster Tully
│ ├─ Edmure Tully
│ └─ House Frey — sworn 283
│ └─ Walder Frey
├─ House Tyrell — sworn 283
│ ├─ Olenna Tyrell
│ ├─ Margaery Tyrell
│ └─ Loras Tyrell
└─ House Velaryon — sworn 283
House Targaryen — lost the crown †283
├─ Viserys Targaryen
└─ Daenerys Targaryen
Drogo's Khalasar — across the sea
└─ Drogo — the khal
Night's Watch — sworn to no crown
└─ Jeor Mormont — Lord Commander
Jorah Mormont — membership of House Mormont ended †293
Three years later, the same query with activeAt=0300-06-15 returns a different realm. The northern, riverland and Iron Islands oaths have vanished from the result set because their EndDate passed, and the TreeList makes the damage structural: houses whose oaths ended are no longer children of anything, so they surface as roots, each annotated with the edge that used to hold it in place. People move with their own ties: Jon Snow's membership passed to the Night's Watch in 298; Jorah, a root in 297 because his exile ended his membership, now hangs under Daenerys, to whom he swore in 298; and Tyrion, who killed his father in 300, is a root of his own:
The Iron Throne
├─ Jaime Lannister — sworn 281
├─ House Arryn — sworn 283
├─ House Baratheon — holds the crown
│ ├─ Stannis Baratheon
│ └─ Tommen Baratheon — king 300
├─ House Bolton — sworn 299
│ ├─ Roose Bolton
│ └─ Ramsay Bolton
├─ House Hightower — sworn 283
├─ House Lannister — sworn 283
│ ├─ Cersei Lannister
│ └─ Jaime Lannister
├─ House Martell — sworn 283
│ └─ Doran Martell
├─ House Tyrell — sworn 283
│ ├─ Olenna Tyrell
│ ├─ Margaery Tyrell
│ └─ Loras Tyrell
└─ House Velaryon — sworn 283
House Stark — oath to The Iron Throne ended †299
├─ Sansa Stark
├─ Arya Stark
├─ Bran Stark
└─ House Mormont — sworn 283
House Targaryen — lost the crown †283
└─ Daenerys Targaryen
└─ Jorah Mormont — sworn 298
Night's Watch — sworn to no crown
├─ Jon Snow — joined 298
└─ Samwell Tarly — joined 298
House Frey — oath to House Tully ended †299
└─ Walder Frey
House Greyjoy — oath to The Iron Throne ended †299
└─ Theon Greyjoy
House Tully — oath to The Iron Throne ended †299
└─ Edmure Tully
Drogo's Khalasar — across the sea
House Karstark — oath to House Stark ended †299
Tyrion Lannister — membership of House Lannister ended †300
The dead are handled by the same predicate. Party implements IHasStartEndDate too, a person's dates being birth and death, so "alive on this date" is the one-line filter above applied to people instead of edges. The tree leaves the dead out, Eddard, Robb and Joffrey among them; the figure grays them.
Three centuries, zero migrations
How far back does the time machine go? As far as you have rows. Westeros helpfully ships a prequel, so load the Dance of the Dragons — the Targaryen succession war, 170 years before Robert — into the same tables. No new entity, no schema change: the throne as an institution and this era's political vocabulary arrive as ordinary rows. Abridged here; the full seed, each house's members included, is in the sample's CSV files:
// the crown becomes a party of its own, and the era brings its own edge vocabulary
var throne = new Organization { Name = "The Iron Throne", LegalEntity = "Institution" };
var holds = new RelationshipType { Code = "HOLDS", Title = "Holds" };
var king = new RelationshipType { Code = "KING", Title = "King" };
var heir = new RelationshipType { Code = "HEIR", Title = "Named heir" };
var claims = new RelationshipType { Code = "CLAIMS", Title = "Claims" };
var marriedTo = new RelationshipType { Code = "MARRIED_TO", Title = "Married to" };
var alliedWith = new RelationshipType { Code = "ALLIED_WITH", Title = "Allied with" };
// houses that matter in this era (Stark and Baratheon already exist — different members)
var targaryen = new Organization { Name = "House Targaryen", LegalEntity = "Great House" };
var hightower = new Organization { Name = "House Hightower" };
var velaryon = new Organization { Name = "House Velaryon" };
var viserys1 = new Person { GivenName = "Viserys", FamilyName = "Targaryen", EndDate = Y(129) };
var rhaenyra = new Person { GivenName = "Rhaenyra", FamilyName = "Targaryen", EndDate = Y(130) };
var aegon2 = new Person { GivenName = "Aegon", FamilyName = "Targaryen", EndDate = Y(131) };
var daemon = new Person { GivenName = "Daemon", FamilyName = "Targaryen", EndDate = Y(130) };
var alicent = new Person { GivenName = "Alicent", FamilyName = "Hightower" };
var corlys = new Person { GivenName = "Corlys", FamilyName = "Velaryon" };
List<PartyRelationship> history =
[
// the crown itself is a dated relation: Targaryen holds it from the Conquest to Robert's rebellion
new() { Parent = throne, Child = targaryen, RelationshipType = holds, StartDate = Y(1), EndDate = Y(283) },
new() { Parent = throne, Child = viserys1, RelationshipType = king, StartDate = Y(103), EndDate = Y(129) },
new() { Parent = throne, Child = aegon2, RelationshipType = king, StartDate = Y(129), EndDate = Y(131) },
// succession is two dated rows, not an edit: heir until the crown is taken, claimant after
new() { Parent = throne, Child = rhaenyra, RelationshipType = heir, StartDate = Y(105), EndDate = Y(129) },
new() { Parent = throne, Child = rhaenyra, RelationshipType = claims, StartDate = Y(129), EndDate = Y(130) },
// the court's marriages…
new() { Parent = viserys1, Child = alicent, RelationshipType = marriedTo, StartDate = Y(106), EndDate = Y(129) },
new() { Parent = rhaenyra, Child = daemon, RelationshipType = marriedTo, StartDate = Y(120), EndDate = Y(130) },
// …and the war: Blacks vs Greens, all starting the year the king dies
new() { Parent = rhaenyra, Child = velaryon, RelationshipType = alliedWith, StartDate = Y(129) },
new() { Parent = rhaenyra, Child = stark, RelationshipType = alliedWith, StartDate = Y(129) }, // the Pact of Ice and Fire
new() { Parent = aegon2, Child = hightower, RelationshipType = alliedWith, StartDate = Y(129) },
new() { Parent = aegon2, Child = baratheon, RelationshipType = alliedWith, StartDate = Y(129), EndDate = Y(130) },
];
The seed hides two modeling decisions:
- Succession is two rows, not an update. Rhaenyra is named heir from 105 until the crown is taken from her in 129, then claimant until 130; both facts remain queryable forever, which is exactly what an
UPDATEwould have destroyed. - Even the crown is a dated edge.
Targaryen holds the throne (1–283)sits in the same table asBaratheon holds the throne (283–), so "who ruled when" needs no special casing.
The interesting tree in 129 AC is not fealty at all. Build the TreeList over the war relations active in 129 (?activeAt=0129-06-15), with the crowned king and the claimant as the roots, alliances as the tree edges and each allied house bringing its people — and the realm's actual structure appears: two roots, because two people call themselves the rightful monarch:
Aegon II — king 129 (the Greens)
├─ House Baratheon — allied 129
│ └─ Borros Baratheon
└─ House Hightower — allied 129
├─ Otto Hightower — Hand of the King
└─ Alicent Hightower
Rhaenyra Targaryen — claimant 129 (the Blacks)
├─ House Stark — allied 129
│ └─ Cregan Stark
└─ House Velaryon — allied 129
├─ Corlys Velaryon — the Sea Snake
└─ Jacaerys Velaryon
That is the same ToTreeList call with a different relationship filter. Which relations you treat as tree edges is itself a query decision, and a civil war is precisely the moment a realm stops being one tree.
House Stark holds Cregan here and Eddard 170 years later, in the trees as in the figures: the house is one durable row, and its people come and go with their own dates.
The same query with duller nouns
Swap the names back and the same query answers reports you already owe someone:
- An audit view — the org chart on the day a decision was taken:
activeAtset to that day, memberships as the tree edges, printed as above. - "State as of contract date" — which parties stood in which relationship when the contract was signed:
activeAtplusrelationshipTypeId; the half-open interval keeps a partner that left on that day out. - The direct suppliers at the moment of an incident —
partyIdfor the customer,activeAtfor the incident, and the tree built over what comes back; a deeper network repeats the query per level.
Companies restructure, people change roles, brands get acquired and sold; a party model with dated edges keeps every era queryable with one parameter.
Why not a history table? A history table records how a row changed; a dated edge records how long a fact held, which is what every question above asks. The interval sits on the row the query already reads, so nothing is joined and nothing is replayed, and a new era — even one 170 years earlier — arrives as a backfill of ordinary rows.
Runnable: the companion project reads both eras from four CSV files and prints the three snapshots above — grab it, move the date, or end an oath in relationships.csv and run it again. The figures are drawn from its export, as the companion how-to shows.
Docs: the full Stakeholders blueprint ships with the Regira Entities documentation; Regira.TreeList is an Apache-2.0 NuGet package.
