Read an account with its contacts

Runs the sample. Generated for this page; it reads the connection string from the environment and first remakes the sample database from sample-schema.json.

using ArxSamples.MongoDb;
using ArxSamples.MongoDb.Operations.RetrieveMultiple.RealCases;

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

var dbContext = new SampleContext(connectionString);

// Every run starts from the sample data: this deletes and makes again what sample-schema.json lists
// (save the file in the project folder, see Get started).
await dbContext.Direct.ResetAsync();

await new RealCase1_AccountWithItsContacts().RunAsync(dbContext);

The sample, as it is in the repository.

using ARX.SDK.MongoDb.Services;
using ARX.SDK.Query;
using ARX.SDK.Services;

using static ArxSamples.MongoDb.SampleOutput;

namespace ArxSamples.MongoDb.Operations.RetrieveMultiple.RealCases;

/// <summary>
/// An account and its contacts, three ways: lookups read back as references with the account's name, a join
/// (<c>$lookup</c>) from contacts to their account, and a left join that keeps contacts without one.
/// </summary>
/// <remarks>
/// A declared lookup (<c>.Lookup("contact", "account_id", "account")</c>) reads back as an <see cref="ArxEntityReference"/>
/// whose <c>Name</c> is the account's display name. A link entity is a <c>$lookup</c>: inner or left; its fields come back
/// as <c>alias.field</c>.
/// </remarks>
public sealed class RealCase1_AccountWithItsContacts : ISample
{
    public string Name => "retrieve-multiple-real-1-account-with-its-contacts";

    public string Title => "Read an account with its contacts";

    public async Task RunAsync(SampleContext dbContext)
    {
        var account = new ArxEntity("account");
        account["name"] = "City Power & Light";
        account["account_number"] = "ACC-M981";
        var accountId = await dbContext.Service.CreateAsync(account);

        foreach (var (first, last, hasAccount) in new[] { ("Ivo", "Matos", true), ("Joana", "Reis", true), ("Kai", "Lopes", false) })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = last;
            contact["segment"] = "M98";

            if (hasAccount)
            {
                contact["account_id"] = new ArxEntityReference("account", accountId);
            }

            await dbContext.Service.CreateAsync(contact);
        }

        await Scenario1_LookupsWithTheAccountsNameAsync(dbContext.Service);

        await Scenario2_AJoinToTheAccountAsync(dbContext.Service);

        await Scenario3_ALeftJoinAsync(dbContext.Service);
    }

    /// <summary>1. Read the contacts: account_id comes back as a reference named after the account.</summary>
    private static async Task Scenario1_LookupsWithTheAccountsNameAsync(ArxMongoDbService service)
    {
        Step("1. Lookups with the account's name");

        var query = new ArxQueryExpression("contact");
        query.ColumnSet.AddColumns("first_name", "account_id");
        query.Criteria.AddCondition("segment", ArxConditionOperator.Equal, "M98");
        query.Criteria.AddCondition("account_id", ArxConditionOperator.NotNull);
        query.AddOrder("first_name", ArxOrderType.Ascending);

        foreach (var contact in (await service.RetrieveMultipleAsync(query)).Entities)
        {
            var reference = (ArxEntityReference)contact["account_id"]!;
            Show($"{contact["first_name"]} works at {reference.Name}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * db.contact.find({ segment: "M98", account_id: { $ne: null } }, { first_name: 1, account_id: 1 })
         * -- Expect: 2 documents, Ivo and Joana, account_id the account's ObjectId.
         * ===========================================================
         */
    }

    /// <summary>2. An inner join: only contacts with an account, with its name and number.</summary>
    private static async Task Scenario2_AJoinToTheAccountAsync(ArxMongoDbService service)
    {
        Step("2. A join to the account");

        var query = new ArxQueryExpression("contact");
        query.ColumnSet.AddColumn("first_name");
        query.Criteria.AddCondition("segment", ArxConditionOperator.Equal, "M98");
        var link = query.AddLink("account", "account_id", "account_id", ArxJoinOperator.Inner);
        link.EntityAlias = "acc";
        link.ColumnSet.AddColumns("name", "account_number");
        query.AddOrder("first_name", ArxOrderType.Ascending);

        foreach (var contact in (await service.RetrieveMultipleAsync(query)).Entities)
        {
            Show($"{contact["first_name"]}: {contact["acc.name"]} ({contact["acc.account_number"]})");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * db.contact.aggregate([{ $match: { segment: "M98" } }, { $lookup: { from: "account", localField: "account_id", foreignField: "_id", as: "acc" } }, { $unwind: "$acc" }])
         * -- Expect: 2 documents, Ivo and Joana, each with the account City Power & Light.
         * ===========================================================
         */
    }

    /// <summary>3. A left join keeps Kai, who has no account: his account fields read null.</summary>
    private static async Task Scenario3_ALeftJoinAsync(ArxMongoDbService service)
    {
        Step("3. A left join");

        var query = new ArxQueryExpression("contact");
        query.ColumnSet.AddColumn("first_name");
        query.Criteria.AddCondition("segment", ArxConditionOperator.Equal, "M98");
        var link = query.AddLink("account", "account_id", "account_id", ArxJoinOperator.Left);
        link.EntityAlias = "acc";
        link.ColumnSet.AddColumn("name");
        query.AddOrder("first_name", ArxOrderType.Ascending);

        Show(string.Join(", ", (await service.RetrieveMultipleAsync(query)).Entities.Select(x => $"{x["first_name"]}: {x["acc.name"] ?? "(none)"}")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * db.contact.countDocuments({ segment: "M98" })
         * -- Expect: 3.
         * ===========================================================
         */
    }
}

  > 1. Lookups with the account's name
      Ivo works at City Power & Light
      Joana works at City Power & Light
  > 2. A join to the account
      Ivo: City Power & Light (ACC-M981)
      Joana: City Power & Light (ACC-M981)
  > 3. A left join
      Ivo: City Power & Light, Joana: City Power & Light, Kai: (none)