Dataverse Relationship Engineering Lab: Referential Integrity, Cascading Behavior, and C# SDK

Engineering table relationships in Microsoft Dataverse through metadata, lookup columns, referential behavior, cascading operations, and programmatic validation.

Power Platform Engineering Academy — Five Years in Five Days

Technical domain: Microsoft Dataverse
Application: APP-003 — Relationship Engineering Lab
Technologies: Dataverse, C#, Dataverse SDK, Web API, Model-Driven Apps
Status: Engineering design — pending implementation and experimental validation

Introduction

Relationships are fundamental to the architecture of Microsoft Dataverse. They define how records are connected, how applications navigate between tables, and how certain operations propagate across related records.

However, a Dataverse relationship is not merely a foreign key.

A relationship has metadata, navigation properties, referencing and referenced roles, configurable cascading behaviors, and security implications.

This makes relationship engineering substantially more complex than creating a lookup column in the Power Apps maker portal.

For experienced developers working with SQL Server, C#, REST APIs, and distributed systems, the most important question is not how to create a relationship. It is how Dataverse interprets and enforces that relationship across different operations.

APP-003 — Relationship Engineering Lab explores these mechanisms through an independent C# application and a functional Model-Driven App.

The experiment will investigate:

• One-to-many and many-to-one relationships.
• Many-to-many associations.
• Lookup columns and EntityReference.
• Relationship metadata.
• Referential and parental behavior.
• Cascading Assign, Delete, Reparent, Share, and Unshare operations.
• Association and disassociation through the SDK.
• Security and transaction boundaries.
• The consequences of relationship design for enterprise applications.

Our central engineering question is:

How does Dataverse maintain relationships between records, and what actually happens when related records are assigned, deleted, associated, or disconnected?

1. Relationships Are Metadata-Defined Contracts

In Dataverse, relationships are defined through metadata that determines how records are connected and how certain platform operations behave.

Three relationship forms are particularly important.

A many-to-one relationship is not a separate relationship metadata class. It represents the referencing side of a one-to-many relationship.

For a conventional 1 relationship, the lookup column resides on the referencing table.

Consider:

Project
   |
   | 1:N
   |
   +---- Task A
   +---- Task B
   +---- Task C

The Task table contains a lookup referencing Project.

In SDK terminology, Project is the referenced table, while Task is the referencing table.

This distinction becomes critical when inspecting relationship metadata and configuring cascading behavior.

2. Application Architecture

APP-003 introduces a relationship-focused laboratory while reusing the academy’s shared solution conventions.

APP-003 Relationship Engineering Lab
│
├── C# Console Application
│   ├── Retrieve Relationship Metadata
│   ├── Create Parent and Child Records
│   ├── Associate N:N Records
│   ├── Disassociate Records
│   ├── Update Lookup References
│   ├── Test Delete Behavior
│   └── Inspect Results
│
├── Dataverse
│   ├── pea_project
│   ├── pea_projecttask
│   ├── pea_projecttag
│   └── pea_relationshiptest
│
└── Model-Driven App
    ├── Entry Dashboard
    ├── Projects
    ├── Project Tasks
    ├── Project Tags
    ├── Relationship Tests
    └── Diagnostic Views

The C# project is the execution engine for relationship experiments.

The Model-Driven App is the operational interface used to inspect relationships, navigate records, and validate the results.

The design deliberately separates relationship configuration from relationship execution.

3. Dataverse Data Model

We will use four custom tables.

3.1 Project

Logical name: pea_project

3.2 Project Task

Logical name: pea_projecttask

The pea_projectid lookup establishes the 1 relationship between Project and Project Task.

3.3 Project Tag

Logical name: pea_projecttag

Project Task and Project Tag will have an N relationship.

One task may have multiple tags, and one tag may be associated with multiple tasks.

3.4 Relationship Test

Logical name: pea_relationshiptest

This table captures the outcomes of relationship experiments.

The expected result must be recorded separately from the actual result so that failed assumptions remain visible.

4. One-to-Many Relationships and EntityReference

The SDK represents lookup values using EntityReference.

For example:

var task = new Entity("pea_projecttask");
task["pea_name"] = "Review API Contract";
task["pea_projectid"] = new EntityReference(
    "pea_project",
    projectId);
Guid taskId = service.Create(task);

The lookup column receives a reference to the related Project record.

EntityReference contains the logical name of the referenced table and the record identifier.

This is fundamentally different from assigning the GUID as an ordinary text value.

Dataverse uses the lookup metadata to interpret the reference and enforce the relationship.

4.1 Changing a relationship

A task can be reassigned to another project by updating its lookup.

var task = new Entity(
    "pea_projecttask",
    taskId);
task["pea_projectid"] = new EntityReference(
    "pea_project",
    anotherProjectId);
service.Update(task);

This changes the referenced parent record.

It does not create a new relationship definition. It changes the value of an existing lookup.

4.2 Clearing a lookup

For a nullable lookup, the relationship can be cleared:

var task = new Entity(
    "pea_projecttask",
    taskId);
task["pea_projectid"] = null;
service.Update(task);

Whether this is permitted depends on the column configuration and any additional server-side validation.

A Model-Driven App form may impose requirements that differ from the behavior of an SDK update.

This connects directly to the validation-boundary lessons from APP-002.

5. Referential, Parental, and Configurable Cascading Behavior

Dataverse relationships support configurable cascading behavior for certain operations.

These settings determine what happens to related records when an operation is performed on a parent record.

Relevant operations include:

The exact supported configuration depends on the relationship and table capabilities.

5.1 Referential behavior

A referential relationship does not automatically behave like a parental relationship.

For example, a relationship can be configured so that deleting a parent removes the lookup reference from its children, when supported.

Alternatively, deletion can be restricted while related records exist.

5.2 Parental behavior

A parental relationship introduces stronger cascading semantics for supported operations.

Deleting a parent can cause related child records to be deleted.

This behavior has significant consequences for data integrity, security, and auditability.

5.3 Configurable cascading

Dataverse supports configurable cascading rules.

For example, a relationship may use cascading deletion while restricting or disabling cascading behavior for other operations.

The application must inspect the actual relationship metadata rather than infer behavior from the lookup column alone.

Reference: Table relationship behavior.

6. Engineering Experiment: Delete Behavior

The laboratory will create a parent project and two child tasks.

Project Alpha
   |
   +---- Task A
   +---- Task B

We will then attempt to delete Project Alpha under different supported relationship configurations.

These are proposed scenarios. The exact configuration options and results must be verified for the relationship being tested.

Critical architectural consideration

Cascading deletion should never be treated as a harmless convenience.

Deleting a parent can affect multiple related records and may trigger additional processing.

Before enabling cascading deletion in enterprise applications, developers should understand the business meaning of the relationship and the operational consequences of removing the parent.

The appropriate rule is determined by the business invariant, not by a preference for fewer lines of code.

7. Many-to-Many Relationships

A many-to-many relationship allows multiple records on each side to be associated with multiple records on the other.

For example:

Task A ---- Security
       \--- Integration
Task B ---- Security
       \--- Performance

Dataverse maintains the association through a relationship intersect structure.

For a native N relationship, the intersect table is managed by Dataverse and is not equivalent to a fully customizable business table.

If the association itself needs business attributes such as status, effective date, approval state, or ownership, an explicit junction table is often a better design.

7.1 Associate records

The SDK provides Associate.

service.Associate(
    "pea_projecttask",
    taskId,
    new Relationship("pea_projecttask_projecttag"),
    new EntityReferenceCollection
    {
        new EntityReference(
            "pea_projecttag",
            tagId)
    });

The relationship schema name must match the actual published relationship definition.

7.2 Disassociate records

The corresponding operation is Disassociate.

service.Disassociate(
    "pea_projecttask",
    taskId,
    new Relationship("pea_projecttask_projecttag"),
    new EntityReferenceCollection
    {
        new EntityReference(
            "pea_projecttag",
            tagId)
    });

This removes the association.

It does not delete either participating business record.

This distinction is central to relationship engineering.

8. Relationship Metadata Inspection

The SDK exposes relationship definitions through metadata requests.

For a 1 relationship, we can inspect:

var request = new RetrieveRelationshipRequest
{
    Name = "pea_project_projecttask"
};
var response = (RetrieveRelationshipResponse)
    service.Execute(request);

The returned relationship metadata can be examined to determine:

• Referenced table.
• Referencing table.
• Referencing attribute.
• Relationship schema name.
• Cascading configuration.

A robust metadata-aware application should discover and inspect these definitions before executing operations that depend on them.

This is especially important when deploying across environments where schema customizations may differ.

Reference: Query schema definitions.

9. Independent C# Relationship Laboratory

The proposed project is:

PowerPlatformEngineeringAcademy/
└── Dataverse/
    └── APP003.RelationshipEngineeringLab/
        ├── APP003.RelationshipEngineeringLab.csproj
        └── Program.cs

Install the Dataverse client package:

dotnet new console --name APP003.RelationshipEngineeringLab
cd APP003.RelationshipEngineeringLab
dotnet add package Microsoft.PowerPlatform.Dataverse.Client

The following program demonstrates relationship metadata retrieval, parent-child creation, N association, lookup reassignment, and disassociation.

It assumes that the custom tables and relationships have already been created and published.

Complete Program.cs

using System;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Xrm.Sdk.Query;
const string ProjectTable = "pea_project";
const string TaskTable = "pea_projecttask";
const string TagTable = "pea_projecttag";
const string ProjectLookup = "pea_projectid";
const string OneToManyRelationship =
    "pea_project_projecttask";
const string ManyToManyRelationship =
    "pea_projecttask_projecttag";
string connectionString =
    Environment.GetEnvironmentVariable(
        "DATAVERSE_CONNECTION")
    ?? throw new InvalidOperationException(
        "DATAVERSE_CONNECTION is missing.");
using var client = new ServiceClient(connectionString);
if (!client.IsReady)
{
    throw new InvalidOperationException(
        $"Dataverse connection failed: {client.LastError}");
}
var whoAmI = (WhoAmIResponse)client.Execute(
    new WhoAmIRequest());
Console.WriteLine($"Connected user: {whoAmI.UserId}");
Console.WriteLine("\nRELATIONSHIP METADATA");
var relationshipRequest = new RetrieveRelationshipRequest
{
    Name = OneToManyRelationship
};
var relationshipResponse =
    (RetrieveRelationshipResponse)
    client.Execute(relationshipRequest);
if (relationshipResponse.RelationshipMetadata
    is OneToManyRelationshipMetadata relationshipMetadata)
{
    Console.WriteLine(
        $"Schema: {relationshipMetadata.SchemaName}");
    Console.WriteLine(
        $"Referenced table: {relationshipMetadata.ReferencedEntity}");
    Console.WriteLine(
        $"Referencing table: {relationshipMetadata.ReferencingEntity}");
    Console.WriteLine(
        $"Lookup: {relationshipMetadata.ReferencingAttribute}");
    Console.WriteLine(
        $"Delete behavior: " +
        $"{relationshipMetadata.CascadeConfiguration?.Delete}");
}
Console.WriteLine("\nCREATING PROJECTS");
var projectA = new Entity(ProjectTable);
projectA["pea_name"] = "Project Alpha";
Guid projectAId = client.Create(projectA);
var projectB = new Entity(ProjectTable);
projectB["pea_name"] = "Project Beta";
Guid projectBId = client.Create(projectB);
Console.WriteLine($"Project Alpha: {projectAId}");
Console.WriteLine($"Project Beta: {projectBId}");
Console.WriteLine("\nCREATING TASK");
var task = new Entity(TaskTable);
task["pea_name"] = "Review API Contract";
task[ProjectLookup] = new EntityReference(
    ProjectTable,
    projectAId);
Guid taskId = client.Create(task);
Console.WriteLine($"Task: {taskId}");
Console.WriteLine("\nCREATING TAG");
var tag = new Entity(TagTable);
tag["pea_name"] = "Integration";
Guid tagId = client.Create(tag);
Console.WriteLine($"Tag: {tagId}");
Console.WriteLine("\nASSOCIATING TASK AND TAG");
client.Associate(
    TaskTable,
    taskId,
    new Relationship(ManyToManyRelationship),
    new EntityReferenceCollection
    {
        new EntityReference(TagTable, tagId)
    });
Console.WriteLine("Association requested successfully.");
Console.WriteLine("\nREPARENTING TASK");
var update = new Entity(TaskTable, taskId);
update[ProjectLookup] = new EntityReference(
    ProjectTable,
    projectBId);
client.Update(update);
var updatedTask = client.Retrieve(
    TaskTable,
    taskId,
    new ColumnSet(ProjectLookup));
var currentProject =
    updatedTask.GetAttributeValue<EntityReference>(
        ProjectLookup);
Console.WriteLine(
    $"Current parent: {currentProject?.Id}");
Console.WriteLine("\nDISASSOCIATING TASK AND TAG");
client.Disassociate(
    TaskTable,
    taskId,
    new Relationship(ManyToManyRelationship),
    new EntityReferenceCollection
    {
        new EntityReference(TagTable, tagId)
    });
Console.WriteLine("Disassociation requested successfully.");
Console.WriteLine("\nEXPERIMENT COMPLETED");

Implementation limitations

The program demonstrates the basic relationship operations, but it is not yet a complete automated test suite.

It does not verify the N association by querying the related records after Associate.

It does not test cascading deletion.

It does not record results in pea_relationshiptest.

It also does not clean up the records it creates.

These behaviors must be implemented and validated during the practical experiment before APP-003 can be considered complete.

10. Designing the Model-Driven App

The application will be named:

PEA Relationship Engineering Lab

Its navigation will include:

10.1 Entry dashboard

The dashboard should display:

Projects: Number of project records.

Tasks: Number of task records.

Tags: Number of tag records.

Relationship tests: Number of recorded experiment results.

Failed tests: Number of tests where observed behavior differs from the expected result.

A recent-tests view should expose the latest relationship operations.

10.2 Project form

The Project main form should contain a subgrid displaying related Project Tasks.

This allows the user to navigate the 1 relationship directly.

10.3 Project Task form

The Project Task main form should expose:

• Project lookup.
• Task description.
• Status.
• Related Project Tags subgrid.

This provides a direct interface for testing both 1 and N relationships.

10.4 Relationship Test form

The test form should expose the operation, relationship schema name, expected result, actual result, and pass/fail status.

This is the operational evidence surface for the experiment.

11. Security Implications

Relationship configuration interacts with Dataverse security.

For example, cascading Assign operations can affect ownership of related records.

Cascading Share and Unshare operations can affect record access.

These operations must be understood in the context of Dataverse security roles, ownership, and access rights.

The presence of a lookup does not automatically grant a user access to the referenced record.

Likewise, the ability to read a child record does not necessarily imply permission to read its parent.

The experiment should use multiple security identities to investigate these boundaries.

For APP-003, the initial focus is on relationship behavior. A more comprehensive examination of Dataverse security is reserved for APP-010.

12. Performance and Operational Risks

Relationships affect query performance, record navigation, cascading operations, and data lifecycle management.

A parent deletion that cascades to many related records can be operationally more expensive than deleting a single isolated record.

A Model-Driven App form containing several related-record subgrids may generate additional data retrieval activity.

Many-to-many associations introduce additional relationship traversal operations.

These costs depend on data volume, query design, security evaluation, and platform execution behavior.

The practical experiment should measure operations separately and avoid assuming that a successful operation on a small dataset predicts behavior at enterprise scale.

13. Architectural Decision: Native N or Explicit Junction Table?

This is one of the most important modeling decisions in Dataverse.

A native N relationship is appropriate when the association itself carries no meaningful business attributes.

For example:

Task <----> Tag

If the association must contain additional information, such as:

Task
  |
  v
Task Assignment
  |
  +---- Assigned User
  +---- Assignment Date
  +---- Approval Status
  +---- Effective Date

then an explicit junction table is generally preferable.

This makes the association a first-class business record with its own columns, security, lifecycle, and automation capabilities.

The choice is architectural, not merely a matter of interface convenience.

14. Engineering Validation Matrix

The expected results must be compared with observed results.

For N operations, successful API responses are not sufficient evidence. The association should be independently queried.

For deletion tests, the records must be retrieved after the operation to establish whether they were deleted, retained, or unlinked.

15. Microsoft Learn Technical References

The following official references provide the technical foundation for APP-003.

1. Table relationships in Dataverse — Relationship types, metadata, and programmatic operations.
2. Configure table relationship cascading behavior — Referential, parental, and configurable cascading behavior.
3. Query schema definitions — Relationship metadata retrieval.
4. Associate and disassociate table rows using the SDK — SDK association operations.
5. Create one-to-many and many-to-one table relationships — Relationship configuration in Power Apps.
6. Create many-to-many table relationships — Native N relationship configuration.
7. Dataverse security concepts — Security roles, ownership, and record access.

Conclusion

Dataverse relationships are more than references between records.

They define structural contracts that influence navigation, record lifecycle, security, and platform execution behavior.

Understanding these contracts requires investigating both metadata and runtime operations.

APP-003 establishes a laboratory for doing exactly that.

Through the Dataverse SDK, we can create parent-child relationships, inspect relationship metadata, associate records, reparent children, and evaluate cascading behavior.

Through the Model-Driven App, we can inspect the resulting records and verify how the platform represents those relationships to users.

The central architectural lesson is straightforward:

A relationship is not merely a connection between two tables. It is a set of behavioral rules that can affect the lifecycle and accessibility of related records.

The implementation phase will validate these rules through controlled execution, failure injection, and inspection of persisted results.

Power Platform Engineering Academy — Five Years in Five Days

Article 003/100 — Relationship Engineering Lab

Understand the metadata. Execute the operation. Inspect the result. Challenge the assumptions.