Create a record that points at another one

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Create.Basics;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_POSTGRESQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_POSTGRESQL environment variable to your PostgreSQL connection string.");

await new Basic2_CreateWithALookup().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.PostgreSql.Services;
using ARX.SDK.Services;

using static ArxSamples.PostgreSql.SampleOutput;

namespace ArxSamples.PostgreSql.Operations.Create.Basics;

/// <summary>
/// A record that points at another one (a lookup), in four scenarios: by the referenced entity's RecordId, by the id
/// CreateAsync returned, to a record whose id is text, and what happens when the lookup is wrong.
/// </summary>
public sealed class Basic2_CreateWithALookup : ISample
{
    public string Name => "create-basics-2-create-with-a-lookup";

    public string Title => "Create a record that points at another one";

    public async Task RunAsync(SampleContext dbContext)
    {
        await Scenario1_ByTheEntitysRecordIdAsync(dbContext.ConnectionString);

        await Scenario2_ByTheIdCreateReturnedAsync(dbContext.ConnectionString);

        await Scenario3_ToARecordWithATextIdAsync(dbContext.ConnectionString);

        await Scenario4_WhenTheLookupIsWrongAsync(dbContext.ConnectionString);
    }

    private static ArxPgDbService Service(string connectionString) => new ArxPgDbService(connectionString, ArxSchemaCatalog.Create()
        .Table("account", ArxIdColumn.Generated<long>("id"))
        .Table("contact", ArxIdColumn.Generated<long>("id"))
        .Table("country", ArxIdColumn.Supplied<string>("code"))
        .Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")
        .Build());

    /// <summary>1. The usual way: point at the account by the RecordId set on it when it was created.</summary>
    private static async Task Scenario1_ByTheEntitysRecordIdAsync(string connectionString)
    {
        Step("1. By the entity's RecordId");

        var service = Service(connectionString);

        var account = new ArxEntity("account");
        account["name"] = "Adventure Works";
        await service.CreateAsync(account);

        var contact = new ArxEntity("contact");
        contact["first_name"] = "Ana";
        contact["last_name"] = "Silva";
        contact["email"] = "ana.silva@adventure-works.example";
        contact["account_id"] = new ArxEntityReference("account", account.RecordId);   // the table, then the id

        await service.CreateAsync(contact);

        Show($"created contact {contact.RecordId} for account {account.RecordId}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT c.id, c.first_name, c.last_name, c.account_id, a.name AS account
         * FROM contact c
         * JOIN account a ON a.id = c.account_id
         * WHERE c.email = 'ana.silva@adventure-works.example';
         * -- Expect: 1 row, Ana Silva, account Adventure Works.
         * ===========================================================
         */
    }

    /// <summary>2. The other way: keep the id CreateAsync returned, and point at the account with it.</summary>
    private static async Task Scenario2_ByTheIdCreateReturnedAsync(string connectionString)
    {
        Step("2. By the id Create returned");

        var service = Service(connectionString);

        var account = new ArxEntity("account");
        account["name"] = "Proseware Labs";

        ArxRecordId accountId = await service.CreateAsync(account);

        var contact = new ArxEntity("contact");
        contact["first_name"] = "Ben";
        contact["last_name"] = "Okafor";
        contact["email"] = "ben.okafor@proseware-labs.example";
        contact["account_id"] = new ArxEntityReference("account", accountId);    // an ArxRecordId works for any id type

        await service.CreateAsync(contact);

        Show($"created contact {contact.RecordId} for account {accountId}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT c.id, c.first_name, c.last_name, c.account_id, a.name AS account
         * FROM contact c
         * JOIN account a ON a.id = c.account_id
         * WHERE c.email = 'ben.okafor@proseware-labs.example';
         * -- Expect: 1 row, Ben Okafor, account Proseware Labs.
         * ===========================================================
         */
    }

    /// <summary>3. A record whose id is text: a price points at its country by the country code.</summary>
    private static async Task Scenario3_ToARecordWithATextIdAsync(string connectionString)
    {
        Step("3. To a text id");

        var service = Service(connectionString);

        var belgium = new ArxEntity("country");
        belgium["code"] = "BE";
        belgium["name"] = "Belgium";
        await service.CreateAsync(belgium);

        var price = new ArxEntity("price_list");
        price["region"] = new ArxEntityReference("country", "BE");               // a text id
        price["sku"] = "SKU-000001";
        price["unit_price"] = 2.40m;
        price["valid_from"] = new DateOnly(2026, 1, 1);

        await service.CreateAsync(price);

        Show("created a price for country BE");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT p.region, c.name AS country, p.sku, p.unit_price
         * FROM price_list p
         * JOIN country c ON c.code = p.region
         * WHERE p.region = 'BE';
         * -- Expect: 1 row, Belgium, SKU-000001, unit_price 2.40.
         * ===========================================================
         */
    }

    /// <summary>
    /// 4. When the lookup is wrong: a record that doesn't exist is refused by the database's foreign key (ARX3002); a
    /// table the schema doesn't declare is refused by ARX before anything is sent (ARX1005).
    /// </summary>
    private static async Task Scenario4_WhenTheLookupIsWrongAsync(string connectionString)
    {
        Step("4. A record that isn't there");

        var service = Service(connectionString);

        var noSuchAccount = new ArxEntity("contact");
        noSuchAccount["first_name"] = "Nobody";
        noSuchAccount["last_name"] = "Here";
        noSuchAccount["account_id"] = new ArxEntityReference("account", 999_999L);

        try
        {
            await service.CreateAsync(noSuchAccount);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.ForeignKeyViolation)
        {
            Show($"{error.Code} (the database's foreign key)");
        }

        var typo = new ArxEntity("contact");
        typo["first_name"] = "Typo";
        typo["last_name"] = "Table";
        typo["account_id"] = new ArxEntityReference("acount", 1L);              // "acount": not a declared table

        try
        {
            await service.CreateAsync(typo);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.InvalidLookup)
        {
            Show($"A table the schema lacks: {error.Code}, before anything was sent: {error.Message}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM contact
         * WHERE first_name IN ('Nobody', 'Typo');
         * -- Expect: 0: both contacts were refused.
         * ===========================================================
         */
    }
}

What every sample is given: the connection string (and the ISample interface the samples implement).

namespace ArxSamples.PostgreSql;

/// <summary>One documentation sample: a scenario a real application has, shown end to end.</summary>
public interface ISample
{
    /// <summary>The name to run it alone: <c>dotnet run -- &lt;name&gt;</c>.</summary>
    string Name { get; }

    /// <summary>One line on what it shows.</summary>
    string Title { get; }

    Task RunAsync(SampleContext dbContext);
}

/// <summary>
/// What a sample is given. Every sample gets the connection string; the application's service and catalog
/// (SampleContext.Application.cs) and the plain Npgsql read-back helper (SampleContext.Direct.cs) live in their own
/// files, so a sample needs only the files it uses.
/// </summary>
public sealed partial class SampleContext
{
    public SampleContext(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public string ConnectionString { get; }
}

How the samples print: Step for each scenario, Show for what it did, ShowError for an error caught on purpose.

using ARX.SDK;

namespace ArxSamples.PostgreSql;

/// <summary>How the samples print: a numbered step for each scenario, then what it did.</summary>
public static class SampleOutput
{
    /// <summary>The start of a scenario: <c>Step("1. By the id Create returned")</c>.</summary>
    public static void Step(string text) => Console.WriteLine($"  > {text}");

    /// <summary>What the scenario did.</summary>
    public static void Show(string text) => Console.WriteLine($"      {text}");

    /// <summary>An ARX error the scenario caught on purpose.</summary>
    public static void ShowError(ArxException exception)
        => Console.WriteLine($"      caught {exception.GetType().Name}: {exception.Message}");
}

  > 1. By the entity's RecordId
      created contact 1 for account 1
  > 2. By the id Create returned
      created contact 2 for account 2
  > 3. To a text id
      created a price for country BE
  > 4. A record that isn't there
      ForeignKeyViolation (the database's foreign key)
      A table the schema lacks: InvalidLookup, before anything was sent: ARX1005 InvalidLookup: Lookup 'account_id' on 'contact' refers to table 'acount', which the schema doesn't declare.