Use columns named outside the name rule, through aliases

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Create.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 Advanced8_ColumnNamesThroughAliases().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

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

namespace ArxSamples.PostgreSql.Operations.Create.AdvancedCases;

/// <summary>
/// Use a table whose columns are named outside ARX's name rule, in three scenarios: declare an alias for each such column
/// and create a record through them, read and query by the aliases, and see that the database's own name is still refused
/// when a call uses it.
/// </summary>
/// <remarks>
/// <para>ARX accepts only names of ASCII letters, digits and underscores, so no name can break out of its quotes. A table
/// made outside ARX may have columns such as <c>first-name</c>, <c>Customer Code</c> or <c>order.total</c>: declare an alias
/// for each in the schema catalog, <c>.Alias(table, attribute, databaseName)</c>. Code uses the attribute; ARX sends the
/// column's name, quoted by PostgreSQL's rules, and reads the column back as the attribute. The id and keys are declared by
/// their attributes.</para>
/// <para>Only declared names pass: the database name never comes from a call. A link entity's columns keep the name rule
/// in this version.</para>
/// </remarks>
public sealed class Advanced8_ColumnNamesThroughAliases : ISample
{
    public string Name => "create-advanced-8-column-names-through-aliases";

    public string Title => "Use columns named outside the name rule, through aliases";

    public async Task RunAsync(SampleContext dbContext)
    {
        var schema = ArxSchemaCatalog.Create()
            .Table("legacy_customer", ArxIdColumn.Supplied<string>("customer_code"))
            .Alias("legacy_customer", "customer_code", "Customer Code")
            .Alias("legacy_customer", "first_name", "first-name")
            .Alias("legacy_customer", "last_name", "last-name")
            .Alias("legacy_customer", "order_total", "order.total")
            .Build();

        var service = new ArxPgDbService(dbContext.ConnectionString, schema);

        await Scenario1_CreateThroughTheAliasesAsync(service);

        await Scenario2_ReadAndQueryByTheAliasesAsync(service);

        await Scenario3_TheDatabaseNameItselfIsRefusedAsync(service);
    }

    /// <summary>1. Create two customers: each attribute is written to the column its alias names.</summary>
    private static async Task Scenario1_CreateThroughTheAliasesAsync(ArxPgDbService service)
    {
        Step("1. Create through the aliases");

        var ana = new ArxEntity("legacy_customer");
        ana["customer_code"] = "C-1001";
        ana["first_name"] = "Ana";
        ana["last_name"] = "Silva";
        ana["order_total"] = 120.50m;

        var id = await service.CreateAsync(ana);

        var ben = new ArxEntity("legacy_customer");
        ben["customer_code"] = "C-1002";
        ben["first_name"] = "Ben";
        ben["order_total"] = 75.00m;

        await service.CreateAsync(ben);

        Show($"created {id} and C-1002");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT "first-name", "order.total" FROM legacy_customer WHERE "Customer Code" IN ('C-1001', 'C-1002');
         * -- Expect: 2 rows: Ana with 120.50, Ben with 75.00.
         * ===========================================================
         */
    }

    /// <summary>2. Read a record by id and query by the aliases: every column comes back under its alias.</summary>
    private static async Task Scenario2_ReadAndQueryByTheAliasesAsync(ArxPgDbService service)
    {
        Step("2. Read and query by the aliases");

        var ana = await service.RetrieveAsync("legacy_customer", ArxRecordId.Of("customer_code", "C-1001")) ?? throw new InvalidOperationException("The legacy_customer record isn't there.");

        Show($"{ana["customer_code"]}: {ana["first_name"]} {ana["last_name"]}, total {ana["order_total"]}");

        var query = new ArxQueryExpression("legacy_customer");
        query.ColumnSet.AddColumns("first_name", "order_total");
        query.Criteria.AddCondition("order_total", ArxConditionOperator.GreaterThan, 50m);
        query.AddOrder("first_name", ArxOrderType.Descending);

        var customers = await service.RetrieveMultipleAsync(query);

        foreach (var customer in customers.Entities)
        {
            Show($"{customer["first_name"]}: {customer["order_total"]}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT "first-name", "order.total" FROM legacy_customer WHERE "order.total" > 50 ORDER BY "first-name" DESC;
         * -- Expect: Ben, then Ana.
         * ===========================================================
         */
    }

    /// <summary>3. A call that uses the column's own name is refused before the database: only declared aliases pass.</summary>
    private static async Task Scenario3_TheDatabaseNameItselfIsRefusedAsync(ArxPgDbService service)
    {
        Step("3. The database name itself is refused");

        var change = new ArxEntity("legacy_customer");
        change["customer_code"] = "C-1002";
        change["first-name"] = "Benjamin";

        try
        {
            await service.UpdateAsync(change);
        }
        catch (ArxException exception) when (exception.Code == ArxErrorCode.InvalidIdentifier)
        {
            ShowError(exception);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT "first-name" FROM legacy_customer WHERE "Customer Code" = 'C-1002';
         * -- Expect: Ben (the update was 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. Create through the aliases
      created C-1001 and C-1002
  > 2. Read and query by the aliases
      C-1001: Ana Silva, total 120.50
      Ben: 75.00
      Ana: 120.50
  > 3. The database name itself is refused
      caught ArxValidationException: ARX1003 InvalidIdentifier: Attribute name 'first-name' is not a valid identifier. Use ASCII letters, digits and underscores, starting with a letter or underscore, at most 63 characters. A column named otherwise is used through an alias declared in the schema catalog: .Alias(table, attribute, databaseName).