Accounts and contacts in one query

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 RealCase2_AccountWithItsContacts().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>
/// Reading across tables, in three scenarios: a link entity (a join with its own alias, columns and criteria); a LEFT
/// join that keeps rows without a match; and the short form, a direct join written in the column set
/// (<c>account_id._lk.account.name,account_number</c>).
/// </summary>
public sealed class RealCase2_AccountWithItsContacts : ISample
{
    public string Name => "retrieve-multiple-real-2-account-with-its-contacts";

    public string Title => "Accounts and contacts in one query";

    public async Task RunAsync(SampleContext dbContext)
    {
        var account = new ArxEntity("account");
        account["name"] = "Linked Logistics";
        account["account_number"] = "ACC-R602";
        account["status"] = new ArxSelectionSetValue("AccountStatus", "ACTIVE");
        var accountId = await dbContext.Service.CreateAsync(account);

        var contacts = new List<ArxEntity>();

        foreach (var (first, withAccount) in new[] { ("Lena", true), ("Lars", true), ("Lone", false) })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = "Link-R602";
            contact["account_id"] = withAccount ? new ArxEntityReference("account", accountId) : null;
            contacts.Add(contact);
        }

        await dbContext.Service.CreateMultipleAsync(contacts);

        await Scenario1_ALinkEntityAsync(dbContext);

        await Scenario2_ALeftJoinKeepsEveryContactAsync(dbContext);

        await Scenario3_ADirectJoinInTheColumnSetAsync(dbContext);
    }

    private static ArxQueryExpression Contacts()
    {
        var query = new ArxQueryExpression("contact");
        query.ColumnSet.AddColumn("first_name");
        query.Criteria.AddCondition("last_name", ArxConditionOperator.Equal, "Link-R602");
        query.AddOrder("first_name", ArxOrderType.Ascending);
        return query;
    }

    /// <summary>1. Contacts with their account's name and number: an inner join; the columns come back as <c>acc.name</c>.</summary>
    private static async Task Scenario1_ALinkEntityAsync(SampleContext dbContext)
    {
        Step("1. A link entity: each contact with its account");

        var query = Contacts();
        var account = query.AddLink("account", "account_id", "id", ArxJoinOperator.Inner);
        account.EntityAlias = "acc";
        account.ColumnSet.AddColumns("name", "account_number");
        account.LinkCriteria.AddCondition("status", ArxConditionOperator.Equal, "ACTIVE");

        var rows = await dbContext.Service.RetrieveMultipleAsync(query);

        foreach (var row in rows.Entities)
        {
            Show($"{row["first_name"]} works at {row["acc.name"]} ({row["acc.account_number"]})");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT c.first_name, a.name, a.account_number
         * FROM contact c
         * JOIN account a ON a.id = c.account_id AND a.status = 'ACTIVE'
         * WHERE c.last_name = 'Link-R602'
         * ORDER BY c.first_name;
         * -- Expect: 2 rows, Lars and Lena at Linked Logistics (ACC-R602).
         * ===========================================================
         */
    }

    /// <summary>2. A LEFT join: Lone has no account and is kept, with a null account name.</summary>
    private static async Task Scenario2_ALeftJoinKeepsEveryContactAsync(SampleContext dbContext)
    {
        Step("2. A LEFT join keeps the contact without an account");

        var query = Contacts();
        var account = query.AddLink("account", "account_id", "id", ArxJoinOperator.Left);
        account.EntityAlias = "acc";
        account.ColumnSet.AddColumn("name");

        var rows = await dbContext.Service.RetrieveMultipleAsync(query);
        Show(string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]}: {x["acc.name"] ?? "(no account)"}")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT c.first_name, a.name
         * FROM contact c
         * LEFT JOIN account a ON a.id = c.account_id
         * WHERE c.last_name = 'Link-R602'
         * ORDER BY c.first_name;
         * -- Expect: 3 rows, Lars and Lena with Linked Logistics, Lone with NULL.
         * ===========================================================
         */
    }

    /// <summary>
    /// 3. The short form: <c>account_id._lk.account.name,account_number</c> in the column set joins the account on its
    /// declared id and reads its columns as <c>account_id.name</c> and <c>account_id.account_number</c>.
    /// </summary>
    private static async Task Scenario3_ADirectJoinInTheColumnSetAsync(SampleContext dbContext)
    {
        Step("3. A direct join written in the column set");

        var query = Contacts();
        query.ColumnSet.AddColumn("account_id._lk.account.name,account_number");

        var rows = await dbContext.Service.RetrieveMultipleAsync(query);
        Show(string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]}: {x["account_id.account_number"] ?? "-"}")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT c.first_name, a.account_number
         * FROM contact c
         * LEFT JOIN account a ON a.id = c.account_id
         * WHERE c.last_name = 'Link-R602'
         * ORDER BY c.first_name;
         * -- Expect: 3 rows, Lars ACC-R602, Lena ACC-R602, Lone NULL (a direct join is a LEFT join).
         * ===========================================================
         */
    }
}

  > 1. A link entity: each contact with its account
      Lars works at Linked Logistics (ACC-R602)
      Lena works at Linked Logistics (ACC-R602)
  > 2. A LEFT join keeps the contact without an account
      Lars: Linked Logistics, Lena: Linked Logistics, Lone: (no account)
  > 3. A direct join written in the column set
      Lars: ACC-R602, Lena: ACC-R602, Lone: -