Dataverse Column Behavior Laboratory: Data Types, Validation, Formula Columns, and the C# SDK

Understanding how Microsoft Dataverse interprets, validates, stores, and calculates column values across application and API boundaries.

Power Platform Engineering Academy — Five Years in Five Days

Technical domain: Microsoft Dataverse
Application: APP-002 — Column Behavior Laboratory
Technologies: Dataverse, C#, Dataverse SDK, Power Fx, Model-Driven Apps
Status: Engineering design — pending practical validation

Introduction

A column in Microsoft Dataverse is not simply a database field.

It is a metadata-defined component whose behavior depends on its data type, configuration, calculation model, validation rules, and execution context.

Two columns that appear nearly identical in a Model-Driven App can behave differently when accessed through C#, the Dataverse Web API, Power Automate, or Power Fx.

A column configured as Business Required may prevent a user from saving a Model-Driven App form while still permitting a programmatic operation that omits its value.

A Date and Time column may display different values depending on the user’s time zone and the column’s configured behavior.

A formula column may calculate its value when retrieved rather than behaving like an ordinary stored value.

These differences are not minor implementation details. They influence data integrity, integration contracts, application architecture, performance, and debugging.

APP-002 — Column Behavior Laboratory investigates these behaviors through controlled experiments using a dedicated Dataverse table, an independent C# application, and a functional Model-Driven App.

Our central engineering question is:

Where does Dataverse enforce a column’s behavior, and how does that behavior change depending on the client, API, and execution context?

1. The Engineering Model: A Column Has Multiple Contracts

A Dataverse column participates in several overlapping contracts.

Metadata contract: Defines the column’s type, logical name, required level, supported operations, precision, limits, and other characteristics.

Storage contract: Determines how values are represented and persisted.

Application contract: Determines how forms and controls present, validate, and manipulate values.

API contract: Determines which values the SDK and Web API accept and return.

Calculation contract: Determines how computed values are evaluated and exposed.

A developer must distinguish these contracts rather than assuming that the Model-Driven App interface represents the complete behavior of the platform.

For example, setting a column to Business Required does not make it equivalent to a SQL NOT NULL constraint. Microsoft documents that programmatic saves enforce System Required columns, while Business Required behavior is primarily enforced by applications. Microsoft Learn — Create and edit columns.

This is our first important experimental hypothesis: the same record operation may be accepted or rejected depending on which layer performs the validation.

2. Application Architecture

We will extend the academy’s shared Dataverse environment while keeping APP-002 independently executable.

The architecture consists of:

APP-002 Column Behavior Laboratory
│
├── C# Console Application
│   ├── ServiceClient
│   ├── Metadata Inspection
│   ├── Record Creation
│   ├── Record Updates
│   ├── Validation Tests
│   └── Result Comparison
│
├── Microsoft Dataverse
│   ├── pea_columnexperiment
│   └── pea_columnvalidation
│
└── Model-Driven App
    ├── Entry Dashboard
    ├── Column Experiments
    ├── Validation Results
    ├── Forms
    └── Diagnostic Views

The C# application performs controlled operations through the SDK.

Dataverse persists test records and their results.

The Model-Driven App provides the operational interface for inspecting input values, executing manual tests, and comparing results.

The architecture deliberately separates programmatic validation from form validation so that their differences can be observed.

3. Designing the Dataverse Tables

The experiment introduces two custom tables in the PEA.Dataverse solution.

3.1 Column Experiment

Display name: Column Experiment
Logical name: pea_columnexperiment

For reproducibility, the following configuration is proposed:

The numeric values assigned to Choice options must be captured from the actual published Dataverse metadata. The C# client should not assume that option labels are the values stored by the platform.

3.2 Column Validation

Display name: Column Validation
Logical name: pea_columnvalidation

A successful test is not necessarily a successful Dataverse write.

For example, if the test expects Dataverse to reject a value outside a configured range, receiving that rejection means the test passed.

Therefore, pea_success represents whether the observed behavior matches the test expectation, not whether the underlying API operation succeeded.

4. Experiment One: Required Column Behavior

This experiment investigates a frequently misunderstood aspect of Dataverse.

The pea_requiredtext column is configured as Business Required.

We will compare two operations.

Operation A: Create a record through the Model-Driven App without entering the required value.

Operation B: Create a record through the Dataverse SDK without supplying the same column.

Expected behavior:

The Model-Driven App should enforce the Business Required configuration when the user attempts to save the form.

The SDK operation may succeed because Business Required is not equivalent to a server-enforced System Required constraint.

The experiment should also test the difference between omitting the attribute and explicitly supplying null.

These are separate input conditions and should be recorded independently.

Architectural implication

Business Required is valuable for user experience and form-level data quality, but it should not be treated as a universal integrity guarantee across every integration path.

If a business rule requires a value to be present regardless of how the record is created, additional server-side validation may be necessary.

Possible mechanisms include appropriately scoped server-side business rules or synchronous plug-ins.

The choice depends on the complexity of the invariant, the required execution scope, and the desired error behavior.

Reference: Create and edit columns in Dataverse.

5. Experiment Two: Numeric Range and Precision

Numeric columns introduce a different category of behavior.

Dataverse supports configured minimum and maximum values for numeric columns, along with precision settings for relevant numeric types.

For the laboratory:

pea_wholenumber
Minimum: 0
Maximum: 100
pea_decimalnumber
Minimum: 0
Maximum: 1000
Precision: 2

The C# application will attempt the following operations:

The precision experiment requires special attention.

A developer should not assume that Dataverse will reject every decimal value with more fractional digits than the configured precision.

The actual behavior must be established through an API write followed by a fresh retrieval.

This distinguishes submitted values from persisted values.

Why this matters

Numeric precision affects financial calculations, integrations, comparisons, and rounding behavior.

If an external API supplies four decimal places but Dataverse stores only two, downstream calculations may not preserve the original input.

The engineering decision is therefore not simply which column type to choose. It is where precision should be controlled and which component owns the authoritative value.

Reference: Column data types in Microsoft Dataverse.

6. Experiment Three: Date and Time Behavior

Date and Time columns are among the most important sources of integration defects.

Dataverse distinguishes three relevant behaviors:

The behavior is part of the column’s metadata.

A format such as Date Only is not necessarily the same thing as configuring Date Only behavior.

This distinction is essential when designing integrations.

Proposed experiment

Create a record with a known UTC timestamp:

DateTime eventDate =
    new DateTime(
        2026, 10, 29,
        15, 30, 0,
        DateTimeKind.Utc);

Persist it in a User Local Date and Time column.

Then inspect the same record through:

1. The C# SDK.
2. A Model-Driven App user configured for one time zone.
3. A second user configured for a different time zone.

Record the actual values displayed and retrieved.

For example, a timestamp of 15:30 UTC corresponds to 12:30 in São Paulo when the applicable offset is UTC−03:00.

The experiment must verify the actual user settings and returned SDK values rather than assuming that every client applies identical formatting or conversion.

Architectural implication

Use User Local when the value represents an instant that users should see in their own time zone.

Use Date Only when the business concept is fundamentally a calendar date, such as a birthday.

Use Time Zone Independent when the business semantics require a wall-clock date/time without user-specific conversion.

Reference: Behavior and format of the Date and Time column.

7. Experiment Four: Choice Columns

Choice columns illustrate the difference between human-readable labels and stored values.

A Choice column presents labels to users, but the Dataverse SDK works with numeric option values represented by OptionSetValue.

For example:

record["pea_category"] =
    new OptionSetValue(100000000);

The value above is illustrative. It must not be used without verifying the actual configured option values.

A metadata-aware client should retrieve the available options and their numeric values before performing the test.

The experiment will compare:

• A valid option value.
• An invalid numeric option value.
• An omitted Choice value.
• A null Choice value.
• A label change without changing the option value.

This reveals an important integration principle: labels are presentation metadata, not stable integration identifiers.

For an external system, mapping by option label is more fragile than using a controlled, documented mapping of option values.

8. Experiment Five: Formula Columns and Power Fx

Formula columns introduce a different execution model.

Unlike a conventional stored input column, a formula column derives its value from an expression.

For this experiment, we propose:

pea_quantity * pea_unitprice

The exact formula syntax must be confirmed in the formula editor using the actual published column identifiers.

The resulting column, pea_totalamount, represents a calculated decimal value.

Suppose the inputs are:

Quantity: 5
Unit Price: 12.50

The expected result is:

Total Amount: 62.50

The C# application should retrieve the record after creation and inspect the returned formula value.

It should not attempt to write directly to the formula column as though it were an ordinary editable decimal field.

Formula columns are not plug-ins

Microsoft documents that formula columns are calculated when retrieved and that formula columns cannot themselves trigger workflows or plug-ins.

This means they should not be treated as a general-purpose server-side event mechanism.

Formula columns are appropriate for supported declarative calculations. Synchronous plug-ins are more appropriate when the requirement involves transactional validation, side effects, or complex server-side business logic.

Important platform constraints

Microsoft documents several limitations for formula columns, including:

• Maximum expression length of 1,000 characters.
• Maximum formula dependency depth of 10.
• Restrictions on supported types and functions.
• Limitations involving related-table references and sorting.
• No formula values in mobile offline mode.
• No direct triggering of workflows or plug-ins from formula columns.

These constraints matter when selecting between formula columns, calculated columns, plug-ins, and other computation mechanisms.

Reference: Work with Dataverse formula columns.

9. Complete Independent C# Test Application

We will create a separate console project:

PowerPlatformEngineeringAcademy/
└── Dataverse/
    └── APP002.ColumnBehaviorLaboratory/
        ├── APP002.ColumnBehaviorLaboratory.csproj
        └── Program.cs

Install the Dataverse client package:

dotnet new console --name APP002.ColumnBehaviorLaboratory
cd APP002.ColumnBehaviorLaboratory
dotnet add package Microsoft.PowerPlatform.Dataverse.Client

The following Program.cs provides an independent test harness.

It creates records, evaluates the result of each operation, reads persisted values, and attempts to store validation results.

It assumes that the two custom tables, columns, and formula definition have been created and published.

Program.cs

using System;
using System.Diagnostics;
using System.Globalization;
using System.Linq;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Metadata;
using Microsoft.Xrm.Sdk.Query;
const string ExperimentTable = "pea_columnexperiment";
const string ValidationTable = "pea_columnvalidation";
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(
        $"Connection failed: {client.LastError}");
}
var whoAmI = (WhoAmIResponse)client.Execute(
    new WhoAmIRequest());
Console.WriteLine($"Connected user: {whoAmI.UserId}");
int testNumber = 0;
int passed = 0;
int failed = 0;
void RunTest(
    string testName,
    string targetColumn,
    string inputValue,
    bool expectWriteSuccess,
    Action operation)
{
    testNumber++;
    var stopwatch = Stopwatch.StartNew();
    bool writeSucceeded = false;
    string actualResult;
    try
    {
        operation();
        writeSucceeded = true;
        actualResult = "Operation completed successfully.";
    }
    catch (Exception ex)
    {
        actualResult =
            $"{ex.GetType().Name}: {ex.Message}";
    }
    stopwatch.Stop();
    bool testPassed =
        writeSucceeded == expectWriteSuccess;
    if (testPassed)
        passed++;
    else
        failed++;
    Console.WriteLine();
    Console.WriteLine($"Test: {testName}");
    Console.WriteLine($"Expected write success: {expectWriteSuccess}");
    Console.WriteLine($"Actual write success: {writeSucceeded}");
    Console.WriteLine($"Test passed: {testPassed}");
    Console.WriteLine($"Result: {actualResult}");
    try
    {
        var validation = new Entity(ValidationTable);
        validation["pea_name"] =
            $"APP002-{testNumber:D3}";
        validation["pea_testcase"] = testName;
        validation["pea_targetcolumn"] = targetColumn;
        validation["pea_inputvalue"] = inputValue;
        validation["pea_expectedresult"] =
            expectWriteSuccess
                ? "Write accepted"
                : "Write rejected";
        validation["pea_actualresult"] = actualResult;
        validation["pea_success"] = testPassed;
        validation["pea_durationms"] = checked(
            (int)stopwatch.ElapsedMilliseconds);
        validation["pea_executedon"] = DateTime.UtcNow;
        client.Create(validation);
    }
    catch (Exception logError)
    {
        Console.Error.WriteLine(
            $"Validation logging failed: {logError.Message}");
    }
}
Entity NewExperiment(string name)
{
    var record = new Entity(ExperimentTable);
    record["pea_name"] = name;
    return record;
}
void CreateAndInspect(
    Entity record,
    params string[] columns)
{
    Guid id = client.Create(record);
    Console.WriteLine($"Created record: {id}");
    if (columns.Length == 0)
        return;
    var retrieved = client.Retrieve(
        ExperimentTable,
        id,
        new ColumnSet(columns));
    foreach (string column in columns)
    {
        object? value = retrieved.Contains(column)
            ? retrieved[column]
            : null;
        string output = value switch
        {
            null => "<null or not returned>",
            OptionSetValue option => option.Value.ToString(),
            DateTime date => date.ToString("O"),
            decimal number => number.ToString(
                CultureInfo.InvariantCulture),
            _ => value.ToString() ?? ""
        };
        Console.WriteLine($"{column}: {output}");
    }
}
// Test 1: Valid whole number
RunTest(
    "Valid whole number",
    "pea_wholenumber",
    "50",
    true,
    () =>
    {
        var record = NewExperiment("Valid Integer");
        record["pea_wholenumber"] = 50;
        CreateAndInspect(record, "pea_wholenumber");
    });
// Test 2: Value above configured maximum
RunTest(
    "Whole number above maximum",
    "pea_wholenumber",
    "101",
    false,
    () =>
    {
        var record = NewExperiment("Invalid Integer");
        record["pea_wholenumber"] = 101;
        CreateAndInspect(record);
    });
// Test 3: Valid decimal
RunTest(
    "Valid decimal",
    "pea_decimalnumber",
    "12.34",
    true,
    () =>
    {
        var record = NewExperiment("Valid Decimal");
        record["pea_decimalnumber"] = 12.34m;
        CreateAndInspect(record, "pea_decimalnumber");
    });
// Test 4: Decimal above configured maximum
RunTest(
    "Decimal above maximum",
    "pea_decimalnumber",
    "1000.01",
    false,
    () =>
    {
        var record = NewExperiment("Invalid Decimal");
        record["pea_decimalnumber"] = 1000.01m;
        CreateAndInspect(record);
    });
// Test 5: Business Required omitted
RunTest(
    "Business Required omitted",
    "pea_requiredtext",
    "<omitted>",
    true,
    () =>
    {
        var record = NewExperiment("Missing Business Required");
        CreateAndInspect(record);
    });
// Test 6: User Local Date and Time
RunTest(
    "Date and Time round trip",
    "pea_eventdate",
    "2026-10-29T15:30:00Z",
    true,
    () =>
    {
        var record = NewExperiment("DateTime Test");
        record["pea_eventdate"] =
            new DateTime(
                2026, 10, 29,
                15, 30, 0,
                DateTimeKind.Utc);
        CreateAndInspect(record, "pea_eventdate");
    });
// Test 7: Formula column
RunTest(
    "Formula evaluation",
    "pea_totalamount",
    "Quantity=5; UnitPrice=12.50",
    true,
    () =>
    {
        var record = NewExperiment("Formula Test");
        record["pea_quantity"] = 5;
        record["pea_unitprice"] = 12.50m;
        CreateAndInspect(
            record,
            "pea_quantity",
            "pea_unitprice",
            "pea_totalamount");
    });
// Inspect Choice metadata without assuming option values.
var metadataRequest = new RetrieveAttributeRequest
{
    EntityLogicalName = ExperimentTable,
    LogicalName = "pea_category",
    RetrieveAsIfPublished = false
};
var metadataResponse = (RetrieveAttributeResponse)
    client.Execute(metadataRequest);
if (metadataResponse.AttributeMetadata
    is PicklistAttributeMetadata choiceMetadata)
{
    Console.WriteLine("\nCHOICE OPTIONS");
    foreach (var option in
        choiceMetadata.OptionSet?.Options
        ?? Enumerable.Empty<OptionMetadata>())
    {
        Console.WriteLine(
            $"Value: {option.Value}, " +
            $"Label: {option.Label?.UserLocalizedLabel?.Label}");
    }
}
Console.WriteLine();
Console.WriteLine($"Tests executed: {testNumber}");
Console.WriteLine($"Passed: {passed}");
Console.WriteLine($"Failed: {failed}");
Environment.ExitCode = failed == 0 ? 0 : 1;

Important implementation qualifications

This test harness is intentionally simple and self-contained.

It demonstrates how to submit values and record whether a write was accepted or rejected.

However, it does not yet implement complete assertions for retrieved values.

For example, the formula test prints the retrieved result but does not compare it numerically against 62.50.

Similarly, the date test prints the returned timestamp but does not compare it with the original UTC instant.

Those comparisons must be added during implementation before the tests can be considered comprehensive.

The current harness also assumes that an operation rejected with any exception satisfies a negative test. A stronger implementation must validate the specific Dataverse error code and distinguish expected validation errors from unrelated failures such as authentication, network outages, or missing table permissions.

These limitations are explicit engineering work items, not verified outcomes.

10. Designing the Model-Driven App

The application will be named:

PEA Column Behavior Laboratory

It will provide a dedicated entry dashboard and operational pages.

Entry Dashboard

The dashboard should display:

The dashboard will also contain a view of recent validation results and a view of experiment records.

These metrics must be calculated from persisted data rather than manually entered.

Column Experiment form

The form should contain sections for:

Text validation: Required and optional text fields.

Numeric validation: Whole numbers, decimals, minimums, maximums, and precision.

Date and Time: Date input and time-zone behavior.

Choice and Boolean: Choice selection and Yes/No values.

Formula evaluation: Quantity, Unit Price, and calculated Total Amount.

This layout makes it possible to compare manual form behavior with programmatic SDK behavior.

Validation Results view

The view should expose:

• Test case.
• Target column.
• Submitted value.
• Expected result.
• Actual result.
• Pass/fail status.
• Duration.
• Execution timestamp.

Filtering by target column allows engineers to isolate particular behavior categories.

11. Testing Across Execution Boundaries

The experiment is not complete until we compare behavior across different clients.

The purpose is not to prove that one client is better.

It is to discover which behaviors belong to the application layer, which belong to Dataverse itself, and which are consequences of client-side representation.

12. Formula Columns Versus C# Plug-ins

One of the most important architectural decisions is determining where calculations belong.

A formula column is not a lightweight substitute for every server-side computation.

Likewise, a plug-in should not be introduced merely to multiply two numeric values when a supported declarative formula is sufficient.

The architectural principle is to select the simplest mechanism that satisfies the actual execution and consistency requirements.

13. Performance Considerations

Column configuration influences more than presentation.

Computed columns may introduce calculation work during retrieval.

Complex formulas can affect query capabilities and sorting behavior.

Choice labels introduce localization considerations.

Date and Time columns introduce conversion semantics.

Numeric precision can influence downstream calculation accuracy.

The practical experiment should measure write and retrieval operations separately.

For formula columns, compare the cost of retrieving the source columns with retrieving the calculated value under controlled conditions.

Do not infer a general platform performance rule from a single execution.

Any meaningful benchmark requires repeated measurements, comparable environments, and a documented test methodology.

14. Security and Data Integrity

A user interface is not a security boundary.

Client-side validation improves usability but cannot guarantee that every integration follows the same rules.

Server-side security and business invariants must be designed independently of the form.

For example, if a record must never have a negative quantity, that requirement should be enforced through an appropriate platform mechanism rather than relying solely on a JavaScript form handler.

Likewise, hiding a column on a Model-Driven App form does not constitute field-level authorization.

Dataverse column security and table privileges must be evaluated separately when sensitive information is involved.

This distinction becomes central to later experiments involving security roles, plug-ins, and API integrations.

15. Engineering Challenges

The implementation phase should investigate several questions that go beyond the initial successful execution.

Challenge A — Required fields: Can an SDK client create a record while omitting a Business Required column?

Challenge B — Precision: What value is actually persisted when a decimal input contains more digits than the configured precision?

Challenge C — Date and Time: Do two users in different time zones see different values for the same User Local timestamp?

Challenge D — Choice metadata: Can the C# client discover valid option values dynamically and reject an invalid selection?

Challenge E — Formula consistency: Does updating Quantity change the subsequently retrieved formula result without explicitly updating the formula column?

Challenge F — Validation ownership: Which failures originate from the client interface, and which are returned by Dataverse?

The answers must be based on execution evidence.

16. Microsoft Learn Technical References

The following official Microsoft documentation establishes the foundation for this laboratory.

1. Column data types in Microsoft Dataverse — Column types, configuration, and supported behavior.
2. Create and edit columns in Dataverse — Required levels, numeric properties, and column configuration.
3. Behavior and format of the Date and Time column — User Local, Date Only, and Time Zone Independent behavior.
4. Work with Dataverse formula columns — Power Fx formulas, supported functions, and limitations.
5. Specialized columns using code — SDK metadata properties and calculation behavior.
6. Automate calculations by defining calculated columns — Calculated column behavior and limitations.
7. Create a business rule in Microsoft Dataverse — Validation and business-rule execution.

These references describe documented platform capabilities. The actual outcomes of the laboratory remain to be verified experimentally.

Conclusion

Dataverse column behavior cannot be understood solely by looking at the data type displayed in Power Apps.

Every column participates in a broader contract involving metadata, application validation, API serialization, storage behavior, and server-side execution.

The Column Behavior Laboratory is designed to expose these distinctions through controlled C# operations and a functional Model-Driven App.

Its most important lesson is architectural: data integrity depends on understanding where a rule is enforced, not merely where it is configured.

A developer who understands these execution boundaries can make better decisions about Dataverse schema design, API integration, Power Fx formulas, business rules, and C# plug-ins.

This experiment also establishes a reusable foundation for the academy’s later investigations into relationships, transactions, concurrency, security, and distributed integration.

The article is ready for editorial review. Its implementation and experimental conclusions remain pending until the application is executed and inspected in a real Dataverse environment.

Power Platform Engineering Academy — Five Years in Five Days

Article 002/100 — Column Behavior Laboratory

Maximum technical depth. Minimum unnecessary complexity. Evidence before claims.