A report by industry

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 RealCase2_ReportByIndustry().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>
/// A report from aggregates (<c>$group</c>), in two scenarios: counts and sums by industry, and the distinct regions.
/// </summary>
/// <remarks>
/// Group-by columns and aggregate aliases are the result's attributes; order by either. A count of a field counts the
/// documents that have a value for it, as SQL's COUNT(column).
/// </remarks>
public sealed class RealCase2_ReportByIndustry : ISample
{
    public string Name => "retrieve-multiple-real-2-report-by-industry";

    public string Title => "A report by industry";

    public async Task RunAsync(SampleContext dbContext)
    {
        var accounts = new[] { (Industry.Retail, 120, "North"), (Industry.Retail, 80, "South"), (Industry.Finance, 300, "North"), (Industry.Technology, 45, "North") }
            .Select((x, i) =>
            {
                var account = new ArxEntity("account");
                account["name"] = $"Report account {i}";
                account["account_number"] = $"ACC-M99{i}";
                account["industry"] = new ArxSelectionSetValue("Industry", (int)x.Item1);
                account["employee_count"] = x.Item2;
                account["region"] = x.Item3;
                account["report"] = "M99";
                return account;
            })
            .ToList();

        await dbContext.Service.CreateMultipleAsync(accounts);

        await Scenario1_CountsAndSumsByIndustryAsync(dbContext.Service);

        await Scenario2_DistinctRegionsAsync(dbContext.Service);
    }

    /// <summary>1. Accounts and employees by industry, the largest first.</summary>
    private static async Task Scenario1_CountsAndSumsByIndustryAsync(ArxMongoDbService service)
    {
        Step("1. Counts and sums by industry");

        var query = new ArxQueryExpression("account");
        query.Criteria.AddCondition("report", ArxConditionOperator.Equal, "M99");
        query.AggregateInfo = new ArxAggregateInfo();
        query.AggregateInfo.AddGroupByColumn("industry");
        query.AggregateInfo.AddAggregateColumn("accounts", "", ArxAggregateOperator.Count);
        query.AggregateInfo.AddAggregateColumn("employees", "employee_count", ArxAggregateOperator.Sum);
        query.AddOrder("employees", ArxOrderType.Descending);

        foreach (var row in (await service.RetrieveMultipleAsync(query)).Entities)
        {
            Show($"{(Industry)Convert.ToInt32(row["industry"])}: {row["accounts"]} accounts, {row["employees"]} employees");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * db.account.aggregate([{ $match: { report: "M99" } }, { $group: { _id: "$industry", accounts: { $sum: 1 }, employees: { $sum: "$employee_count" } } }, { $sort: { employees: -1 } }])
         * -- Expect: 3 groups: Finance 1 / 300, Retail 2 / 200, Technology 1 / 45.
         * ===========================================================
         */
    }

    /// <summary>2. The regions the report's accounts are in, once each.</summary>
    private static async Task Scenario2_DistinctRegionsAsync(ArxMongoDbService service)
    {
        Step("2. Distinct regions");

        var query = new ArxQueryExpression("account") { Distinct = true };
        query.ColumnSet.AddColumn("region");
        query.Criteria.AddCondition("report", ArxConditionOperator.Equal, "M99");
        query.AddOrder("region", ArxOrderType.Ascending);

        Show(string.Join(", ", (await service.RetrieveMultipleAsync(query)).Entities.Select(x => x["region"])));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * db.account.distinct("region", { report: "M99" })
         * -- Expect: ["North", "South"].
         * ===========================================================
         */
    }
}

  > 1. Counts and sums by industry
      Finance: 1 accounts, 300 employees
      Retail: 2 accounts, 200 employees
      Technology: 1 accounts, 45 employees
  > 2. Distinct regions
      North, South