Lookups and display names

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.RetrieveMultiple.RealCases;

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

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

The sample, as it is in the repository.

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

namespace ArxSamples.MSSQL.Operations.RetrieveMultiple.RealCases;

/// <summary>
/// Lookups read back as references with a display name, in three scenarios: a contact's account, named
/// by the account's name (declared in SampleCatalog.cs); an account without one; and the names turned off when a
/// screen doesn't need them (one join fewer).
/// </summary>
public sealed class RealCase7_LookupsAndDisplayNames : ISample
{
    public string Name => "retrieve-multiple-real-7-lookups-and-display-names";

    public string Title => "Lookups and display names";

    public async Task RunAsync(SampleContext dbContext)
    {
        var account = new ArxEntity("account");
        account["name"] = "Named Nurseries";
        account["account_number"] = "ACC-R681";
        var accountId = await dbContext.Service.CreateAsync(account);

        var withAccount = new ArxEntity("contact");
        withAccount["first_name"] = "Nora";
        withAccount["last_name"] = "Lookup-R681";
        withAccount["account_id"] = new ArxEntityReference("account", accountId);
        var noraId = await dbContext.Service.CreateAsync(withAccount);

        var withoutAccount = new ArxEntity("contact");
        withoutAccount["first_name"] = "Ned";
        withoutAccount["last_name"] = "Lookup-R681";
        await dbContext.Service.CreateAsync(withoutAccount);

        await Scenario1_ALookupWithItsNameAsync(dbContext, noraId);

        await Scenario2_NoAccountAsync(dbContext);

        await Scenario3_NamesOffAsync(dbContext);
    }

    /// <summary>1. account_id reads back as an ArxEntityReference: the table, the id, and the account's name.</summary>
    private static async Task Scenario1_ALookupWithItsNameAsync(SampleContext dbContext, ArxRecordId contactId)
    {
        Step("1. A contact's account, with its name");

        var contact = await dbContext.Service.RetrieveAsync("contact", contactId, new ArxColumnSet("first_name", "account_id")) ?? throw new InvalidOperationException("The contact record isn't there.");
        var account = (ArxEntityReference)contact["account_id"]!;
        Show($"{contact["first_name"]} → {account.EntityName} {account.RecordId}: \"{account.Name}\"");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT c.first_name, a.name
         * FROM contact c
         * JOIN account a ON a.id = c.account_id
         * WHERE c.first_name = 'Nora' AND c.last_name = 'Lookup-R681';
         * -- Expect: 1 row, Nora, Named Nurseries.
         * ===========================================================
         */
    }

    /// <summary>2. A contact without an account: the attribute is there, and null.</summary>
    private static async Task Scenario2_NoAccountAsync(SampleContext dbContext)
    {
        Step("2. A contact without an account");

        var query = new ArxQueryExpression("contact");
        query.ColumnSet.AddColumns("first_name", "account_id");
        query.Criteria.AddCondition("first_name", ArxConditionOperator.Equal, "Ned");
        query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "Lookup-R681");

        var ned = (await dbContext.Service.RetrieveMultipleAsync(query)).Entities[0];
        Show($"{ned["first_name"]} → {(ned["account_id"] == null ? "no account" : "an account")}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT CASE WHEN account_id IS NULL THEN 1 ELSE 0 END AS no_account
         * FROM contact
         * WHERE first_name = 'Ned' AND last_name = 'Lookup-R681';
         * -- Expect: 1 row, 1.
         * ===========================================================
         */
    }

    /// <summary>3. LookupNames = false: the references keep their id, without the join that reads the names.</summary>
    private static async Task Scenario3_NamesOffAsync(SampleContext dbContext)
    {
        Step("3. Names off: the id only, one join fewer");

        var query = new ArxQueryExpression("contact");
        query.ColumnSet.AddColumns("first_name", "account_id");
        query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "Lookup-R681");
        query.Criteria.AddCondition("account_id", ArxConditionOperator.NotNull);

        var nora = (await dbContext.Service.RetrieveMultipleAsync(query, new ArxRetrieveOptions { LookupNames = false })).Entities[0];
        var account = (ArxEntityReference)nora["account_id"]!;
        Show($"{nora["first_name"]} → {account.EntityName} {account.RecordId}, name {(account.Name == null ? "not read" : account.Name)}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT CASE WHEN account_id IS NOT NULL THEN 1 ELSE 0 END AS has_account
         * FROM contact
         * WHERE first_name = 'Nora' AND last_name = 'Lookup-R681';
         * -- Expect: 1 row, 1.
         * ===========================================================
         */
    }
}

  > 1. A contact's account, with its name
      Nora → account 1: "Named Nurseries"
  > 2. A contact without an account
      Ned → no account
  > 3. Names off: the id only, one join fewer
      Nora → account 1, name not read