Retrieve on a preloaded schema: lookups found, enums typed, Strict, saved JSON

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.RetrieveMultiple.AdvancedCases;

// 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 Advanced10_DynamicTableSchemaServiceSetup().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.PostgreSql.Services;
using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.PostgreSql.SampleOutput;

namespace ArxSamples.PostgreSql.Operations.RetrieveMultiple.AdvancedCases;

/// <summary>
/// Retrieve on a schema preloaded at service setup, in four scenarios: a lookup the preload found
/// (account.primary_contact_id, which added contact) read back as a reference with the contact's name; an enum column
/// read as text from the first read; Strict, where reading all columns means the preloaded ones and any other column
/// is refused; and a preload saved as JSON, so the next startup needs no catalog queries.
/// </summary>
/// <remarks>
/// Display names and selection sets are the developer's: a preload doesn't guess them. Add them in the preload's
/// <c>configure</c> callback, as below.
/// </remarks>
public sealed class Advanced10_DynamicTableSchemaServiceSetup : ISample
{
    public string Name => "retrieve-multiple-advanced-10-dynamic-table-schema-service-setup";

    public string Title => "Retrieve on a preloaded schema: lookups found, enums typed, Strict, saved JSON";

    public async Task RunAsync(SampleContext dbContext)
    {
        var schema = await Scenario1_ALookupThePreloadFoundAsync(dbContext);

        await Scenario2_AnEnumColumnReadAsTextFromTheFirstReadAsync(dbContext);

        await Scenario3_StrictReadsOnlyThePreloadedColumnsAsync(dbContext);

        await Scenario4_SaveThePreloadAndStartFromItAsync(dbContext, schema);
    }

    /// <summary>1. Preload account (contact comes with it), name contacts, and read the primary contact back as a reference.</summary>
    private static async Task<ArxSchemaCatalog> Scenario1_ALookupThePreloadFoundAsync(SampleContext dbContext)
    {
        Step("1. A lookup the preload found, read back with its display name");

        var tables = new ArxPreLoadTableRegister()
            .PreLoadTable("account");

        var schema = await new ArxPreLoadTable(tables).LoadAsync(
            dbContext.ConnectionString,
            configure => configure.DisplayName("contact", ["first_name", "last_name"], " "));
        var service = new ArxPgDbService(dbContext.ConnectionString, schema);

        var contact = new ArxEntity("contact");
        contact["first_name"] = "Rita";
        contact["last_name"] = "Retrieve";
        contact["email"] = "[email protected]";
        var contactId = await service.CreateAsync(contact);

        var account = new ArxEntity("account");
        account["name"] = "Lookup Lighting";
        account["account_number"] = "ACC-PRE111";
        account["primary_contact_id"] = new ArxEntityReference("contact", contactId);
        await service.CreateAsync(account);

        var query = new ArxQueryExpression("account");
        query.ColumnSet.AddColumns("name", "primary_contact_id");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-PRE111");
        var read = (await service.RetrieveMultipleAsync(query)).Entities.Single();
        var primary = (ArxEntityReference)read["primary_contact_id"]!;
        Show($"{read["name"]}: primary contact {primary.EntityName} {primary.RecordId}, \"{primary.Name}\"");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT a.name, c.first_name || ' ' || c.last_name AS primary_contact
         * FROM account a JOIN contact c ON c.id = a.primary_contact_id
         * WHERE a.account_number = 'ACC-PRE111';
         * -- Expect: Lookup Lighting, Rita Retrieve.
         * ===========================================================
         */

        return schema;
    }

    /// <summary>2. The showcase table's mood is an enum: written from a plain string, read as text, no retry.</summary>
    private static async Task Scenario2_AnEnumColumnReadAsTextFromTheFirstReadAsync(SampleContext dbContext)
    {
        Step("2. An enum column: a plain string in, text out, from the first read");

        var tables = new ArxPreLoadTableRegister()
            .PreLoadTable("data_type_showcase", ["text_value", "mood"]);

        var schema = await new ArxPreLoadTable(tables).LoadAsync(dbContext.ConnectionString);
        var service = new ArxPgDbService(dbContext.ConnectionString, schema);

        schema.TryGetTable("data_type_showcase", out var table);
        Show($"mood: {table!.Columns["mood"]}, {table.Columns["mood"].Traits}");

        var row = new ArxEntity("data_type_showcase");
        row["text_value"] = "preloaded mood";
        row["mood"] = "happy";
        await service.CreateAsync(row);

        var query = new ArxQueryExpression("data_type_showcase");
        query.ColumnSet.AddColumns("text_value", "mood");
        query.Criteria.AddCondition("mood", ArxConditionOperator.Equal, "happy");
        query.Criteria.AddCondition("text_value", ArxConditionOperator.Equal, "preloaded mood");
        var read = (await service.RetrieveMultipleAsync(query)).Entities.Single();
        Show($"read back: mood \"{read["mood"]}\" ({read["mood"]!.GetType().Name})");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT text_value, mood, pg_typeof(mood)
         * FROM data_type_showcase
         * WHERE text_value = 'preloaded mood';
         * -- Expect: 1 row, happy, showcase_mood: the string was cast to the enum when written.
         * ===========================================================
         */
    }

    /// <summary>3. Strict: all columns means the three preloaded; any other column is refused before the database.</summary>
    private static async Task Scenario3_StrictReadsOnlyThePreloadedColumnsAsync(SampleContext dbContext)
    {
        Step("3. Strict: reading all columns reads the preloaded ones");

        // A public directory endpoint: name, number and website, never the credit limit.
        var tables = new ArxPreLoadTableRegister()
            .PreLoadTable("account", ["name", "account_number", "website"]);

        var schema = await new ArxPreLoadTable(tables).LoadAsync(dbContext.ConnectionString);
        var directory = new ArxPgDbService(dbContext.ConnectionString, schema) { Strict = true };

        var all = new ArxQueryExpression("account") { ColumnSet = ArxColumnSet.All };
        all.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-PRE111");
        var read = (await directory.RetrieveMultipleAsync(all)).Entities.Single();
        Show($"all columns: {string.Join(", ", read.Attributes.Keys)}");

        var probe = new ArxQueryExpression("account");
        probe.ColumnSet.AddColumns("name", "credit_limit");

        try
        {
            await directory.RetrieveMultipleAsync(probe);
        }
        catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.NotPreloaded)
        {
            ShowError(exception);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, name, account_number, website
         * FROM account
         * WHERE account_number = 'ACC-PRE111';
         * -- Expect: 1 row: the four columns the Strict read returned (the id is always loaded).
         * ===========================================================
         */
    }

    /// <summary>4. Save the preload as JSON; the next startup builds the catalog from it, without a catalog query.</summary>
    private static async Task Scenario4_SaveThePreloadAndStartFromItAsync(SampleContext dbContext, ArxSchemaCatalog preloaded)
    {
        Step("4. Save the preload as JSON and start from it");

        // ARX doesn't write files: the application keeps the JSON where it likes (a file, a settings store).
        var json = preloaded.ToJson();
        var schema = ArxSchemaCatalog.Create().AddJson(json).Build();
        var service = new ArxPgDbService(dbContext.ConnectionString, schema);

        // The JSON doesn't drift by itself: check it against the database at startup, as with any declared catalog.
        await service.ValidateSchemaAsync();

        var query = new ArxQueryExpression("account");
        query.ColumnSet.AddColumns("name", "primary_contact_id");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-PRE111");
        var primary = (ArxEntityReference)(await service.RetrieveMultipleAsync(query)).Entities.Single()["primary_contact_id"]!;
        Show($"from the JSON ({json.Length:N0} characters): primary contact \"{primary.Name}\"");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * -- Nothing new is stored: the catalog was rebuilt in memory from the JSON.
         * ===========================================================
         */
    }
}

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. A lookup the preload found, read back with its display name
      Lookup Lighting: primary contact contact 1, "Rita Retrieve"
  > 2. An enum column: a plain string in, text out, from the first read
      mood: mood showcase_mood (string), Enum
      read back: mood "happy" (String)
  > 3. Strict: reading all columns reads the preloaded ones
      all columns: id, name, account_number, website
      caught ArxValidationException: ARX1018 NotPreloaded: Column 'credit_limit' of 'account' wasn't preloaded, and the service is Strict. Preloaded: id, name, account_number, website. Add it to the ArxPreLoadTable, or turn Strict off.
  > 4. Save the preload as JSON and start from it
      from the JSON (7,130 characters): primary contact "Rita Retrieve"