Read a view

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.RetrieveMultiple.RealCases;

// 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 RealCase5_ReadAView().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

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

namespace ArxSamples.PostgreSql.Operations.RetrieveMultiple.RealCases;

/// <summary>
/// A view, read like a table, in two scenarios: the view as it is (no declaration needed: no RecordId); and the view
/// declared with its id, so its rows carry a RecordId and Retrieve by id works.
/// </summary>
public sealed class RealCase5_ReadAView : ISample
{
    public string Name => "retrieve-multiple-real-5-read-a-view";

    public string Title => "Read a view";

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

        foreach (var first in new[] { "Vera", "Vik" })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = "View-R661";
            contact["account_id"] = new ArxEntityReference("account", accountId);
            await dbContext.Service.CreateAsync(contact);
        }

        await Scenario1_TheViewAsItIsAsync(dbContext);

        await Scenario2_DeclaredWithItsIdAsync(dbContext, accountId);
    }

    /// <summary>1. vw_account_overview (an account and its number of contacts), filtered like a table.</summary>
    private static async Task Scenario1_TheViewAsItIsAsync(SampleContext dbContext)
    {
        Step("1. The view as it is");

        var query = new ArxQueryExpression("vw_account_overview");
        query.ColumnSet.AddColumns("name", "contacts");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-R661");

        var row = (await dbContext.Service.RetrieveMultipleAsync(query)).Entities[0];
        Show($"{row["name"]}: {row["contacts"]} contacts, RecordId {(row.RecordId == null ? "none (the view isn't declared)" : row.RecordId)}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, contacts
         * FROM vw_account_overview
         * WHERE account_number = 'ACC-R661';
         * -- Expect: 1 row, Viewed Ventures, 2.
         * ===========================================================
         */
    }

    /// <summary>2. Declared with its id (a view has no primary key, so the id is supplied, not generated): RecordId and Retrieve by id.</summary>
    private static async Task Scenario2_DeclaredWithItsIdAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("2. The view declared with its id");

        var service = new ArxPgDbService(dbContext.ConnectionString, ArxSchemaCatalog.Create()
            .Table("vw_account_overview", ArxIdColumn.Supplied<long>("id"))
            .Build());

        var row = await service.RetrieveAsync("vw_account_overview", accountId.AsInt64(), new ArxColumnSet("name", "contacts")) ?? throw new InvalidOperationException("The vw_account_overview record isn't there.");
        Show($"Retrieve by id: {row["name"]}, RecordId {row.RecordId}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT o.name, o.contacts
         * FROM vw_account_overview o
         * JOIN account a ON a.id = o.id
         * WHERE a.account_number = 'ACC-R661';
         * -- Expect: 1 row, Viewed Ventures, 2.
         * ===========================================================
         */
    }
}

  > 1. The view as it is
      Viewed Ventures: 2 contacts, RecordId none (the view isn't declared)
  > 2. The view declared with its id
      Retrieve by id: Viewed Ventures, RecordId 1